Daily Distinct Product Rollup

Easy

A retail analytics pipeline records one row per observed sale event. Table: activities sell_date: calendar day of the sale event product: sold product name There is no primary key; duplicate rows are allowed Task: For each sell_date, build a compact daily summary with: num_sold: number of distinct products sold on that date products: comma-separated list of distinct product names in lexicographic order Output requirements: Return exactly these columns in this order: sell_date num_sold products Sort the final result by sell_date ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

activities:
| sell_date  | product    |
|------------|------------|
| 2024-05-30 | Headphone  |
| 2024-06-01 | Pencil     |
| 2024-06-02 | Mask       |
| 2024-05-30 | Basketball |
| 2024-06-01 | Bible      |
| 2024-06-02 | Mask       |
| 2024-05-30 | TShirt     |

Output

[
  {"sell_date":"2024-05-30","num_sold":3,"products":"Basketball,Headphone,TShirt"},
  {"sell_date":"2024-06-01","num_sold":2,"products":"Bible,Pencil"},
  {"sell_date":"2024-06-02","num_sold":1,"products":"Mask"}
]

Explanation

Duplicate rows do not increase num_sold. Product names are deduplicated and listed in lexicographic order per day.

Example 2

Input

activities:
| sell_date  | product |
|------------|---------|
| 2025-01-10 | alpha   |
| 2025-01-10 | alpha   |
| 2025-01-10 | alpha   |
| 2025-01-11 | beta    |
| 2025-01-11 | gamma   |
| 2025-01-11 | beta    |

Output

[
  {"sell_date":"2025-01-10","num_sold":1,"products":"alpha"},
  {"sell_date":"2025-01-11","num_sold":2,"products":"beta,gamma"}
]

Explanation

Each day is computed independently. Repeated appearances of the same product on one day are counted once.

Example 3

Input

activities:
| sell_date  | product |
|------------|---------|
| 2023-09-04 | p2      |
| 2023-09-04 | p10     |
| 2023-09-04 | p1      |
| 2023-09-03 | x       |

Output

[
  {"sell_date":"2023-09-03","num_sold":1,"products":"x"},
  {"sell_date":"2023-09-04","num_sold":3,"products":"p1,p10,p2"}
]

Explanation

Lexicographic sorting is string-based, so p10 appears between p1 and p2.

Constraints

1 <= number of rows in activities <= 500000
sell_date is a valid YYYY-MM-DD date
1 <= length of product <= 100
activities may contain duplicate rows
A product can appear multiple times on the same date
Distinct counting is per sell_date
products must include each distinct daily product exactly once
products must be lexicographically ordered within each date
Return exactly three columns: sell_date, num_sold, products
Order final rows by sell_date ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: sell_date, num_sold, products

activities: | sell_date | product | |------------|------------| | 2024-05-30 | Headphone | | 2024-06-01 | Pencil | | 2024-06-02 | Mask | | 2024-05-30 | Basketball | | 2024-06-01 | Bible | | 2024-06-02 | Mask | | 2024-05-30 | TShirt |

[ {"sell_date":"2024-05-30","num_sold":3,"products":"Basketball,Headphone,TShirt"}, {"sell_date":"2024-06-01","num_sold":2,"products":"Bible,Pencil"}, {"sell_date":"2024-06-02","num_sold":1,"products":"Mask"} ]

activities: | sell_date | product | |------------|---------| | 2025-01-10 | alpha | | 2025-01-10 | alpha | | 2025-01-10 | alpha | | 2025-01-11 | beta | | 2025-01-11 | gamma | | 2025-01-11 | beta |