Weekly Category Sales Matrix

Hard

A retail intelligence team needs a compact weekly report that summarizes sold units by product category. Table: orders order_id: order identifier customer_id: customer identifier order_date: purchase date of the line item item_id: ordered item identifier quantity: units purchased for that item line (order_id, item_id) is unique Table: items item_id: unique item identifier item_name: descriptive item name item_category: business category label Task: For every category in items, compute total ordered quantity for each weekday bucket: Monday Tuesday Wednesday Thursday Friday Saturday Sunday Output requirements: Return columns in this exact order: category Monday Tuesday Wednesday Thursday Friday Saturday Sunday Include categories with no orders (all weekday totals must be 0). Sort by category in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

orders:
| order_id | customer_id | order_date | item_id | quantity |
|----------|-------------|------------|---------|----------|
| 1        | 101         | 2026-01-05 | A1      | 8        |
| 2        | 101         | 2026-01-12 | A2      | 6        |
| 3        | 202         | 2026-01-06 | A1      | 5        |
| 4        | 303         | 2026-01-07 | P1      | 4        |
| 5        | 404         | 2026-01-08 | P2      | 3        |
| 6        | 505         | 2026-01-09 | G1      | 7        |
| 7        | 606         | 2026-01-09 | A1      | 2        |
| 8        | 707         | 2026-01-11 | P2      | 5        |

items:
| item_id | item_name            | item_category |
|---------|----------------------|---------------|
| A1      | Algorithms Handbook  | Book          |
| A2      | Query Playbook       | Book          |
| P1      | Alpha Phone          | Phone         |
| P2      | Beta Phone           | Phone         |
| G1      | Smart Glasses        | Glasses       |
| T1      | Cotton Tee           | T-Shirt       |

Output

[
  {"category":"Book","Monday":14,"Tuesday":5,"Wednesday":0,"Thursday":0,"Friday":2,"Saturday":0,"Sunday":0},
  {"category":"Glasses","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":7,"Saturday":0,"Sunday":0},
  {"category":"Phone","Monday":0,"Tuesday":0,"Wednesday":4,"Thursday":3,"Friday":0,"Saturday":0,"Sunday":5},
  {"category":"T-Shirt","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0}
]

Explanation

Categories are grouped using items.item_category. Every ordered row contributes its quantity to exactly one weekday bucket. T-Shirt appears with all zeros because it has no matching orders.

Example 2

Input

orders:
| order_id | customer_id | order_date | item_id | quantity |
|----------|-------------|------------|---------|----------|
| 100      | 1           | 2026-02-02 | I10     | 3        |
| 100      | 1           | 2026-02-02 | I11     | 2        |
| 101      | 2           | 2026-02-03 | I12     | 4        |
| 102      | 2           | 2026-02-09 | I12     | 1        |

items:
| item_id | item_name      | item_category |
|---------|----------------|---------------|
| I10     | Fast Charger   | Accessories   |
| I11     | USB-C Cable    | Accessories   |
| I12     | Wireless Mouse | Peripherals   |

Output

[
  {"category":"Accessories","Monday":5,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0},
  {"category":"Peripherals","Monday":1,"Tuesday":4,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0}
]

Explanation

The same order_id can include multiple item rows. Totals are still aggregated by category and weekday.

Example 3

Input

orders:
| order_id | customer_id | order_date | item_id | quantity |
|----------|-------------|------------|---------|----------|

items:
| item_id | item_name | item_category |
|---------|-----------|---------------|
| B1      | Novel     | Books         |
| B2      | Comics    | Books         |
| S1      | Pen       | Stationery    |

Output

[
  {"category":"Books","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0},
  {"category":"Stationery","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0}
]

Explanation

When there are no orders, every category from items must still appear with zeros for all weekday columns.

Constraints

0 <= number of rows in orders <= 300000
0 <= number of rows in items <= 200000
item_id is unique in items
(order_id, item_id) is unique in orders
orders.item_id references items.item_id
1 <= order_id, customer_id <= 2147483647
order_date is a valid calendar date
1 <= quantity <= 1000000
Return exactly eight columns: category, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
Include categories with zero sales
Sort output by category ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the exact required columns

orders: | order_id | customer_id | order_date | item_id | quantity | |----------|-------------|------------|---------|----------| | 1 | 101 | 2026-01-05 | A1 | 8 | | 2 | 101 | 2026-01-12 | A2 | 6 | | 3 | 202 | 2026-01-06 | A1 | 5 | | 4 | 303 | 2026-01-07 | P1 | 4 | | 5 | 404 | 2026-01-08 | P2 | 3 | | 6 | 505 | 2026-01-09 | G1 | 7 | | 7 | 606 | 2026-01-09 | A1 | 2 | | 8 | 707 | 2026-01-11 | P2 | 5 | items: | item_id | item_name | item_category | |---------|----------------------|---------------| | A1 | Algorithms Handbook | Book | | A2 | Query Playbook | Book | | P1 | Alpha Phone | Phone | | P2 | Beta Phone | Phone | | G1 | Smart Glasses | Glasses | | T1 | Cotton Tee | T-Shirt |

[ {"category":"Book","Monday":14,"Tuesday":5,"Wednesday":0,"Thursday":0,"Friday":2,"Saturday":0,"Sunday":0}, {"category":"Glasses","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":7,"Saturday":0,"Sunday":0}, {"category":"Phone","Monday":0,"Tuesday":0,"Wednesday":4,"Thursday":3,"Friday":0,"Saturday":0,"Sunday":5}, {"category":"T-Shirt","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0} ]

orders: | order_id | customer_id | order_date | item_id | quantity | |----------|-------------|------------|---------|----------| | 100 | 1 | 2026-02-02 | I10 | 3 | | 100 | 1 | 2026-02-02 | I11 | 2 | | 101 | 2 | 2026-02-03 | I12 | 4 | | 102 | 2 | 2026-02-09 | I12 | 1 | items: | item_id | item_name | item_category | |---------|----------------|---------------| | I10 | Fast Charger | Accessories | | I11 | USB-C Cable | Accessories | | I12 | Wireless Mouse | Peripherals |

[ {"category":"Accessories","Monday":5,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0}, {"category":"Peripherals","Monday":1,"Tuesday":4,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0} ]

orders: | order_id | customer_id | order_date | item_id | quantity | |----------|-------------|------------|---------|----------| items: | item_id | item_name | item_category | |---------|-----------|---------------| | B1 | Novel | Books | | B2 | Comics | Books | | S1 | Pen | Stationery |

[ {"category":"Books","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0}, {"category":"Stationery","Monday":0,"Tuesday":0,"Wednesday":0,"Thursday":0,"Friday":0,"Saturday":0,"Sunday":0} ]