1484. Group Sold Products By The Date

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.

 

어떻게 접근해야 하나 한참을 고민하는데, 난이도를 보니 Easy였다.

 

Easy 문제에 이렇게 고민할 리가 없다고 생각했고, 역시나 그러했다. 함수 하나만 써주면 되는 문제였던 것.

그 함수는 GROUP_CONCAT()

 

-- 풀이
select sell_date
     , count(distinct product) as num_sold
     , group_concat(distinct product order by product) as products
  from Activities
  group by sell_date
  order by sell_date;

 

 

GROUP_CONCAT() 함수에 대한 소개는 아래의 포스팅을 참고하도록 하자.

 

[STUDY/SQL] - [MySQL] - GROUP_CONCAT()

[MySQL] - GROUP_CONCAT()