Leetcode SQL 50 -- 1484. Group Sold Products By The Date

LeetCode > SQL_50 SQL

Created At : 2024-10-31 07:15

Count:269 Views :

Question
1. Table name: Activities
Explaination
1. Type of Items
2. GROUP_CONCAT(): Rolling up multiple rows in single row
Solution

Question

Table name: `Activities`

Column Name	Type
sell_date	date
product	varchar

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.

Explaination

Again, there are two tasks we need to solve:

Count how many types of products are sold in one day
List all types of items into a single column

Type of Items

We can simply count how many products are sold using COUNT, groupped by the date they belong to:

SELECT COUNT(*) AS num_sold, 
FROM Activities
GROUP BY sell_date

However, if a product is sold twice in one day, they will be counted twice as well.
Since the question asked for the number of different products sold,
we need to specify the column we are counting, and use the keyword DINSTINCT to remove duplicates:

SELECT COUNT(DISTINCT product) AS num_sold, 
FROM Activities
GROUP BY sell_date

GROUP_CONCAT(): Rolling up multiple rows in single row

To roll data from multiple rows in single row, we may use the GROUP_CONCAT() function,
and below is the syntax:

GROUP_CONCAT(expr 
  [ORDER BY {unsigned_integer | col_name | expr} ASC | DESC]
    [SEPARATOR str_val]
)

It can be sorted with respect to other column by using ORDER BY inside the function
You may define custom string as the separator of the output (default is comma)

Solution

SELECT sell_date, COUNT(DISTINCT product) AS num_sold, 
    GROUP_CONCAT(DISTINCT product) AS products
FROM Activities
GROUP BY sell_date