Group Sold Products By The Date

Group Sold Products By The Date - Problem

Database Easy

Given a table Activities that contains product sales data, write a SQL solution to find for each date:

The number of different products sold
The names of all products sold (sorted alphabetically)

The table may contain duplicate entries for the same product sold on the same date.

Requirements:

Group results by sell_date
Count distinct products per date
Concatenate product names in lexicographical order
Order final results by sell_date

Table Schema

Activities

Column Name	Type	Description
`sell_date`	date	Date when the product was sold
`product`	varchar	Name of the product sold

Note: No primary key - may contain duplicates for same product on same date

Input & Output

Example 1 — Multiple Products Per Date

Input 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-06-02	T-Shirt

Output:

sell_date	num_sold	products
2020-05-30	2	Basketball,Headphone
2020-06-01	2	Bible,Pencil
2020-06-02	2	Mask,T-Shirt

💡 Note:

Notice how duplicate Mask entries on 2020-06-02 are counted only once. Products are sorted alphabetically: Basketball,Headphone, Bible,Pencil, and Mask,T-Shirt.

Example 2 — Single Product Per Date

Input Table:

sell_date	product
2020-01-01	Apple
2020-01-02	Orange
2020-01-03	Apple

Output:

sell_date	num_sold	products
2020-01-01	1	Apple
2020-01-02	1	Orange
2020-01-03	1	Apple

💡 Note:

Each date has only one unique product, so num_sold is 1 for all dates. Same products can appear on different dates.

Constraints

1 ≤ Activities.length ≤ 1000
sell_date is a valid date
product is a non-empty string

Visualization

Tap to expand

Asked in

A Amazon 15 M Meta 12 M Microsoft 8

Use GROUP BY with COUNT(DISTINCT) and STRING_AGG(DISTINCT ... ORDER BY) to group by date, count unique products, and concatenate sorted product names.

Table Schema

Activities

Column Name	Type	Description
`sell_date`	date	Date when the product was sold
`product`	varchar	Name of the product sold

Note: No primary key - may contain duplicates for same product on same date

Common Approaches

✓ GROUP BY with String Aggregation

⏱️ Time: O(n log n) Space: O(n)

Use GROUP BY to group records by sell_date, then apply COUNT(DISTINCT) to count unique products and STRING_AGG to concatenate product names in alphabetical order.

GROUP BY with String Aggregation — Algorithm Steps

Step 1: Group all records by sell_date
Step 2: Count distinct products per date
Step 3: Concatenate unique product names alphabetically
Step 4: Order results by sell_date

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

Group by sell_date

Count

Count distinct products

Aggregate

Concatenate sorted products

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Grouping and sorting products within each group

⚡ Linearithmic

Space Complexity

O(n)

Storage for grouped results and concatenated strings

⚡ Linearithmic Space

28.5K Views

Medium Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

GROUP BY with String Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler