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.
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.
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.
Java
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.
| 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. |
LeetCode 1484 Interview SQL Question with Detailed Explanation | GROUP_CONCAT() | Practice SQL • Everyday Data Science • 8,754 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