Group Sold Products By The Date

Difficulty: Easy | Acceptance: 77.90% | Paid: No Topics: Database

Table: Activities +-------------+---------+ | Column Name | Type | +-------------+---------+ | sell_date | date | | product | varchar | +-------------+---------+ There is no primary key 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 an SQL query to find for each date the number of different products sold and their names.

The sold products names for each date should be listed in alphabetical order and separated by comma ’,‘.

Return the result table ordered by sell_date.

The query result format is in the following example.

• Examples
• Constraints
• Approach 1: MySQL Group Concatenation
• Approach 2: PostgreSQL/SQL Server String Aggregation

Examples

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, the items sold were (Headphone, Basketball, T-Shirt), sorted in alphabetical order we have (Basketball, Headphone, T-Shirt). For 2020-06-01, the items sold were (Pencil, Bible), sorted in alphabetical order we have (Bible, Pencil). For 2020-06-02, the items sold were (Mask, Mask), sorted in alphabetical order we have (Mask).

Constraints

The sell_date is between 2020-01-01 and 2020-12-31.
The product length is between 1 and 30.

Approach 1: MySQL Group Concatenation

Intuition We need to aggregate the data by sell_date. For each date, we count the distinct products and concatenate their names into a single string, sorted alphabetically.

Steps

• Group the table by sell_date.
• Use COUNT(DISTINCT product) to get the number of unique products sold on that date.
• Use GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') to create the comma-separated list of product names, ensuring they are distinct and sorted.
• Order the final result by sell_date.

# Write your MySQL query statement below
SELECT sell_date, COUNT(DISTINCT product) AS num_sold, GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products FROM Activities GROUP BY sell_date ORDER BY sell_date

Complexity

• Time: O(N log N) due to the sorting required within the string aggregation function.
• Space: O(N) to store the aggregated results.
• Notes: The GROUP_CONCAT function is specific to MySQL. Other SQL dialects use different functions for string aggregation.

Approach 2: PostgreSQL/SQL Server String Aggregation

Intuition Similar to the MySQL approach, we group by date and aggregate. However, PostgreSQL and SQL Server use the STRING_AGG function instead of GROUP_CONCAT.

Steps

• Group the table by sell_date.
• Use COUNT(DISTINCT product) to calculate num_sold.
• Use STRING_AGG(DISTINCT product, ',' ORDER BY product) to generate the products string. Note that DISTINCT inside STRING_AGG is supported in PostgreSQL but might not be supported in older versions of SQL Server (where a subquery or WITHIN GROUP is used).
• Order the result by sell_date.

# Write your PostgreSQL/SQL Server query statement below
SELECT sell_date, COUNT(DISTINCT product) AS num_sold, STRING_AGG(DISTINCT product, ',' ORDER BY product) AS products FROM Activities GROUP BY sell_date ORDER BY sell_date

Complexity

• Time: O(N log N) due to sorting.
• Space: O(N) for storing the result.
• Notes: STRING_AGG is the standard way to concatenate strings in modern PostgreSQL and SQL Server.