TIL - 250413_(SQL_datalemur_7개)

문제1 (링크)

SELECT
  salary AS second_highest_salary
FROM 
  (
  SELECT
    *
    , DENSE_RANK() OVER (ORDER BY salary DESC) d_rank
  FROM employee
  ) a
WHERE d_rank = 2

비고 : 윈도우 함수 중 rank()는 중복이 있을 때 넘어간다. (ex, 1 1 1 4) 하지만 dense_rank()는 중복이 있어도 다음 숫자가 나와서 (ex, 1 1 1 2) 중복이 포함됐을 때 지금처럼 유리하게 사용할 수 있다. 그리고 row_number()는 중복된 값이어도 무조건 다른 숫자를 부여한다.

문제2 (링크)

# 1번
SELECT
  age_bucket
  , ROUND(100*SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END)
  / SUM(CASE WHEN activity_type in ('send','open') THEN time_spent ELSE 0 END),2) AS send_perc
  , ROUND(100*SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END)
  / SUM(CASE WHEN activity_type in ('send','open') THEN time_spent ELSE 0 END),2) AS open_perc
FROM activities
LEFT JOIN age_breakdown
USING (user_id)
GROUP BY 1

# 2번
WITH tb AS (
SELECT
  age_bucket
  ,SUM (CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) send_time
  ,SUM (CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END) open_time
FROM activities
LEFT JOIN age_breakdown
USING (user_id)
GROUP BY 1
ORDER BY 1
)

SELECT
  age_bucket
  ,ROUND(100 * send_time / (send_time + open_time),2)
  ,ROUND(100 * open_time / (send_time + open_time),2)
FROM tb
;

비고 : 1번은 문제 하나를 풀 때 코드를 길고 한 번에 쓰기에 멋져서 저렇게 썼다. 하지만 실무에서는 유지보수, 확장성 등을 같이 고려해야 하므로 2번 쿼리가 훨씬 좋다고 한다. CTE를 사용해 다음에 또 사용할 수 있기 때문이다.

문제3 (링크)

SELECT
  user_id
  , tweet_date
  , ROUND(AVG(tweet_count) 
  OVER (PARTITION BY user_id ORDER BY tweet_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) AS rolling_avg_3d
FROM tweets

윈도우 기본 구조는 다음과 같다.
<윈도우 함수> OVER (
PARTITION BY ...
ORDER BY ...
ROWS BETWEEN AND
)

ORDER BY 다음에 행 범위 설정이 있는데 위와 같이 하는 경우도 있지만 무제한을 선택하는 경우 unbounded preceding 또는 unbounded following으로 설정할 수 있다.

문제4 (링크)

WITH a AS(
  SELECT
    *
    , DENSE_RANK() OVER (PARTITION BY category ORDER BY total_spend DESC) d_rank
  FROM 
    (
    SELECT
      category
      , product
      , SUM(spend) total_spend
    FROM product_spend
    WHERE EXTRACT(YEAR FROM transaction_date) = 2022
    GROUP BY 1, 2
    ) a
  )

SELECT
  category
  , product
  , total_spend
FROM a 
WHERE d_rank < 3

문제5 (링크)

SELECT
  department_name
  , name 
  , salary
FROM (
  SELECT
    *
    , DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS d_rank
  FROM employee
  LEFT JOIN department
  USING (department_id)
) a
WHERE d_rank < 4
ORDER BY department_name, salary DESC, name

문제6 (링크)

SELECT
  ROUND(1.0 * SUM(CASE WHEN signup_action = 'Confirmed' THEN 1 ELSE 0 END) / COUNT(*),2)
FROM emails
RIGHT JOIN texts
USING (email_id)

문제7 (링크)

# 1번
SELECT
  customer_id
FROM 
(
  SELECT 
    customer_id
    , product_category
    , DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY product_category) d_rank
  FROM customer_contracts
  LEFT JOIN products
  USING (product_id)
) a
WHERE d_rank = 3

  
# 2번
SELECT
  customer_id
FROM customer_contracts
LEFT JOIN products
USING (product_id)
GROUP BY 1
HAVING COUNT(DISTINCT product_category)
= (SELECT COUNT(DISTINCT product_category) FROM products)

비고 : 1번의 경우 값은 정확하게 나오나 2번이 효율적인 쿼리라고 한다.