Leetcode SQL 50 -- 1327. List the Products Ordered in a Period

LeetCode > SQL_50 SQL

Created At : 2024-11-01 13:42

Count:239 Views :

Question
1. Table name: Products
2. Table name: Orders
Explaination
1. Retrieve the amount of products
2. SELECT based on Time Range
Solution
List of part in EXTRACT() function

Question

Table name: `Products`

Column Name	Type
product_id	int
product_name	varchar
product_category	varchar

This table contains data about the company’s products.

Table name: `Orders`

Column Name	Type
product_id	int
order_date	date
unit	int

unit is the number of products ordered in order_date.

Write a solution to get the names of products,
that have at least 100 units ordered in February 2020 and their amount.
Return the result table in any order.

Explaination

Retrieve the amount of products

Here, we can use SUM() function. Similar to COUNT(),
we can use GROUP BY so that multiple rows that contain the same product_name will sum up into one row.

SELECT based on Time Range

To validate the date, we can use EXTRACT() function.

EXTRACT(part FROM date)

<part> in this function refers to specific piece of information about the time.

Here, we can use YEAR_MONTH to check whether the date in within February 2020.

For all possible parts defined by SQL, you may check the list below.

Solution

SELECT p.product_name, SUM(o.unit) AS unit
FROM Products p 
JOIN Orders o ON p.product_id = o.product_id 
    AND EXTRACT(YEAR_MONTH FROM order_date) = 202002
GROUP BY product_name
HAVING unit >= 100

List of `part` in `EXTRACT()` function

Here’s the list of all possible component you can extract from date:

Parts
MICROSECOND	WEEK	MINUTE_MICROSECOND	DAY_MICROSECOND
SECOND	MONTH	MINUTE_SECOND	DAY_SECOND
MINUTE	QUARTER	HOUR_MICROSECOND	DAY_MINUTE
HOUR	YEAR	HOUR_SECOND	DAY_HOUR
DAY	SECOND_MICROSECOND	HOUR_MINUTE	YEAR_MONTH