Sales by Day of the Week

Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


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.

Solution

Method 1 - Pivot by Day of Week

Intuition

We need to aggregate sales by item category and day of the week. This is a classic pivot problem: group by category and weekday, then pivot the weekday column into separate columns.

Approach

Join Orders and Items on item_id to get item_category.
Extract the day of the week from order_date (Monday, Tuesday, …).
Group by item_category and day of week, sum quantity.
Pivot the day of week into columns, fill missing with 0.
Order by category.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SELECT
  i.item_category AS Category,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 2 THEN o.quantity ELSE 0 END) AS Monday,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 3 THEN o.quantity ELSE 0 END) AS Tuesday,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 4 THEN o.quantity ELSE 0 END) AS Wednesday,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 5 THEN o.quantity ELSE 0 END) AS Thursday,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 6 THEN o.quantity ELSE 0 END) AS Friday,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 7 THEN o.quantity ELSE 0 END) AS Saturday,
  SUM(CASE WHEN DAYOFWEEK(o.order_date) = 1 THEN o.quantity ELSE 0 END) AS Sunday
FROM Orders o
JOIN Items i ON o.item_id = i.item_id
GROUP BY i.item_category
ORDER BY i.item_category ASC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SELECT
  i.item_category AS Category,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 1 THEN o.quantity ELSE 0 END) AS Monday,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 2 THEN o.quantity ELSE 0 END) AS Tuesday,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 3 THEN o.quantity ELSE 0 END) AS Wednesday,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 4 THEN o.quantity ELSE 0 END) AS Thursday,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 5 THEN o.quantity ELSE 0 END) AS Friday,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 6 THEN o.quantity ELSE 0 END) AS Saturday,
  SUM(CASE WHEN EXTRACT(DOW FROM o.order_date) = 0 THEN o.quantity ELSE 0 END) AS Sunday
FROM Orders o
JOIN Items i ON o.item_id = i.item_id
GROUP BY i.item_category
ORDER BY i.item_category ASC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


# Orders and Items are pandas DataFrames
import pandas as pd
def sales_by_day_of_week(Orders, Items):
    df = Orders.merge(Items, on='item_id')
    df['order_date'] = pd.to_datetime(df['order_date'])
    df['weekday'] = df['order_date'].dt.day_name()
    pivot = df.pivot_table(index='item_category', columns='weekday', values='quantity', aggfunc='sum', fill_value=0)
    # Ensure all days in correct order
    days = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday']
    for d in days:
        if d not in pivot.columns:
            pivot[d] = 0
    pivot = pivot[days]
    pivot = pivot.reset_index().rename(columns={'item_category':'Category'})
    return pivot
# result = sales_by_day_of_week(Orders, Items)

Complexity

⏰ Time complexity: O(N) (N = number of orders)
🧺 Space complexity: O(C) (C = number of categories)

Problem#

Examples#

Solution#

Method 1 - Pivot by Day of Week#

Intuition#

Approach#

Code#

Complexity#