[LeetCode - Easy] Group Sold Products By The Date ( GROUP_CONCAT 여러 행에 있는 데이터 연결해 출력하기 )

LeetCode 1484. Group Sold Products By The Date

Problem

Difficulty : Easy

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 sorted lexicographically. Return the result table ordered by sell_date.

-- Expected 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                         |
+------------+----------+------------------------------+

My Solution

1. COUNT the number of distinct product names BY sell_date
2. Use GROUP_CONCAT to concatenate products from multiple rows into products field. (When concatenating, it should be sorted lexicographically.)
3. ORDER BY sell_date

-- sell_date, num_sold, products  
SELECT 
    sell_date,
    COUNT(DISTINCT product) AS `num_sold`,
    -- The sold products names sorted lexicographically.
    GROUP_CONCAT(DISTINCT product 
                 ORDER BY product ASC
                SEPARATOR ',') AS `products`
FROM Activities
GROUP BY 1
-- ordered by sell_date
ORDER BY 1

⇒ Runtime: 380 ms, faster than 84.75% of MySQL online submissions for Group Sold Products By The Date.

Lessons learned

GROUP_CONCAT : to concatenate data from multiple rows into one field.

SELECT col1, col2, ..., colN, 
                GROUP_CONCAT ( 
                                            [DISTINCT] col_name1
                                            [ORDER BY clause]  
                                            [SEPARATOR str_val] 
                                            )
FROM table_name 
GROUP BY col_name2;

• Distinct : It eliminates the repetition of values from result.
• Order By :It sort the values of group in specific order and then concatenate them.
• Separator : By default, the values of group are separated by , operator.
  • Separator ‘str_value’.

---

Source/Reference : MySQL | Group_CONCAT() Function - GeeksforGeeks