116. Restaurant Growth

[문제]

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

 

[풀이]

1. 날짜순으로 정렬한 뒤, 그룹화하여 더해준다. 

2. rolling 함수를 이용해서 윈도우 크기를 7일로 해주고, 이동합계를 구한다.

3. 마찬가지로 이동합계에 7을 나눠서 이동평균을 구해준다.

4. 최소 날짜에서 6일을 더해 7일 평균이 계산된 행들만 필터링 해준다.

 

Pandas

import pandas as pd

def restaurant_growth(customer: pd.DataFrame) -> pd.DataFrame:
    df = customer.sort_values("visited_on").groupby("visited_on")[["amount"]].sum()
    df = df.assign(
        amount = df.rolling("7D").sum(),
        average_amount = round(df.rolling("7D").sum()/7, 2)
    )

    return df[df.index >= df.index.min() + pd.DateOffset(6)].reset_index()

 

SQL

SELECT
    a.visited_on AS visited_on,
    SUM(b.day_sum) AS amount,
    ROUND(AVG(b.day_sum), 2) AS average_amount
FROM 
    (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on) a
    , (SELECT visited_on, SUM(amount) AS day_Sum FROM Customer GROUP BY visited_on) b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on
HAVING COUNT(b.visited_on) = 7

SELECT
    visited_on
    , (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount
    , ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM Customer c
WHERE visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) 
    FROM Customer
)
GROUP BY visited_on

SELECT
    visited_on
    , amount
    , ROUND(amount/7, 2) AS average_amount
FROM 
(
    SELECT
        DISTINCT visited_on
        , SUM(amount) OVER (
            ORDER BY visited_on
            RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
        ) AS amount
    FROM Customer
) sub
WHERE visited_on >= (
    SELECT MIN(visited_on) + INTERVAL 6 DAY FROM Customer
)