리트코드 : 1327. List the Products Ordered in a Period

리트코드 : 1327. List the Products Ordered in a Period

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

---

문제

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.

• 2월에 판매된 제품들 중, 매출이 100 넘는 제품의 이름과 매출 출력하기

문제 풀이

MySQL

with feb_sales as (
    select *, sum(unit) as amount
    from orders
    where order_date between '2020-02-01' and '2020-02-29'
    group by product_id
    having sum(unit) >= 100
)

select p.product_name, f.amount as unit
from feb_sales f
join products p on f.product_id = p.product_id

• CTE에 GROUP BY + HAVING을 통해서 2월의 orders table에서 제품별 합을 구한다.
• join 통해서 제품이름 출력하기.

Pandas

import pandas as pd

def list_products(products: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    orders['feb20'] = orders['order_date'].dt.strftime('%Y-%m') == '2020-02'
    grouped = orders[orders['feb20']==True].groupby('product_id').agg(
        unit = ('unit','sum')
    ).reset_index()

    answer = pd.merge(grouped[grouped['unit']>=100],products)
    return answer[['product_name','unit']]

• 날짜필터 걸어주고, group by + sum으로 그룹
• grouped에서 100이 넘는 것 들 중에서 products와 join.

코멘트

• .