SQL문제풀기

[SQL 문제 풀이] List the Products Ordered in a Period (해당 기간 동안 주문한 제품 나열)

May 21, 2025

[SQL 문제 풀이] List the Products Ordered in a Period (해당 기간 동안 주문한 제품 나열)

Contents

내가 작성한 쿼리

List the Products Ordered in a Period - LeetCode

Can you solve this real interview question? List the Products Ordered in a Period - Table: Products +------------------+---------+ | Column Name | Type | +------------------+---------+ | product_id | int | | product_name | varchar | | product_category | varchar | +------------------+---------+ product_id is the primary key (column with unique values) for this table. This table contains data about the company's products. Table: Orders +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | order_date | date | | unit | int | +---------------+---------+ This table may have duplicate rows. product_id is a foreign key (reference column) to the Products table. 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. The result format is in the following example. Example 1: Input: Products table: +-------------+-----------------------+------------------+ | product_id | product_name | product_category | +-------------+-----------------------+------------------+ | 1 | Leetcode Solutions | Book | | 2 | Jewels of Stringology | Book | | 3 | HP | Laptop | | 4 | Lenovo | Laptop | | 5 | Leetcode Kit | T-shirt | +-------------+-----------------------+------------------+ Orders table: +--------------+--------------+----------+ | product_id | order_date | unit | +--------------+--------------+----------+ | 1 | 2020-02-05 | 60 | | 1 | 2020-02-10 | 70 | | 2 | 2020-01-18 | 30 | | 2 | 2020-02-11 | 80 | | 3 | 2020-02-17 | 2 | | 3 | 2020-02-24 | 3 | | 4 | 2020-03-01 | 20 | | 4 | 2020-03-04 | 30 | | 4 | 2020-03-04 | 60 | | 5 | 2020-02-25 | 50 | | 5 | 2020-02-27 | 50 | | 5 | 2020-03-01 | 50 | +--------------+--------------+----------+ Output: +--------------------+---------+ | product_name | unit | +--------------------+---------+ | Leetcode Solutions | 130 | | Leetcode Kit | 100 | +--------------------+---------+ Explanation: Products with product_id = 1 is ordered in February a total of (60 + 70) = 130. Products with product_id = 2 is ordered in February a total of 80. Products with product_id = 3 is ordered in February a total of (2 + 3) = 5. Products with product_id = 4 was not ordered in February 2020. Products with product_id = 5 is ordered in February a total of (50 + 50) = 100.

https://leetcode.com/problems/list-the-products-ordered-in-a-period/description/

List the Products Ordered in a Period - LeetCode

notion image


2020년 2월에 최소 100개 이상 주문된 제품의 이름과 수량을 확인할 수 있는 솔루션을 작성하세요.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

Oracle


SELECT 
    P.product_name,
    SUM(O.unit) unit
FROM Products P
JOIN Orders O
ON P.product_id = O.product_id
WHERE TO_CHAR(order_date, 'YYYY-MM') = '2020-02'
GROUP BY P.product_name
HAVING SUM(O.unit) >= 100;

MySQL


SELECT 
    product_name,
    SUM(unit) unit
FROM Products P
JOIN Orders O
ON P.product_id = O.product_id
WHERE order_date LIKE '2020-02%'
GROUP BY O.product_id
HAVING SUM(unit) >= 100

차이점

특정 날짜를 제한하는 방식의 차이

Share article

More articles

[JAVA 문제 풀이] 272. 멀리 뛰기

[JAVA 문제 풀이] 272. 멀리 뛰기

[JAVA 문제 풀이] 271. 문자열 내 마음대로 정렬하기

[JAVA 문제 풀이] 271. 문자열 내 마음대로 정렬하기

[SQL 문제 풀이] Duplicate Emails (중복 이메일)

[SQL 문제 풀이] Duplicate Emails (중복 이메일)

[JAVA 문제 풀이] 270. 귤 고르기

[JAVA 문제 풀이] 270. 귤 고르기

stupefyee

RSS·Powered by Inblog