SQL 문제 풀이 챌린지(16~20)

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

16. 초기 사용자의 친구 관계 찾기

https://solvesql.com/problems/friendship-between-early-users/

WITH r AS (
  SELECT
    user_a_id,
    user_b_id,
    user_a_id + user_b_id AS id_sum,
    DENSE_RANK() OVER (ORDER BY user_a_id + user_b_id ASC) AS dr,
    COUNT(*) OVER () AS total_cnt
  FROM edges
),
cut AS (
  SELECT MIN(dr) AS cutoff_dr
  FROM (
    SELECT
      dr,
      SUM(COUNT(*)) OVER (ORDER BY dr) AS cum_cnt,
      MAX(total_cnt) AS total_cnt
    FROM r
    GROUP BY dr
  ) x
  WHERE cum_cnt / total_cnt >= 0.001
)
SELECT
  r.user_a_id,
  r.user_b_id,
  r.id_sum
FROM r
  INNER JOIN cut AS c ON r.dr <= c.cutoff_dr
ORDER BY r.id_sum ASC

 

 

 

17. 신규 유입을 견인하는 카테고리

https://solvesql.com/problems/first-order-category/

SELECT
  r.category AS category,
  r.sub_category AS sub_category,
  COUNT(DISTINCT r.order_id) AS cnt_orders
FROM records r
  INNER JOIN customer_stats c ON r.customer_id = c.customer_id
  AND r.order_date = c.first_order_date
GROUP BY
  r.category,
  r.sub_category
ORDER BY
  cnt_orders DESC;

 

18. 인플루언서 마케팅 후보 찾기

https://solvesql.com/problems/influencer-marketing-candidates/

WITH all_edges AS (
  SELECT user_a_id AS user_id, user_b_id AS friend_id FROM edges
  UNION ALL
  SELECT user_b_id AS user_id, user_a_id AS friend_id FROM edges
)
,friend_cnt AS (
  SELECT
    user_id,
    COUNT(*) AS friends
  FROM all_edges
  GROUP BY user_id
)
,friends_of_friends AS (
  SELECT
    ae.user_id,
    SUM(fc.friends) AS friends_of_friends
  FROM all_edges ae
    INNER JOIN friend_cnt fc ON ae.friend_id = fc.user_id
  GROUP BY ae.user_id
)

SELECT
  f.user_id,
  f.friends,
  fof.friends_of_friends,
  ROUND(fof.friends_of_friends / f.friends, 2) AS ratio
FROM friend_cnt f
  INNER JOIN friends_of_friends fof ON f.user_id = fof.user_id
WHERE f.friends >= 100
ORDER BY ratio DESC
LIMIT 5

19. 연도별 순매출 구하기

https://solvesql.com/problems/yearly-net-sales/

SELECT
  YEAR(purchased_at) AS year,
  SUM(total_price - discount_amount) AS net_sales
FROM transactions
WHERE is_returned = 0
GROUP BY YEAR(purchased_at)
ORDER BY YEAR(purchased_at)

20. 연도별 배송 업체 이용 내역 분석하기
https://solvesql.com/problems/yearly-shipping-usage/

SELECT 
    YEAR(purchased_at) AS year,
    SUM(CASE WHEN shipping_method = 'Standard' THEN 1 ELSE 0 END) + 
    SUM(CASE WHEN is_returned = TRUE THEN 1 ELSE 0 END) AS standard,
    SUM(CASE WHEN shipping_method = 'Express' THEN 1 ELSE 0 END) AS express,
    SUM(CASE WHEN shipping_method = 'Overnight' THEN 1 ELSE 0 END) AS overnight
FROM transactions
WHERE MONTH(purchased_at) IN (11, 12)
  AND YEAR(purchased_at) BETWEEN 2018 AND 2023
  AND is_online_order = TRUE
GROUP BY YEAR(purchased_at)
ORDER BY year