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.
This approach employs SQL to directly query the database. We will use GROUP BY to group products by sell_date. The DISTINCT keyword helps count unique products per date, and the STRING_AGG function (or similar) concatenates product names sorted lexicographically.
The SQL query utilizes GROUP BY to aggregate rows by sell_date. COUNT(DISTINCT product) calculates the number of unique products for each date. STRING_AGG, a SQL function, concatenates distinct product names sorted alphabetically, resulting in a comma-separated string for the products column.
SQL
Time Complexity: O(n log n), where n is the number of entries. Sorting the products contributes a log factor.
Space Complexity: O(n), for storing products for each date.
This approach involves reading all records into a suitable data structure in a chosen programming language, then processing the data manually. We use dictionaries or hashmaps to group products by date. For each date, extract unique products, sort them, and store the results.
The Python solution uses a defaultdict to group products by sell_date. We iterate over each record, adding each product to the set corresponding to its date (eliminating duplicates). After grouping, the dates are sorted. For each date, product names are sorted lexicographically, and results are constructed displaying the date, number of unique products, and a comma-separated list of products.
Time Complexity: O(n log n + m log m), where n is the number of records and m is the number of unique products for a specific date due to sorting operations.
Space Complexity: O(n), to store entries in dictionary structures.
MySQL
| Approach | Complexity |
|---|---|
| Use SQL Query with GROUP BY and STRING_AGG | Time Complexity: O(n log n), where n is the number of entries. Sorting the products contributes a log factor. |
| Manual Processing in Code | Time Complexity: O(n log n + m log m), where n is the number of records and m is the number of unique products for a specific date due to sorting operations. |
| Default Approach | — |
| 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 |
LeetCode 1484 Interview SQL Question with Detailed Explanation | GROUP_CONCAT() | Practice SQL • Everyday Data Science • 10,282 views views
Watch 9 more video solutions →Practice Group Sold Products By The Date with our built-in code editor and test cases.
Practice on FleetCode