리트코드 : 1321. Restaurant Growth

리트코드 : 1321. Restaurant Growth

• https://leetcode.com/problems/restaurant-growth/description/

---

문제

Table: Customer

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
In SQL,(customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
amount is the total paid by a customer.


You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).

Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.

Return the result table ordered by visited_on in ascending order.

The result format is in the following example.

• 이동평균, 이동합(?)을 구하는 문제

 

문제 풀이

MySQL

with sales_day as (
    select visited_on, sum(amount) as amount
    from customer
    group by visited_on
    order by visited_on
),
moving_avg as (
    select visited_on,
        round(sum(amount) over (order by visited_on rows between 6 preceding and current row), 2) as amount,
        round(avg(amount) over (order by visited_on rows between 6 preceding and current row), 2) as average_amount
    from sales_day
)

select *
from moving_avg
where visited_on >= (select min(visited_on) + interval 6 day from moving_avg)
order by visited_on;

• 일단 주어진 테이블이 정렬됐다는 보장이 없으므로, 날짜로 group by + order by를 진행한다.
• 두 번째 CTE에서 이동평균, 이동합을 구해준다.
• 세 번째로는 첫 날짜로 부터 6일이 흐른 시점부터 WINDOW를 적용 가능하므로 날짜 조건을 WHERE로 걸어준다.

Pandas

import pandas as pd

def restaurant_growth(customer: pd.DataFrame) -> pd.DataFrame:
    grouped = customer.groupby('visited_on').agg(
        sales_day = ('amount','sum')
    ).reset_index()
    grouped['amount'] = grouped['sales_day'].rolling(window=7).sum()
    grouped['average_amount'] = grouped['sales_day'].rolling(window=7).mean().round(2)
    return grouped[~grouped['average_amount'].isnull()][['visited_on','amount','average_amount']]

• rolling으로 window size 고정 시키는 메서드가 존재한다
• groupby로 날짜 별 매출을 묶고, rolling + 집계함수를 통해서 문제풀이

코멘트

• 잘 못짜서 그런거겠지만, 뭔가 SQL도 그렇고 pandas도 그렇고 2%씩 쿼리 로직에 나사가 빠진느낌