Group sold products by the date - SQL

I encountered an issue while solving the LeetCode question "Group sold products by date.

 

Question

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.

 

The difficulty of this problem is easy. However, it contained some code that I was unfamiliar with. I had no idea how to list all the product names in one column, separated by commas.

 

I tried using concat, but it was difficult to extract values based on sell_date since they were all in one column. I had no choice but to search on Google, and I found out group_concat and string_agg functions.

 

:GROUP_CONCAT

GROUP_CONCAT is used to concatenate values from multiple rows into a single string result.

 

GROUP_CONCAT(DISTINCT column_name ORDER BY column_name SEPARATOR ',')

 

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;

 

Result

# Result:
sell_date	num_sold	products
2020-05-30	3	Basketball,Headphone,T-Shirt
2020-06-01	2	Bible,Pencil
2020-06-02	1	Mask

 

 

 

STRING_AGG

STRING_AGG is used to concatenate values from multiple rows into a single string with a specified delimiter.

 

STRING_AGG(DISTINCT column_name, ',' ORDER BY column_name)

 

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;

 

Result

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