TIL - 250415_(SQL_datalemur_7개)

문제1 (링크)

WITH a AS (
  SELECT
    *
    , ROW_NUMBER() OVER (PARTITION BY DATE(measurement_time) ORDER BY measurement_time) r_num
  FROM measurements
)

SELECT
  DATE(measurement_time) AS measurement_day
  , SUM(CASE WHEN r_num % 2 = 1 THEN measurement_value ELSE 0 END) AS odd_sum
  , SUM(CASE WHEN r_num % 2 = 0 THEN measurement_value ELSE 0 END) AS even_sum
FROM a
GROUP BY 1
ORDER BY 1

비고 : %는 나머지를 구하는 것이다. 나머지는 주로 홀수와 짝수를 구분할 때 많이 쓰인다.

문제2 (링크)

SELECT 
  order_id
  , COALESCE(CASE WHEN order_id % 2 = 0 THEN LAG(item) OVER (ORDER BY order_id) 
  ELSE LEAD(item) OVER (ORDER BY order_id) END,item)
FROM orders

비고 : lag()는 이전 행의 데이터, lead()는 다음 행의 데이터를 불러온다. 만약 다다음 데이터를 알고 싶으면 lead(..., 2)를 하면 된다. coalesce는 null값에 넣을 데이터를 부여할 수 있다. 이 문제의 마지막이 15인데 홀수인 경우 다음 행의 데이터를 불러오지만 마지막이라 없어서 null이 된다. 이 때 이전 item 데이터를 불러오는 것으로 coalesce를 지정했다.

문제3 (링크)

WITH abc AS (
  SELECT
    *
    , ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open) min_num
    , ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC) max_num
    , CONCAT(TO_CHAR(date,'Mon'),'-',EXTRACT(YEAR FROM date)) n_date
  FROM stock_prices
  )
  
SELECT
  ticker
  , MAX(CASE WHEN max_num = 1 THEN n_date END)
  , MAX(CASE WHEN max_num = 1 THEN open END)
  , MAX(CASE WHEN min_num = 1 THEN n_date END)
  , MAX(CASE WHEN min_num = 1 THEN open END)
FROM abc 
GROUP BY 1

비고 : CASE WHEN에서 MAX를 써야하는 이유가 있다. 조건이 아닌 경우 다 NULL 값으로 반환하기 때문에 안전하게 추출하기 위해서이다.

문제4 (링크)

SELECT
  user_id
FROM (
  SELECT
    user_id
    ,transaction_date
    ,LAG(transaction_date,1) OVER(PARTITION BY user_id ORDER BY transaction_date) one_day
    ,LAG(transaction_date,2) OVER(PARTITION BY user_id ORDER BY transaction_date) two_day
  FROM transactions
) a
WHERE one_day IS NOT NULL AND two_day IS NOT NULL
;

문제5 (링크)

WITH a AS (
  SELECT
    *
    , RANK() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) r_num
  FROM user_transactions
  )
SELECT
  transaction_date
  , user_id
  , COUNT(product_id)
FROM a 
WHERE r_num = 1
GROUP BY 2, 1
ORDER BY 1

비고 : GROUP BY 에 user_id만 했더니 안됐다. GROUP BY를 잘 보자.

문제6 (링크)

SELECT
  item_count AS mode
FROM items_per_order
WHERE order_occurrences = (SELECT MAX(order_occurrences) FROM items_per_order)

문제7 (링크)

SELECT
  card_name
  , issued_amount
FROM (
  SELECT 
    *
    , ROW_NUMBER() OVER (PARTITION BY card_name ORDER BY issue_year, issue_month) r_num
  FROM monthly_cards_issued
) a
WHERE r_num = 1
ORDER BY 2 DESC