LEETCODE 1484 - Group Sold Products By The Date - Solution in MySQL

LEETCODE 1484 - Group Sold Products By The Date - Solution in MySQL | LEETCODE 1484 Database SQL Solution

LEETCODE | 1484

Group Sold Products By The Date

LEETCODE 1484 - Group Sold Products By The Date - Solution in MySQL | LEETCODE 1484 Database SQL Solution :

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 sorted lexicographically.

Return the result table ordered by sell_date.

The query 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.

Solution of LEETCODE 1484 - Group Sold Products By The Date in MySQL:

Let's breakdown the steps that we are required to do in this before jumping into SQL:

The only serious challange of this problem is how to aggregate the product names in one cell. In MySql, this can be done using GROUP_CONCAT, in which you can also specify the sorting mechanism for the group concatenation (aggregation). The rest is simple.

There's no need to use SEPARATOR in group_concat here. group_concat automatically does it for you.

There is no need of 'ORDER BY product' since default is ASC

Now with above information we can create our query as following:

SELECT 

    sell_date,

    COUNT(DISTINCT product) AS num_sold,

    GROUP_CONCAT(DISTINCT product ORDER BY product) AS products

FROM Activities

GROUP BY sell_date;

Next Problem:

LEETCODE 1527 - Patients With a Condition - Solution in MySQL | LEETCODE 1527 Database SQL Solution

Previous Problem: 

LEETCODE 1667- Fix Names in a Table - Solution in MySQL | LEETCODE 1667 Database SQL Solution

Ashraf LEETCODE Solutions