Skip to main content
Talentd

Sales by Day of the Week - Solution & Explanation

HardPremiumFree on FleetCodeDatabase6 min readAsked at: Amazon
Practice this problem

Problem Statement

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    | 
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(ordered_id, item_id) is the primary key (combination of columns with unique values) for this table.
This table contains information on the orders placed.
order_date is the date item_id was ordered by the customer with id customer_id.

 

Table: Items

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id is the primary key (column with unique values) for this table.
item_name is the name of the item.
item_category is the category of the item.

 

You are the business owner and would like to obtain a sales report for category items and the day of the week.

Write a solution to report how many units in each category have been ordered on each day of the week.

Return the result table ordered by category.

The result format is in the following example.

 

Example 1:

Input: 
Orders table:
+------------+--------------+-------------+--------------+-------------+
| order_id   | customer_id  | order_date  | item_id      | quantity    |
+------------+--------------+-------------+--------------+-------------+
| 1          | 1            | 2020-06-01  | 1            | 10          |
| 2          | 1            | 2020-06-08  | 2            | 10          |
| 3          | 2            | 2020-06-02  | 1            | 5           |
| 4          | 3            | 2020-06-03  | 3            | 5           |
| 5          | 4            | 2020-06-04  | 4            | 1           |
| 6          | 4            | 2020-06-05  | 5            | 5           |
| 7          | 5            | 2020-06-05  | 1            | 10          |
| 8          | 5            | 2020-06-14  | 4            | 5           |
| 9          | 5            | 2020-06-21  | 3            | 5           |
+------------+--------------+-------------+--------------+-------------+
Items table:
+------------+----------------+---------------+
| item_id    | item_name      | item_category |
+------------+----------------+---------------+
| 1          | LC Alg. Book   | Book          |
| 2          | LC DB. Book    | Book          |
| 3          | LC SmarthPhone | Phone         |
| 4          | LC Phone 2020  | Phone         |
| 5          | LC SmartGlass  | Glasses       |
| 6          | LC T-Shirt XL  | T-Shirt       |
+------------+----------------+---------------+
Output: 
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Explanation: 
On Monday (2020-06-01, 2020-06-08) were sold a total of 20 units (10 + 10) in the category Book (ids: 1, 2).
On Tuesday (2020-06-02) were sold a total of 5 units in the category Book (ids: 1, 2).
On Wednesday (2020-06-03) were sold a total of 5 units in the category Phone (ids: 3, 4).
On Thursday (2020-06-04) were sold a total of 1 unit in the category Phone (ids: 3, 4).
On Friday (2020-06-05) were sold 10 units in the category Book (ids: 1, 2) and 5 units in Glasses (ids: 5).
On Saturday there are no items sold.
On Sunday (2020-06-14, 2020-06-21) were sold a total of 10 units (5 +5) in the category Phone (ids: 3, 4).
There are no sales of T-shirts.

Approach Overview

Problem Overview: You receive two tables: Orders and Items. Each order records an item_id, order_date, and quantity. The task is to report total quantity sold for each item_category across all seven days of the week (Monday through Sunday) as separate columns.

Approach 1: Join + Conditional Aggregation (O(n) time, O(1) extra space)

Start by joining Orders with Items on item_id so each order is associated with its item_category. Once joined, compute totals for each weekday using conditional aggregation. In MySQL, this is typically written as SUM(CASE WHEN DAYNAME(order_date) = 'Monday' THEN quantity ELSE 0 END). Repeat the same pattern for all seven weekdays. Group the result by item_category. The query scans the dataset once and aggregates results during grouping, which gives O(n) time complexity where n is the number of orders. No additional data structures are required beyond aggregation buffers, so extra space stays O(1).

This pattern is common in SQL analytics queries where you need to convert row-based data into column-based summaries. Conditional aggregation effectively acts as a manual pivot.

Approach 2: Numeric Weekday Mapping with CASE (O(n) time, O(1) space)

Another variation uses DAYOFWEEK(order_date) instead of DAYNAME. MySQL returns weekday numbers (1–7), which you map to Monday–Sunday using CASE expressions inside aggregation. For example: SUM(CASE WHEN DAYOFWEEK(order_date) = 2 THEN quantity ELSE 0 END) for Monday. The rest of the query stays identical: join tables, compute weekday sums, and group by item_category.

This approach avoids string comparison and relies on numeric checks instead. It performs the same logical work and keeps complexity at O(n) time and O(1) extra space. It is often preferred in database workloads because numeric comparisons can be slightly cheaper than string matching.

The core idea across both approaches is transforming row-level sales data into a pivot-style summary using aggregation. This is a fundamental technique in SQL aggregation problems.

Recommended for interviews: Use the join + conditional aggregation approach. It clearly demonstrates understanding of SQL joins, grouping, and pivot-style reporting. Interviewers expect you to recognize that the weekday columns can be computed with SUM(CASE WHEN ...) expressions during grouping rather than running multiple queries.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Join + Conditional AggregationO(n)O(1)Standard SQL solution for pivot-style reports grouped by category
DAYOFWEEK Numeric MappingO(n)O(1)When preferring numeric weekday checks instead of string comparisons

Video Solution

LeetCode Hard 1479 "Sales by Day of the Week" Amazon Interview SQL Question with Explanation • Everyday Data Science • 4,824 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Sales by Day of the Week easy or hard?
Sales by Day of the Week is rated Hard on LeetCode, but the SQL logic is straightforward once you recognize the pivot pattern. The key requirement is joining tables and using conditional aggregation to produce weekday columns in a single grouped query.
Sales by Day of the Week Python/Java solution
This problem is primarily solved using SQL because it operates directly on relational tables. If implemented in Python or Java, you would iterate through orders, map item_id to category, compute the weekday from the date, and accumulate totals in a dictionary keyed by category and weekday.
How to solve Sales by Day of the Week in O(n)?
Join the Orders and Items tables on item_id, then group by item_category. Use conditional aggregation such as SUM(CASE WHEN DAYNAME(order_date) = 'Monday' THEN quantity ELSE 0 END) for each weekday column. This scans the dataset once and aggregates totals during the group operation.
What is the best approach for Sales by Day of the Week?
The best approach uses a JOIN between Orders and Items followed by conditional aggregation with SUM(CASE WHEN ...). Each weekday column is calculated by summing quantities only when the order_date falls on that specific day. This processes all rows in a single grouped query with O(n) time complexity.
Is Sales by Day of the Week asked at Google/Amazon/Meta?
SQL aggregation and reporting problems similar to Sales by Day of the Week frequently appear in database interview rounds at companies like Amazon, Google, and Meta. They test joins, grouping, and transforming row data into summarized reports.
What data structure is used in Sales by Day of the Week?
The problem relies on relational database tables and SQL aggregation rather than traditional in-memory data structures. Internally, the database engine performs grouping and aggregation using hash or sort-based strategies.
What is the time complexity of Sales by Day of the Week?
The query runs in O(n) time where n is the number of rows in the Orders table. Each order is scanned once during aggregation, and grouping by item_category computes the weekday totals. Space overhead is O(1) aside from the grouped result set.

Ready to solve this problem?

Practice Sales by Day of the Week with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary