Sales by Day of the Week - Problem

You're the business owner of a retail company and need to generate a comprehensive sales report that shows your business performance across different days of the week.

Given two database tables:

  • Orders - Contains all order transactions with dates and quantities
  • Items - Contains item details including categories

Your goal is to create a weekly sales summary that shows:

  • How many units of each item category were sold
  • Broken down by each day of the week (Monday through Sunday)
  • Results ordered alphabetically by category name

This type of analysis helps identify shopping patterns - for example, do people buy more Electronics on weekends or Groceries on specific weekdays?

Expected Output: A table with columns for Category, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday containing total quantities sold.

Input & Output

example_1.sql โ€” Basic Sales Report
$ Input: Orders table: | order_id | customer_id | order_date | item_id | quantity | |----------|-------------|------------|---------|----------| | 1 | 1001 | 2020-06-01 | 10 | 10 | | 2 | 1002 | 2020-06-02 | 20 | 5 | | 3 | 1003 | 2020-06-03 | 30 | 15 | | 4 | 1004 | 2020-06-04 | 40 | 8 | Items table: | item_id | item_name | item_category | |---------|--------------|---------------| | 10 | Mouse | Electronics | | 20 | Keyboard | Electronics | | 30 | T-shirt | Clothing | | 40 | Novel | Books |
โ€บ Output: | Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |-------------|--------|---------|-----------|----------|--------|-----------|---------| | Books | 8 | 0 | 0 | 0 | 0 | 0 | 0 | | Clothing | 0 | 0 | 15 | 0 | 0 | 0 | 0 | | Electronics | 10 | 5 | 0 | 0 | 0 | 0 | 0 |
๐Ÿ’ก Note: Orders are placed on different days: Monday (10 Electronics), Tuesday (5 Electronics), Wednesday (15 Clothing), Thursday (8 Books). Each category shows totals for respective days, with 0 for days without sales.
example_2.sql โ€” Multiple Orders Same Day
$ Input: Orders table: | order_id | customer_id | order_date | item_id | quantity | |----------|-------------|------------|---------|----------| | 1 | 1001 | 2020-06-01 | 10 | 5 | | 2 | 1002 | 2020-06-01 | 10 | 3 | | 3 | 1003 | 2020-06-01 | 20 | 7 | | 4 | 1004 | 2020-06-02 | 30 | 12 | Items table: | item_id | item_name | item_category | |---------|-----------|---------------| | 10 | Phone | Electronics | | 20 | Laptop | Electronics | | 30 | Jeans | Clothing |
โ€บ Output: | Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |-------------|--------|---------|-----------|----------|--------|-----------|---------| | Clothing | 0 | 12 | 0 | 0 | 0 | 0 | 0 | | Electronics | 15 | 0 | 0 | 0 | 0 | 0 | 0 |
๐Ÿ’ก Note: Multiple Electronics orders on Monday (5+3+7=15 total), and one Clothing order on Tuesday (12). The aggregation correctly sums multiple orders for the same category on the same day.
example_3.sql โ€” Categories Without Orders
$ Input: Orders table: | order_id | customer_id | order_date | item_id | quantity | |----------|-------------|------------|---------|----------| | 1 | 1001 | 2020-06-07 | 10 | 20 | Items table: | item_id | item_name | item_category | |---------|-----------|---------------| | 10 | Game | Electronics | | 20 | Book | Education | | 30 | Toy | Children |
โ€บ Output: | Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |-------------|--------|---------|-----------|----------|--------|-----------|---------| | Children | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | Education | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | Electronics | 0 | 0 | 0 | 0 | 0 | 0 | 20 |
๐Ÿ’ก Note: Even categories without any orders (Children, Education) appear in the result with zeros for all days due to LEFT JOIN. The single Electronics order on Sunday shows 20 units.

Constraints

  • 1 โ‰ค number of orders โ‰ค 104
  • 1 โ‰ค number of items โ‰ค 103
  • 1 โ‰ค quantity โ‰ค 103
  • order_date format: 'YYYY-MM-DD'
  • All item_id values in Orders exist in Items table

Visualization

Tap to expand
Sales Dashboard Creation ProcessRaw Orders2020-06-01: Phone x52020-06-02: Laptop x32020-06-03: Jeans x22020-06-07: Game x8Add CategoriesPhone โ†’ ElectronicsLaptop โ†’ ElectronicsJeans โ†’ ClothingGame โ†’ ElectronicsExtract Days06-01 โ†’ Monday06-02 โ†’ Tuesday06-03 โ†’ Wednesday06-07 โ†’ SundayPivot LogicIF Monday: sum_monIF Tuesday: sum_tueIF Wednesday: sum_wedetc...Executive DashboardCategory | Mon | Tue | Wed | Thu | Fri | Sat | SunClothing | 0 | 0 | 2 | 0 | 0 | 0 | 0Electronics | 5 | 3 | 0 | 0 | 0 | 0 | 8Final Report
Understanding the Visualization
1
Data Integration
Connect order transactions with product catalog to get category information
2
Day Extraction
Extract day-of-week from each transaction date (Monday=2, Tuesday=3, etc.)
3
Pivot Transform
Transform rows (individual orders) into columns (daily totals) using conditional logic
4
Category Grouping
Aggregate all daily totals for each product category
5
Executive Report
Present final dashboard ordered by category for easy analysis
Key Takeaway
๐ŸŽฏ Key Insight: Conditional aggregation with CASE statements allows us to pivot row data into columns efficiently, transforming transactional data into analytical reports in a single database operation.
Asked in
Amazon 85 Google 72 Microsoft 58 Meta 41
89.4K Views
High Frequency
~25 min Avg. Time
2.8K Likes
Ln 1, Col 1
Smart Actions
๐Ÿ’ก Explanation
AI Ready
๐Ÿ’ก Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen