Skip to content

Latest commit

 

History

History
92 lines (64 loc) · 3.73 KB

README.md

File metadata and controls

92 lines (64 loc) · 3.73 KB

Overview of the SQL Project: Coffee Shop Sales Analysis

This SQL project focuses on a sales analysis of a coffee shop, covering multiple key aspects such as total sales, order counts, and trends over time. Here's a breakdown of the project:

1. Database Setup:

  • A database Coffee_shop_Sales is created to store sales data.
  • The project uses a Coffee_Sales table for querying and analysis.

2. Data Exploration & Alteration:

  • Data Type Checks: Inspects the columns of Coffee_Sales using:
    • sp_help 'Coffee_Sales'
    • INFORMATION_SCHEMA.COLUMNS
  • Alterations:
    • transaction_date is altered to DATE.
    • transaction_time is altered to TIME.

3. Monthly Sales Calculation:

  • For each month (January to December), total sales are calculated using SUM(transaction_qty * unit_price).
  • Extracts the month from transaction_date using MONTH() to filter sales by specific months.

4. Month-on-Month Sales & Order Trends:

  • Sales Trend Analysis:
    • Calculates month-on-month sales differences using the LAG() function to compare current sales with the previous month.
  • Order Count Trends:
    • Calculates the number of transactions (orders) for each month using COUNT(transaction_id).

5. Sales Quantity & Comparison:

  • Total Quantity Sold: Computes the total quantity sold in each month using COUNT(transaction_qty).
  • Month-on-Month Quantity Trends: Uses LAG() to track increases or decreases in sold quantities.

6. Comprehensive Sales Metrics:

  • Combines multiple metrics (quantity, transactions, sales) in a single query:
    • Uses CONCAT() and ROUND() to present totals in thousands (e.g., 1k for 1000).

7. Sales by Weekdays & Weekends:

  • Uses DATEPART(WEEKDAY, transaction_date) to analyze sales performance on weekends versus weekdays.
  • Compares sales totals on weekends (Saturday, Sunday) with weekdays.

8. Store Location Sales Analysis:

  • Analyzes total sales for each store location on a monthly basis.
  • Ranks sales by location using GROUP BY store_location.

9. Day-wise and Hour-wise Sales:

  • Daily Sales: Calculates total sales for each day of the month using DAY(transaction_date).
  • Hourly Sales: Summarizes sales by the hour using DATEPART(HOUR, transaction_time).

10. Average Sales Calculation:

  • Monthly Averages: Calculates the average sales for each month using AVG().

11. Sales by Product Categories and Types:

  • Product Category: Summarizes total sales by product categories (e.g., coffee, snacks) for each month.
  • Product Type: Calculates sales for different product types using GROUP BY product_type.

12. Total Sales by Day of the Week:

  • Weekday Sales Breakdown: Assigns meaningful labels (e.g., "Monday," "Tuesday") to sales based on the day of the week using CASE statements.
  • Sales trends by weekday for the month of May.

Key Functions and Techniques:

  • Aggregations: SUM(), COUNT(), AVG()
  • Date/Time Handling: MONTH(), DAY(), DATEPART(WEEKDAY)
  • Window Functions: LAG() for month-on-month comparisons.
  • Grouping: GROUP BY for summarizing sales by month, product, store, and time.
  • Conditional Statements: CASE for categorizing weekday vs. weekend sales.

Conclusion:

This project provides an in-depth analysis of coffee shop sales data, tracking monthly sales trends, order counts, and performance based on time (day, week, hour). It also covers product-based and location-based sales insights, making it a comprehensive exploration of sales metrics.