Group Sold Products By The Date - Problem
📊 Group Sold Products By The Date
You're working as a data analyst for a bustling marketplace that tracks daily sales. Your task is to create a sales report that shows, for each day, how many unique products were sold and what those products were.
Given an Activities table that records each product sale with its date, you need to:
- 🔢 Count the number of different products sold each day
- 📝 List all product names for each day (sorted alphabetically)
- 📅 Order the results by date
The challenge here is handling duplicate entries - the same product might be sold multiple times on the same day, but we only want to count and list each product once per day.
| Column Name | Type |
|---|---|
| sell_date | date |
| product | varchar |
Note: This table has no primary key and may contain duplicates.
Input & Output
example_1.sql — Basic Grouping
$
Input:
Activities table:
+------------+------------+
| sell_date | product |
+------------+------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+------------+
›
Output:
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-Shirt|
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
💡 Note:
For 2020-05-30, we sold 3 different products (Basketball, Headphone, T-Shirt) listed alphabetically. For 2020-06-01, we sold 2 products (Bible, Pencil). For 2020-06-02, even though Mask appears twice, we count it as 1 unique product.
example_2.sql — Duplicate Products
$
Input:
Activities table:
+------------+----------+
| sell_date | product |
+------------+----------+
| 2019-12-12 | LeetCode |
| 2019-12-12 | LeetCode |
| 2019-12-12 | Coding |
| 2020-01-01 | Book |
+------------+----------+
›
Output:
+------------+----------+-----------------+
| sell_date | num_sold | products |
+------------+----------+-----------------+
| 2019-12-12 | 2 | Coding,LeetCode |
| 2020-01-01 | 1 | Book |
+------------+----------+-----------------+
💡 Note:
On 2019-12-12, LeetCode was sold twice but we only count unique products, so we have 2 unique products: Coding and LeetCode (sorted alphabetically). On 2020-01-01, only Book was sold.
example_3.sql — Single Product Day
$
Input:
Activities table:
+------------+---------+
| sell_date | product |
+------------+---------+
| 2020-01-01 | Apple |
| 2020-01-01 | Apple |
| 2020-01-01 | Apple |
+------------+---------+
›
Output:
+------------+----------+---------+
| sell_date | num_sold | products|
+------------+----------+---------+
| 2020-01-01 | 1 | Apple |
+------------+----------+---------+
💡 Note:
Even though Apple was sold 3 times on the same date, we only count unique products, so the result shows 1 unique product.
Visualization
Tap to expand
Understanding the Visualization
1
Collect Sales Records
Throughout the day, vendors record: 'Apple sold', 'Banana sold', 'Apple sold again'
2
Group by Date
At day's end, organize all records by date: 'What was sold today?'
3
Remove Duplicates
Count each product only once per day, even if sold multiple times
4
Sort & Format
Create final report: 'Today we sold 3 items: Apple, Banana, Orange'
Key Takeaway
🎯 Key Insight: SQL's GROUP BY automatically partitions data by date, while DISTINCT ensures we count each product only once per day, creating clean daily summaries from messy transaction logs.
Time & Space Complexity
Time Complexity
O(n log n)
Single pass through data O(n) plus sorting of products within each group O(k log k) where k is products per day
⚡ Linearithmic
Space Complexity
O(n)
Space for storing intermediate grouped results and sorted products
⚡ Linearithmic Space
Constraints
- 1 ≤ Activities table rows ≤ 1000
- 1 ≤ product name length ≤ 20
- Product names contain only lowercase letters, uppercase letters, and spaces
- sell_date is a valid date in format YYYY-MM-DD
- No primary key - table may contain exact duplicate rows
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code