14. Group Sold Products By the Date

Problem

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.

Problem Definition

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.

Example

1705955123871096 Image scaled to 35%

Output

1705955139707757 Image scaled to 55%

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

To solve this problem, the approach involves using SQL queries to analyze the Activities table and find the number of different products sold and their names for each date. The desired output requires counting the distinct products and listing their names for each unique sell_date.

The COUNT function with the DISTINCT keyword is used to calculate the number of different products sold on each date. This information is captured in the “num_sold” column. Additionally, the GROUP_CONCAT function concatenates the distinct product names for each date. The ORDER BY clause is used within the GROUP_CONCAT function to ensure that the product names are sorted lexicographically.

The results are then grouped by sell_date using the GROUP BY clause, ensuring that the calculations are performed for each unique date. The final step involves ordering the result table by sell_date in ascending order, as the problem statement specifies.


SELECT sell_date,
       Count(DISTINCT product) AS num_sold,
       Group_concat(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
FROM   Activities
GROUP  BY sell_date
ORDER  BY sell_date ASC;

Let’s break down the query step by step:

Step 1: Count the Number of Different Products Sold for Each Date

We use the COUNT(DISTINCT product) to count the number of different products sold for each date.


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

Output After Step 1:


+------------+----------+
| sell_date  | num_sold |
+------------+----------+
| 2020-05-30 | 3        |
| 2020-06-01 | 2        |
| 2020-06-02 | 1        |
+------------+----------+

Step 2: Concatenate Sorted Product Names for Each Date

We use GROUP_CONCAT to concatenate the product names for each date, sorting them lexicographically.


SELECT
    sell_date,
    num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
FROM (
    -- Sub-Step 1 output goes here
) AS subquery
GROUP BY
    sell_date;

Output After Step 2:


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

Step 3: Order the Result

We order the result table by sell_date.


SELECT
    sell_date,
    num_sold,
    products
FROM (
    -- Sub-Step 2 output goes here
) AS final_result
ORDER BY
    sell_date ASC;

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