Watch 10 video solutions for Group Sold Products By The Date, a easy level problem involving Database. This walkthrough by Everyday Data Science has 10,282 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table Activities:
+-------------+---------+ | Column Name | Type | +-------------+---------+ | sell_date | date | | product | varchar | +-------------+---------+ There is no primary key (column with unique values) for this table. It may contain duplicates. Each row of this table contains the product name and the date it was sold in a market.
Write a solution to find for each date the number of different products sold and their names.
The sold products names for each date should be sorted lexicographically.
Return the result table ordered by sell_date.
The result format is in the following example.
Example 1:
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 | +------------+----------+------------------------------+ Explanation: For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma. For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma. For 2020-06-02, the Sold item is (Mask), we just return it.
Problem Overview: The table stores product sales with sell_date and product. The task is to group rows by date, count the number of unique products sold on each date, and return the product names as a comma-separated list sorted alphabetically.
Approach 1: SQL GROUP BY with STRING_AGG (O(n log n) time, O(k) space)
This is the intended database solution. Use GROUP BY sell_date to aggregate all rows for the same day. The number of unique products comes from COUNT(DISTINCT product). To produce the comma-separated list, use STRING_AGG(DISTINCT product, ',') with an ORDER BY product clause so the products appear in alphabetical order. Sorting inside the aggregation leads to roughly O(n log n) time across all groups, while intermediate storage for distinct values takes O(k) space per date. This approach directly leverages database aggregation features and is the most concise solution for SQL and database problems.
Approach 2: Manual Processing with Hash Map (O(n log n) time, O(n) space)
If you process the data in application code (Python or Java), group rows using a hash map where the key is sell_date and the value is a set of products. Iterate through the dataset once and insert each product into the corresponding set. After grouping, iterate through each date, convert the set to a sorted list, and join the values with commas to form the output string. The grouping step runs in O(n) time with hash lookups, while sorting the unique products for each date contributes the O(n log n) cost overall. Space complexity is O(n) because all grouped products must be stored in memory. This mirrors the SQL logic but implements it using hash table grouping and in-memory sorting.
Recommended for interviews: The SQL GROUP BY solution is the expected answer for database interviews because it demonstrates familiarity with aggregation functions and string aggregation techniques. The manual hash map approach is useful for understanding the underlying logic and translating the same grouping pattern into general-purpose programming languages.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL GROUP BY with STRING_AGG | O(n log n) | O(k) | Best for SQL/database queries where aggregation and ordered string concatenation are supported |
| Manual Hash Map Grouping (Python/Java) | O(n log n) | O(n) | Useful when processing records in application code rather than directly in SQL |
