Sales by Day of the Week - Problem

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
Sales by Day of the Week INPUT Orders Table order_id item_id order_date quantity 1 101 2023-01-02 5 2 102 2023-01-03 3 3 101 2023-01-04 2 Items Table item_id category 101 Electronics 102 Clothing 103 Food Days of Week (1-7) 1=Monday, 2=Tuesday 3=Wednesday, 4=Thursday 5=Friday, 6=Saturday 7=Sunday Use DAYOFWEEK() function ALGORITHM STEPS 1 Cross Join Categories Create all category-day combinations (7 days each) 2 Join Orders + Items Link orders to categories via item_id foreign key 3 Extract Day of Week DAYOFWEEK(order_date) returns 1-7 for each order 4 Aggregate with COALESCE SUM quantity per category COALESCE(sum, 0) for nulls SELECT category, SUM(CASE WHEN DAYOFWEEK(date)=1 THEN qty ELSE 0 END) as Monday, ... ORDER BY category FINAL RESULT Category Mon-Sun Sales Clothing Mon:0 Tue:3 Wed:0 Thu:0 Fri:0 Sat:0 Sun:0 Electronics Mon:5 Tue:0 Wed:2 Thu:0 Fri:0 Sat:0 Sun:0 Food Mon:0 Tue:0 Wed:0 Thu:0 Fri:0 Sat:0 Sun:0 Sales Distribution Electronics Clothing OK - Ordered by category Key Insight: Use CROSS JOIN between categories and a generated days table to ensure all 7 days appear for each category. LEFT JOIN with orders and use COALESCE or IFNULL to convert NULL sums to 0 for days with no orders. The pivot pattern with CASE WHEN transforms rows into columns for each day of the week. TutorialsPoint - Sales by Day of the Week | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8
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