[SQL 코드카타] 116 ~ 117번 (LeetCode)

116번 restaurant-growth/description

> 푸는 데 시간이 좀 걸린 문제..

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 + amount1 + amount2 + amount3 + amount4 + amount5 + amount6 as amount,
round((amount + amount1 + amount2 + amount3 + amount4 + amount5 + amount6)/7,2) as average_amount
from 
  (select visited_on,
  amount,
  lag(amount, 1) over() as amount1,
  lag(amount, 2) over() as amount2,
  lag(amount, 3) over() as amount3,
  lag(amount, 4) over() as amount4,
  lag(amount, 5) over() as amount5,
  lag(amount, 6) over() as amount6
  from (select visited_on, sum(amount) as amount from Customer group by 1) as temp2
  group by 1) as temp
group by 1
having visited_on >= date_add((select visited_on from Customer limit 1), interval 6 day)
order by 1

> 다른 방법

WITH cte AS(
SELECT
	visited_on,
	SUM(amount) amount
FROM
    Customer
GROUP BY
    visited_on
),
cte1 AS(
SELECT
    visited_on,
	SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 preceding and CURRENT ROW) amount,
	ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 preceding and CURRENT ROW),2) average_amount,
	ROW_NUMBER()OVER(ORDER BY visited_on) RN
FROM
    cte)
SELECT
    visited_on,
    amount,
    average_amount
FROM
    cte1
WHERE
    RN > 6
ORDER BY
    visited_on

 

117번 friend-requests-ii-who-has-the-most-friends

with friends_list as(
select
    requester_id as id, accepter_id as friends_id
from
    requestaccepted
union all
select
    accepter_id as id, requester_id as friends_id
from requestaccepted
)
select
    id, count(id) as num
from
    friends_list
group by
    id
order by
    num desc
limit 1 ;