Skip to main content
Talentd

Group Sold Products By The Date - Solution & Explanation

EasyDatabase6 min readAsked at: Amazon, Meta, Google
Practice this problem

Problem Statement

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.

Approach Overview

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 1: Use SQL Query with GROUP BY and STRING_AGG

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.

Code

SQL

SQL

Complexity

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.

Try this approach in the editor →

Approach 2: Manual Processing in Code

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.

Code

PythonJava

Python

Java

Complexity

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.

Try this approach in the editor →

Approach 3: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
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.
Space Complexity: O(n), for storing products for each date.

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.
Space Complexity: O(n), to store entries in dictionary structures.

Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL GROUP BY with STRING_AGGO(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

Video Solution

LeetCode 1484 Interview SQL Question with Detailed Explanation | GROUP_CONCAT() | Practice SQL • Everyday Data Science • 10,634 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Group Sold Products By The Date easy or hard?
LeetCode classifies this problem as Easy. The main requirement is understanding SQL aggregation with GROUP BY and generating a sorted concatenated string of unique values.
Group Sold Products By The Date Python/Java solution
In Python or Java, iterate through the rows and store products in a hash map keyed by sell_date. Use a set to keep products unique, then sort the set and join with commas to build the output string. This approach runs in O(n log n) time and O(n) space.
How to solve Group Sold Products By The Date in O(n)?
Pure O(n) time is difficult because the product list must be sorted alphabetically. The standard solution groups rows by date using a hash map or SQL GROUP BY, then sorts the distinct products for each date before joining them into a string, leading to O(n log n) complexity.
What is the best approach for Group Sold Products By The Date?
The best approach is using SQL aggregation with GROUP BY and STRING_AGG. GROUP BY clusters rows by sell_date, COUNT(DISTINCT product) computes the number of unique products, and STRING_AGG with ORDER BY builds a sorted comma-separated list. This solution is concise and runs in roughly O(n log n) time due to sorting.
Is Group Sold Products By The Date asked at Google/Amazon/Meta?
Database aggregation problems similar to this appear in interviews at companies like Amazon, Google, and Meta. They test familiarity with SQL GROUP BY, DISTINCT counting, and string aggregation functions such as STRING_AGG or GROUP_CONCAT.
What data structure is used in Group Sold Products By The Date?
In SQL, the database engine internally uses grouping and aggregation structures to collect rows per date. In application code solutions, a hash map (dictionary) maps each date to a set of product names, ensuring uniqueness before sorting and joining them.
What is the time complexity of Group Sold Products By The Date?
The typical solution runs in O(n log n) time. Rows are grouped by date in O(n), but sorting the distinct product names for each group introduces a log factor. Space complexity ranges from O(k) in SQL aggregations to O(n) when grouping with hash maps in application code.

Ready to solve this problem?

Practice Group Sold Products By The Date with our built-in code editor and test cases.

Practice on FleetCode
Previous

Finding the Topic of Each Post