00:00:00

Description

Editorial

Monthly Canonical Product Sales Rollup

EASY10 pts

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:
1. product_name in ascending order
2. sale_month in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

sales =

[{"sale_id":1,"product_name":"LCPHONE","sale_date":"2020-01-16"},{"sale_id":2,"product_name":" LCPhone","sale_date":"2020-01-17"},{"sale_id":3,"product_name":"LcPhOnE","sale_date":"2020-02-18"},{"sale_id":4,"product_name":"LCKeyCHAiN","sale_date":"2020-02-19"},{"sale_id":5,"product_name":"LCKeyChain","sale_date":"2020-02-28"},{"sale_id":6,"product_name":"Matryoshka","sale_date":"2020-03-31"}]

Loading problem...

101

00:00:00

Description

Editorial

Monthly Canonical Product Sales Rollup

EASY10 pts

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:
1. product_name in ascending order
2. sale_month in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

sales =

Monthly Canonical Product Sales Rollup

Hints

Monthly Canonical Product Sales Rollup

Hints