Monthly Canonical Product Sales Rollup

Easy

A retail analytics team receives product-sale rows typed manually by different operators. The same product can appear with inconsistent case and extra spaces, which breaks monthly trend dashboards. Table: sales sale_id: unique sale event identifier product_name: raw product name entered at transaction time sale_date: calendar date of the sale Canonicalization rules: Convert product_name to lowercase. Remove leading and trailing spaces from product_name. Convert sale_date to reporting month format YYYY-MM. Task: Count sales per (canonical product_name, reporting month). Output requirements: Return exactly these columns in this order: product_name sale_month total Sort by: product_name in ascending order sale_month in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

sales:
| sale_id | product_name   | sale_date   |
|---------|----------------|-------------|
| 1       | LCPHONE        | 2020-01-16  |
| 2       |  LCPhone       | 2020-01-17  |
| 3       | LcPhOnE        | 2020-02-18  |
| 4       | LCKeyCHAiN     | 2020-02-19  |
| 5       | LCKeyChain     | 2020-02-28  |
| 6       | Matryoshka     | 2020-03-31  |

Output

[
  {"product_name":"lckeychain","sale_month":"2020-02","total":2},
  {"product_name":"lcphone","sale_month":"2020-01","total":2},
  {"product_name":"lcphone","sale_month":"2020-02","total":1},
  {"product_name":"matryoshka","sale_month":"2020-03","total":1}
]

Explanation

Case and surrounding spaces are normalized before grouping. Two January rows map to lcphone, and two February rows map to lckeychain.

Example 2

Input

sales:
| sale_id | product_name        | sale_date   |
|---------|---------------------|-------------|
| 10      |   Noise Canceler    | 2024-01-01  |
| 11      | noise canceler      | 2024-01-09  |
| 12      | Noise Canceler      | 2024-02-02  |
| 13      | Smart Plug          | 2024-01-11  |
| 14      | smart plug          | 2024-01-15  |

Output

[
  {"product_name":"noise canceler","sale_month":"2024-01","total":2},
  {"product_name":"noise canceler","sale_month":"2024-02","total":1},
  {"product_name":"smart plug","sale_month":"2024-01","total":2}
]

Explanation

Rows are grouped by canonicalized names and month buckets extracted from sale_date.

Example 3

Input

sales:
| sale_id | product_name | sale_date   |
|---------|--------------|-------------|
| 20      | Gadget       | 2023-12-31  |
| 21      |  gadget      | 2024-01-01  |
| 22      | GADGET       | 2024-01-02  |
| 23      | Cable        | 2024-01-03  |

Output

[
  {"product_name":"cable","sale_month":"2024-01","total":1},
  {"product_name":"gadget","sale_month":"2023-12","total":1},
  {"product_name":"gadget","sale_month":"2024-01","total":2}
]

Explanation

Month extraction is independent per row date, so year transitions are handled naturally.

Constraints

1 <= number of rows in sales <= 300000
sale_id is unique
1 <= sale_id <= 1000000000
product_name is a non-null text field
1 <= length(product_name) <= 255
sale_date is a valid date in YYYY-MM-DD format
Canonical product_name uses lowercase + trim (leading/trailing spaces only)
Return exactly columns: product_name, sale_month, total
Sort by product_name ASC, sale_month ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

sales: | sale_id | product_name | sale_date | |---------|----------------|-------------| | 1 | LCPHONE | 2020-01-16 | | 2 | LCPhone | 2020-01-17 | | 3 | LcPhOnE | 2020-02-18 | | 4 | LCKeyCHAiN | 2020-02-19 | | 5 | LCKeyChain | 2020-02-28 | | 6 | Matryoshka | 2020-03-31 |

[ {"product_name":"lckeychain","sale_month":"2020-02","total":2}, {"product_name":"lcphone","sale_month":"2020-01","total":2}, {"product_name":"lcphone","sale_month":"2020-02","total":1}, {"product_name":"matryoshka","sale_month":"2020-03","total":1} ]

sales: | sale_id | product_name | sale_date | |---------|---------------------|-------------| | 10 | Noise Canceler | 2024-01-01 | | 11 | noise canceler | 2024-01-09 | | 12 | Noise Canceler | 2024-02-02 | | 13 | Smart Plug | 2024-01-11 | | 14 | smart plug | 2024-01-15 |

[ {"product_name":"noise canceler","sale_month":"2024-01","total":2}, {"product_name":"noise canceler","sale_month":"2024-02","total":1}, {"product_name":"smart plug","sale_month":"2024-01","total":2} ]

sales: | sale_id | product_name | sale_date | |---------|--------------|-------------| | 20 | Gadget | 2023-12-31 | | 21 | gadget | 2024-01-01 | | 22 | GADGET | 2024-01-02 | | 23 | Cable | 2024-01-03 |