Group Sold Products By The Date - Leetcode Solution

Problem statement:

Given a table named "sales" containing data about the sales of products (sales [sale_id, product_id, sale_date]) where:

• sale_id is the id of the sale
• product_id is the id of the product
• sale_date is the date of the sale (in YYYY-MM-DD format)

Write a SQL query to group the sales of products by the date of sale and display the total number of products sold on that date. The output should be sorted by the sale_date column in ascending order.

Solution:

To solve this problem, we need to group the sales by the date of sale and count the total number of products sold on each date. We can use the GROUP BY clause to group the sales by the sale_date column and the COUNT function to count the number of products sold on each date. The solution would look like this:

SELECT sale_date, COUNT(product_id) AS total_sold
FROM sales
GROUP BY sale_date
ORDER BY sale_date ASC;

Explanation:

The SELECT statement selects the sale_date and the total number of products sold on each date. The COUNT function counts the number of products sold and renames the output column as total_sold. The FROM clause specifies the sales table. The GROUP BY clause groups the sales by the sale_date column. The ORDER BY clause sorts the output by the sale_date column in ascending order.

Testing:

Let's assume that the sales table contains the following data:

| sale_id | product_id | sale_date | |--------|------------|-----------| | 1 | 1 | 2022-01-01| | 2 | 2 | 2022-01-01| | 3 | 1 | 2022-01-02| | 4 | 3 | 2022-01-02| | 5 | 1 | 2022-01-03| | 6 | 2 | 2022-01-03| | 7 | 2 | 2022-01-03|

Applying the above SQL query on this data, we would get the following output:

| sale_date | total_sold | |-----------|------------| | 2022-01-01| 2 | | 2022-01-02| 2 | | 2022-01-03| 3 |

The output shows that on January 1, 2022, two products were sold, on January 2, 2022, two products were sold as well and on January 3, 2022, three products were sold.

1