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
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.
๐ก
Explanation
AI Ready
๐ก Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code