00:00:00

Description

Editorial

Weekly Category Sales Matrix

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

[{"item_id":"A1","item_name":"Algorithms Handbook","item_category":"Book"},{"item_id":"A2","item_name":"Query Playbook","item_category":"Book"},{"item_id":"P1","item_name":"Alpha Phone","item_category":"Phone"},{"item_id":"P2","item_name":"Beta Phone","item_category":"Phone"},{"item_id":"G1","item_name":"Smart Glasses","item_category":"Glasses"},{"item_id":"T1","item_name":"Cotton Tee","item_category":"T-Shirt"}]

orders =

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

Loading problem...

101

00:00:00

Description

Editorial

Weekly Category Sales Matrix

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

orders =

Weekly Category Sales Matrix

Hints

Weekly Category Sales Matrix

Hints