Sales by Day of the Week - Practice Coding Problems

Sales by Day of the Week - Problem

Database Hard

You are given two tables: Orders and Items. The Orders table contains information about orders placed by customers, and the Items table contains item details including categories.

As a business owner, you need to create a sales report showing how many units of each item category have been ordered on each day of the week.

Write a SQL query to return the total quantity ordered for each category on each day of the week (Monday through Sunday).

Requirements:

Show results for all 7 days of the week, even if no orders were placed
Show results for all categories that appear in orders
Use 0 for days/categories with no orders
Order results by category name

Table Schema

Orders

Column Name	Type	Description
`order_id` PK	int	Order identifier
`customer_id`	int	Customer who placed the order
`order_date`	date	Date when the order was placed
`item_id` PK	varchar	Item that was ordered
`quantity`	int	Number of units ordered

Primary Key: (order_id, item_id)

Items

Column Name	Type	Description
`item_id` PK	varchar	Unique item identifier
`item_name`	varchar	Name of the item
`item_category`	varchar	Category the item belongs to

Primary Key: item_id

Input & Output

Example 1 — Basic Sales Report

Input Tables:

Orders

order_id	customer_id	order_date	item_id	quantity
1	1	2020-06-01	10	10
2	1	2020-06-08	20	10
3	2	2020-06-02	30	5
4	3	2020-06-03	40	5
5	4	2020-06-04	10	15

Items

item_id	item_name	item_category
10	laptop	Electronics
20	C++	Books
30	mouse	Electronics
40	keyboard	Electronics

Output:

item_category	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Books	10	0	0	0	0	0	0
Electronics	10	5	5	15	0	0	0

💡 Note:

The query joins Orders with Items to get categories, then groups by category and day of week. June 1st 2020 was a Monday (laptop: 10 units), June 2nd was Tuesday (mouse: 5 units), June 3rd was Wednesday (keyboard: 5 units), June 4th was Thursday (laptop: 15 units), and June 8th was Monday (C++ book: 10 units).

Example 2 — Single Category

Input Tables:

Orders

order_id	customer_id	order_date	item_id	quantity
1	1	2020-06-01	10	5

Items

item_id	item_name	item_category
10	laptop	Electronics

Output:

item_category	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Electronics	5	0	0	0	0	0	0

💡 Note:

With only one order on Monday (June 1st, 2020) for Electronics category, all other days show 0 quantities. The cross join approach ensures all 7 days appear in the result.

Constraints

1 ≤ order_id ≤ 1000
1 ≤ customer_id ≤ 1000
order_date is a valid date
item_id is a non-empty string
1 ≤ quantity ≤ 1000

Visualization

Tap to expand

Understanding the Visualization

Join

Combine Orders and Items tables

Group

Aggregate by category and day of week

Pivot

Transform to show days as columns

Key Takeaway

🎯 Key Insight: Use CROSS JOIN with days of week to ensure all columns appear, then LEFT JOIN for complete data coverage

Asked in

A Amazon 12 M Microsoft 8

Use cross join to create all category-day combinations, then LEFT JOIN with aggregated order data and pivot results to show sales by day of week.

Table Schema

Orders

Column Name	Type	Description
`order_id` PK	int	Order identifier
`customer_id`	int	Customer who placed the order
`order_date`	date	Date when the order was placed
`item_id` PK	varchar	Item that was ordered
`quantity`	int	Number of units ordered

Primary Key: (order_id, item_id)

Items

Column Name	Type	Description
`item_id` PK	varchar	Unique item identifier
`item_name`	varchar	Name of the item
`item_category`	varchar	Category the item belongs to

Primary Key: item_id

Common Approaches

Approach	Time	Space	Notes
✓ Cross Join with Day Names	O(n + m)	O(k)	Cross join categories with days of week, then left join with aggregated orders

Cross Join with Day Names — Algorithm Steps

Step 1: Create a list of all 7 days of the week
Step 2: Cross join categories with days to get all combinations
Step 3: Left join with aggregated order data grouped by category and day
Step 4: Use COALESCE to show 0 for missing combinations

Visualization

Tap to expand

Step-by-Step Walkthrough

Cross Join

Create all category-day combinations

Left Join

Join with actual order data

Pivot

Transform rows to columns for each day

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of orders and items tables

✓ Linear Growth

Space Complexity

O(k)

Space for category-day combinations

✓ Linear Space

23.4K Views

Medium Frequency

~25 min Avg. Time

892 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Cross Join with Day Names — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler