구름 아카이브

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

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

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

11. 레스토랑의 주중, 주말 매출액 비교하기

https://solvesql.com/problems/revenue-weekday-weekend/

SELECT
  week,
  sum(total_bill) AS sales
FROM (
  SELECT
    *,
    CASE 
      WHEN day IN ('Sat', 'Sun') THEN 'weekend' ELSE 'weekday'
    END AS week
  FROM tips
) AS sub
GROUP BY week
ORDER By sales DESC

12. 장르, 연도별 게임 평론가 점수 구하기
https://solvesql.com/problems/critic-scores-by-genre-and-year/

SELECT
  genre,
  MAX(CASE WHEN year = '2011' THEN score END) AS score_2011,
  MAX(CASE WHEN year = '2012' THEN score END) AS score_2012,
  MAX(CASE WHEN year = '2013' THEN score END) AS score_2013,
  MAX(CASE WHEN year = '2014' THEN score END) AS score_2014,
  MAX(CASE WHEN year = '2015' THEN score END) AS score_2015
FROM (
  SELECT 
    year,
    genres.name AS genre,
    ROUND(AVG(critic_score),2) AS score
  FROM games
    INNER JOIN genres ON games.genre_id = genres.genre_id
  WHERE critic_score IS NOT NULL
  GROUP BY year, genres.name
) AS sub
GROUP BY genre

13. 매출이 높은 배우 찾기
https://solvesql.com/problems/top-revenue-actors/

# 조인키 찾는 문제인가요... 매출 높은 배우 찾는 문제인가요? ....ㅠㅠ

SELECT
    a.first_name,
    a.last_name,
    SUM(p.amount) AS total_revenue
FROM actor a
INNER JOIN film_actor fa
    ON a.actor_id = fa.actor_id
INNER JOIN film f
    ON fa.film_id = f.film_id
INNER JOIN inventory i
    ON f.film_id = i.film_id
INNER JOIN rental r
    ON i.inventory_id = r.inventory_id
INNER JOIN payment p
    ON r.rental_id = p.rental_id
GROUP BY
    a.actor_id,
    a.first_name,
    a.last_name
ORDER BY
    total_revenue DESC
LIMIT 5;

14. 이달의 작가 후보 찾기
https://solvesql.com/problems/monthly-author-candidates/

SELECT
    author
FROM books
WHERE genre = 'Fiction'
GROUP BY
    author
HAVING
    COUNT(*) >= 2
    AND AVG(user_rating) >= 4.5
    AND AVG(reviews) >= (
        SELECT AVG(reviews)
        FROM books
        WHERE genre = 'Fiction'
    )
ORDER BY
    author

15. 한국 감독의 영화 찾기

https://solvesql.com/problems/find-movies-by-korean-artists/

SELECT
    ar.name AS artist,
    aw.title
FROM artists ar
  INNER JOIN artworks_artists aa ON ar.artist_id = aa.artist_id
  INNER JOIN artworks aw ON aa.artwork_id = aw.artwork_id
WHERE ar.nationality = 'Korean'
  AND aw.classification LIKE 'Film%';

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

6. DVD 대여점 우수 고객 찾기

https://solvesql.com/problems/dvdrental-vip/

SELECT
  R.customer_id AS customer_id
FROM rental AS R
  INNER JOIN customer AS C ON R.customer_id = C.customer_id
WHERE active = 1
GROUP BY R.customer_id
HAVING COUNT(*) >= 35

7. 이틀 연속 미세먼지가 나빠진 날
https://solvesql.com/problems/bad-finddust-days-in-a-row/

SELECT
  measured_at AS date_alert
FROM (
    SELECT
        measured_at,
        pm10,
        LAG(pm10, 1) OVER (ORDER BY measured_at) AS prev_pm10,
        LAG(pm10, 2) OVER (ORDER BY measured_at) AS prev2_pm10
    FROM measurements
) AS sub

WHERE pm10 >= 30
AND pm10 > prev_pm10
AND prev_pm10 > prev2_pm10

8. 크리스마스를 기념할 완벽한 와인 찾기 🥂

https://solvesql.com/problems/wines-for-friends/

WITH total_avg AS (
  SELECT
    AVG(density) AS avg_density,
    AVG(residual_sugar) AS avg_residual_sugar
  FROM wines
)
, white_avg AS (
  SELECT
    AVG(pH) AS avg_ph,
    AVG(citric_acid) AS avg_citric_acid
  FROM wines
  WHERE color = 'white'
)

SELECT *
FROM wines
WHERE color = 'white'
  AND quality >= 7
  AND density > (SELECT avg_density FROM total_avg)
  AND residual_sugar > (SELECT avg_residual_sugar FROM total_avg)
  AND pH < (SELECT avg_ph FROM white_avg)
  AND citric_acid > (SELECT avg_citric_acid FROM white_avg);

9. 두 대회 연속으로 출전한 기록이 있는 배구 선수

https://solvesql.com/problems/volleyball-players-in-two-consecutive-olympics/

WITH event_record AS (
  SELECT
    year,
    name,
    a.id
  FROM records AS r
      INNER JOIN teams AS t ON r.team_id = t.id
      INNER JOIN events AS e ON r.event_id = e.id              
      INNER JOIN games AS g ON r.game_id = g.id
      INNER JOIN athletes AS a ON r.athlete_id = a.id
  WHERE team = 'KOR'
  AND event = 'Volleyball Women''s Volleyball'
)

, if_sequence AS (
    SELECT 
      year,
      name,
      id,
      LAG(year) OVER (PARTITION BY id ORDER BY year) AS prev_year
    FROM event_record
)

SELECT DISTINCT
  id,
  name
FROM if_sequence
WHERE year = prev_year + 4

10. 올림픽 메달이 있는 배구 선수
https://solvesql.com/problems/volleyball-players-with-medals/

WITH event_record AS (
  SELECT
    a.id,
    name,
    medal
  FROM records AS r
      INNER JOIN teams AS t ON r.team_id = t.id
      INNER JOIN events AS e ON r.event_id = e.id              
      INNER JOIN games AS g ON r.game_id = g.id
      INNER JOIN athletes AS a ON r.athlete_id = a.id
  WHERE team = 'KOR'
  AND event = 'Volleyball Women''s Volleyball'
  AND medal IS NOT NULL
)

SELECT
  id,
  name,
  GROUP_CONCAT(DISTINCT medal ORDER BY medal SEPARATOR ',') AS medals
FROM event_record
GROUP BY id, name

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

1. 사랑에 대한 영화 찾기

https://solvesql.com/problems/movies-about-love/

SELECT
  title,
  year,
  rotten_tomatoes
FROM movies
WHERE LOWER(title) LIKE "%love%"
ORDER BY rotten_tomatoes DESC, year DESC

2. 서울숲에 놀러 가기 좋은 날
https://solvesql.com/problems/good-days-for-a-seoulforest-picnic/

SELECT measured_at AS good_day
FROM measurements
WHERE pm2_5 <= 9
AND MONTH(measured_at) = 12

3. 펭귄의 종과 몸무게 조회하기
https://solvesql.com/problems/species-and-mass-of-penguins/

SELECT
  species,
  body_mass_g
FROM penguins
WHERE species IS NOT NULL
AND body_mass_g IS NOT NULL
ORDER BY body_mass_g DESC, species ASC

4. 12월 우수 고객 찾기
https://solvesql.com/problems/whales-of-december/

SELECT
  customer_id
FROM records
WHERE DATE_FORMAT(order_date, "%Y-%m") = "2020-12" 
GROUP BY customer_id
HAVING sum(sales) >= 1000

5. 스탬프를 찍어드려요
https://solvesql.com/problems/count-stamps/

SELECT
    CASE
        WHEN total_bill >= 25 THEN 2
        WHEN total_bill >= 15 THEN 1
        ELSE 0
    END AS stamp,
    COUNT(*) AS count_bill
FROM tips
GROUP BY stamp
ORDER BY stamp ASC

SELECT CONCAT(
    SUBSTRING('산타의', 1, 0), 'Ho ',
    SUBSTRING('웃음', 1, 0), 'Ho ',
    SUBSTRING('소리', 1, 0), 'Ho'
) AS 'Santa_Laughing_Loudly'

링크:  https://school.programmers.co.kr/learn/courses/30/lessons/301651

습득한 점:

재귀함수 사용법

활용 아이디어

재귀함수 쿼리 

WITH RECURSIVE CTE(ID,PARENT_ID,GEN_N) as (

    SELECT
        ID,
        PARENT_ID,
        1 AS GEN_N
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL -- 중복 미제거 통합, 재귀에서는 거의 항상 UNION ALL 사용
    
    SELECT
        r.ID,
        r.PARENT_ID,
        cte.GEN_N + 1 AS GEN_N
    FROM ECOLI_DATA r
        INNER JOIN cte ON r.PARENT_ID = cte.ID
)


SELECT
    *
    # COUNT(A.GEN_N) AS COUNT,
    # A.GEN_N AS GENERATION
FROM cte AS A
    LEFT JOIN cte AS B ON A.ID = B.PARENT_ID
# WHERE B.PARENT_ID IS NULL
# GROUP BY A.GEN_N
# ORDER BY GENERATION ASC;

1. 재귀함수로 각 ID별 세대를 GEN_ID라는 컬럼으로 생성한다.

2. ID별 세대를 조건에 맞춰 지정 후, 
셀프 조인(A테이블, B테이블)을 통해,  ID와  PARENT_ID가 서로 일치하는 조건으로  LEFT 조인을 한다.

   -> 자식이 존재하지 않는 ID를 찾기 위함

이후, B테이블의 ID가  "NULL" 인 값만을 찾도록 조건을 걸고, A테이블의 GEN_ID(세대)를 기준으로 그룹화하되, 그룹별 카운트를 한다.

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

25. 메리 크리스마스 2024

SELECT 
    CONCAT(
        CHAR(77), CHAR(101), CHAR(114), CHAR(114), CHAR(121), CHAR(32), 
        CHAR(67), CHAR(104), CHAR(114), CHAR(105), CHAR(115), CHAR(116), CHAR(109), CHAR(97), CHAR(115), CHAR(33)
    ) AS Advent_of_SQL_2024;

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

24. 세 명이 서로 친구인 관계 찾기
https://solvesql.com/problems/friend-group-of-3/

SELECT
    e1.user_a_id AS user_a_id,
    e1.user_b_id AS user_b_id,
    e2.user_b_id AS user_c_id
FROM
    edges e1
INNER JOIN edges e2 ON e1.user_b_id = e2.user_a_id
INNER JOIN edges e3 ON e1.user_a_id = e3.user_a_id AND e3.user_b_id = e2.user_b_id
WHERE
    3820 IN (e1.user_a_id, e1.user_b_id, e2.user_b_id)
    AND e1.user_a_id < e1.user_b_id
    AND e1.user_b_id < e2.user_b_id
ORDER BY
    e1.user_a_id, e1.user_b_id, e2.user_b_id

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

23. 유량(Flow)와 저량(Stock)

https://solvesql.com/problems/flow-and-stock/

-- 첫번째 풀이


WITH yearly_count AS (
    SELECT 
      EXTRACT(YEAR FROM acquisition_date) AS acquisition_year,
      COUNT(*) AS Flow
    FROM artworks
    WHERE EXTRACT(YEAR FROM acquisition_date) IS NOT NULL
    GROUP BY EXTRACT(YEAR FROM acquisition_date)
)

, Cumulative AS (
    SELECT
      Acquisition_year,
      Flow,
      SUM(Flow) OVER (ORDER BY Acquisition_year) AS Cumulative_Sum
    FROM yearly_count
)

SELECT
  Acquisition_year AS "Acquisition year",
  Flow AS "New acquisitions this year (Flow)",
  Cumulative_Sum AS "Total collection size (Stock)"
FROM Cumulative
WHERE Flow > 0
ORDER BY Acquisition_year






-- 두번째 풀이

WITH yearly_count AS (
    SELECT 
      LEFT(acquisition_date, 4) AS Acquisition_year,
      COUNT(*) AS Flow
    FROM artworks
    WHERE LEFT(acquisition_date, 4) IS NOT NULL
    GROUP BY LEFT(acquisition_date, 4)
)

, cumulative_stock AS (
    SELECT
      Acquisition_year,
      Flow,
      SUM(Flow) OVER (ORDER BY Acquisition_year) AS Cumulative_Sum
    FROM yearly_count
)

, comparison_prev_year AS (
    SELECT
      Acquisition_year,
      Flow AS Flow,
      Cumulative_Sum AS Stock,

      CASE WHEN LAG(Cumulative_Sum) OVER(ORDER BY Acquisition_year) IS NULL THEN 0 
      ELSE LAG(Cumulative_Sum) OVER(ORDER BY Acquisition_year)
      END AS LAG_Cumulative_Sum,

      CASE WHEN Cumulative_Sum = LAG(Cumulative_Sum) OVER(ORDER BY Acquisition_year) THEN 'target' 
      ELSE 'non_target'
      END AS comparison

    FROM cumulative_stock
)

SELECT
  Acquisition_year AS "Acquisition year",
  Flow AS "New acquisitions this year (Flow)",
  Stock AS "Total collection size (Stock)"
FROM comparison_prev_year
WHERE comparison = 'non_target'
ORDER BY Acquisition_year

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

22. 친구 수 집계하기

https://solvesql.com/problems/number-of-friends/

#### 1.WHERE NOT IN 활용

  (
  SELECT 
    user_id,
    COUNT(*) AS num_friends
  FROM(
    SELECT user_a_id AS user_id FROM edges
    UNION ALL
    SELECT user_b_id AS user_id FROM edges
  ) combined

  GROUP BY user_id

)

UNION ALL

  (
  SELECT 
    user_id,
    0 AS num_friends
  FROM users
  WHERE user_id NOT IN (
      SELECT user_a_id AS user_id FROM edges
      UNION
      SELECT user_b_id AS user_id FROM edges
  )

  GROUP BY user_id

)
ORDER BY num_friends DESC, user_id ASC







#### 2.LEFT JOIN 활용

-- 1. edges에서 모든 사용자에 대해 친구 수 계산
WITH friend_counts AS (
  
  SELECT 
      user_id,
      COUNT(*) AS num_friends
  FROM (
      SELECT user_a_id AS user_id FROM edges
      UNION ALL
      SELECT user_b_id AS user_id FROM edges
  ) combined
  GROUP BY user_id
)

-- 2. users 테이블의 모든 사용자 가져오기
, all_users AS (
  SELECT user_id FROM users
)

-- 3. 모든 사용자와 친구 수 결합
SELECT 
    all_users.user_id,
    COALESCE(friend_counts.num_friends, 0) AS num_friends
FROM all_users
  LEFT JOIN friend_counts ON all_users.user_id = friend_counts.user_id
ORDER BY num_friends DESC, all_users.user_id ASC

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

21. 세션 유지 시간을 10분으로 재정의하기
https://solvesql.com/problems/redefine-session-2/

WITH user_event AS (
    SELECT 
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      LAG(event_timestamp_kst) OVER(ORDER BY event_timestamp_kst) AS prev_event_time
    FROM ga
    WHERE user_pseudo_id = 'a8Xu9GO6TB'
)

, group_reset AS (
    SELECT
      user_pseudo_id,
      event_name,
      event_timestamp_kst,
      ga_session_id,
      CASE
          WHEN TIMESTAMPDIFF(MINUTE, prev_event_time, event_timestamp_kst) > 10 OR prev_event_time IS NULL THEN 1 ELSE 0 
      END AS new_group
    FROM user_event
)

, regrouping AS (
    SELECT
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      SUM(new_group) OVER(ORDER BY event_timestamp_kst) AS new_session_id
    FROM group_reset
    
)

SELECT
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      new_session_id
FROM regrouping
ORDER BY event_timestamp_kst

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

20. 미세먼지 수치의 계절간 차이

https://solvesql.com/problems/finedust-seasonal-summary/

WITH preprocessed_table AS (

    SELECT
      measured_at,
      season,
      pm10,
      ROW_NUMBER() OVER(PARTITION BY season ORDER BY pm10) AS row_num,
      COUNT(*) OVER(PARTITION BY season) AS cnt

    FROM (
          SELECT
            measured_at,
            CASE
                WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring'
                WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer'
                WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn'
                ELSE 'winter'
            END AS season,
            pm10
          FROM measurements
    ) sub
)

, average_table AS(

    SELECT 
      season,
      ROUND(AVG(pm10),2) AS pm10_average
    FROM preprocessed_table
    GROUP BY season
)



, median_table AS (

    SELECT 
      season,
      AVG(pm10) AS pm10_median
    FROM preprocessed_table
    WHERE row_num IN (FLOOR((cnt+1) /2), FLOOR((cnt+2) /2))
    GROUP BY season

)




-- 테이블 통합 = avg + median

SELECT
  average_table.season AS season,
  pm10_median,
  pm10_average

FROM average_table
    INNER JOIN median_table ON average_table.season = median_table.season
ORDER BY FIELD(season, 'spring', 'summer', 'autumn', 'winter')

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

19. 전국 카페 주소 데이터 정제하기

https://solvesql.com/problems/refine-cafe-address/

SELECT
  SUBSTRING_INDEX(sido, ' ', 1) AS sido,
  SUBSTRING_INDEX(sido, ' ', -1) AS sigungu,
  cnt

FROM(
    SELECT
        REGEXP_SUBSTR(address, '^[^ ]+ [^ ]+', 1, 1) AS sido,
        COUNT(*) AS cnt
    FROM cafes
    GROUP BY REGEXP_SUBSTR(address, '^[^ ]+ [^ ]+', 1, 1)
) address_group

ORDER BY cnt DESC

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

18. 펭귄 날개와 몸무게의 상관 계수

https://solvesql.com/problems/correlation-penguin/

SELECT 
    penguins.species,
    ROUND(
        SUM((penguins.flipper_length_mm - avg_values.avg_fl) * (penguins.body_mass_g - avg_values.avg_bm)) /
        SQRT(SUM(POWER(penguins.flipper_length_mm - avg_values.avg_fl, 2)) * 
             SUM(POWER(penguins.body_mass_g - avg_values.avg_bm, 2))),
    3) AS corr
FROM penguins,
    (SELECT 
         species,
         AVG(flipper_length_mm) AS avg_fl, 
         AVG(body_mass_g) AS avg_bm
     FROM penguins
     GROUP BY species) AS avg_values
WHERE penguins.species = avg_values.species
GROUP BY penguins.species

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

17. 멀티 플랫폼 게임 찾기
https://solvesql.com/problems/multiplatform-games/

WITH major_platform_game AS (

    SELECT
      games.name AS game_name,
      games.platform_id,
      games.publisher_id,
      companies.company_id,
      companies.name AS company_name,
      platforms.name AS platform_name,
      CASE 
          WHEN platforms.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'SONY'
          WHEN platforms.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
          WHEN platforms.name IN ('X360', 'XONE') THEN 'Microsoft'
          ELSE NULL
      END AS major_platform

    FROM games
        INNER JOIN companies ON games.publisher_id = companies.company_id
        INNER JOIN platforms ON games.platform_id = platforms.platform_id
    WHERE year >= '2012'
    ORDER BY games.name
)

SELECT
    game_name AS name
FROM major_platform_game
GROUP BY game_name
HAVING COUNT(DISTINCT major_platform) >= 2
ORDER BY name



-----------------------------------------------------------------
-----------------------------------------------------------------



# 윈도우 함수 활용

WITH major_platform_game AS (
    SELECT DISTINCT
        games.name AS game_name,
        CASE 
            WHEN platforms.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'SONY'
            WHEN platforms.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
            WHEN platforms.name IN ('X360', 'XONE') THEN 'Microsoft'
            ELSE 'Other'
        END AS major_platform
    FROM games
    INNER JOIN companies ON games.publisher_id = companies.company_id
    INNER JOIN platforms ON games.platform_id = platforms.platform_id
    WHERE year >= '2012'
)

, deduplicated AS (
    SELECT DISTINCT
        game_name,
        major_platform
    FROM major_platform_game
    WHERE major_platform != 'Other'
)

, platform_count AS (
    SELECT
        game_name AS name,
        ROW_NUMBER() OVER (PARTITION BY game_name ORDER BY game_name) AS row_num
    FROM deduplicated
)

SELECT DISTINCT name
FROM platform_count
WHERE row_num != 1;

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

16. 스테디셀러 작가 찾기

https://solvesql.com/problems/find-steadyseller-writers/

WITH Fiction_author AS (
    SELECT DISTINCT
      author,
      year
    FROM books
    WHERE genre = "Fiction"
    ORDER BY author
)

, Sequence_num AS (
    SELECT
      author,
      year,
      year - ROW_NUMBER() OVER(PARTITION BY author ORDER BY year) AS group_split
    FROM Fiction_author
)

SELECT
  author,
  MAX(year) AS year,
  COUNT(*) AS depth
FROM Sequence_num
GROUP BY author, group_split
HAVING COUNT(*) >= 5

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

"대여/반납 건수" 의 정확한 의미 및 문제의 상세 조건이 불분명 합니다.

15. 폐쇄할 따릉이 정류소 찾기 2

https://solvesql.com/problems/find-unnecessary-station-2/

WITH usage_counts AS (
    SELECT 
        station_id,
        LEFT(usage_at, 7) AS month,
        COUNT(*) AS usage_count
    FROM (
        SELECT 
            rent_station_id AS station_id,
            rent_at AS usage_at
        FROM rental_history
        WHERE rent_at IS NOT NULL

        UNION ALL

        SELECT 
            return_station_id AS station_id,
            return_at AS usage_at
        FROM rental_history
        WHERE return_at IS NOT NULL
    ) sub
    GROUP BY station_id, month
)
SELECT 
    s.station_id,
    s.name,
    s.local,
    ROUND((u19.usage_count * 100.0) / u18.usage_count, 2) AS usage_pct
FROM station s
    JOIN usage_counts u18 ON s.station_id = u18.station_id AND u18.month = '2018-10'
    JOIN usage_counts u19 ON s.station_id = u19.station_id AND u19.month = '2019-10'
WHERE u18.usage_count > 0 
  AND u19.usage_count > 0
  AND (u19.usage_count * 100.0) / u18.usage_count <= 50
ORDER BY station_id ASC;

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

14. 전력 소비량 이동 평균 구하기

https://solvesql.com/problems/moving-average-of-power-consumption/

SELECT 
    DATE_ADD(measured_at, INTERVAL 10 MINUTE) AS end_at,
    ROUND(AVG(zone_quads) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) AS zone_quads,
    ROUND(AVG(zone_boussafou) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) AS zone_boussafou,
    ROUND(AVG(zone_smir) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) AS zone_smir
FROM power_consumptions
WHERE measured_at BETWEEN '2017-01-01' AND '2017-01-31 23:50:00'
ORDER BY measured_at ASC

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

13. 게임 개발사의 주력 플랫폼 찾기

https://solvesql.com/problems/main-platform-of-game-developers/

WITH main_platform AS (
  SELECT
    games.developer_id,
    games.platform_id,
    SUM(games.sales_na + games.sales_eu + games.sales_jp + games.sales_other) AS sales,
    
    DENSE_RANK() OVER (PARTITION BY games.developer_id ORDER BY SUM(games.sales_na + games.sales_eu + games.sales_jp + games.sales_other) DESC
    ) AS denserank
  FROM games
  GROUP BY games.developer_id, games.platform_id
)

SELECT
  companies.name AS developer,
  platforms.name AS platform,
  sales 
FROM main_platform

    INNER JOIN companies ON main_platform.developer_id = companies.company_id
    INNER JOIN platforms ON main_platform.platform_id = platforms.platform_id
WHERE main_platform.denserank = 1
ORDER BY companies.name, platforms.name;

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

12. 3년간 들어온 소장품 집계하기

https://solvesql.com/problems/summary-of-artworks-in-3-years/

WITH 3years_data AS (
    SELECT
        LEFT(acquisition_date,4) AS acquisition_date,
        classification
    FROM artworks
)
SELECT
    classification,
    -- COUNT(CASE WHEN acquisition_date = '2014' THEN 1 ELSE NULL END) AS '2014',
    -- COUNT(CASE WHEN acquisition_date = '2015' THEN 1 ELSE NULL END) AS '2015',
    -- COUNT(CASE WHEN acquisition_date = '2016' THEN 1 ELSE NULL END) AS '2016'
    SUM(CASE WHEN acquisition_date = '2014' THEN 1 ELSE 0 END) AS '2014',
    SUM(CASE WHEN acquisition_date = '2015' THEN 1 ELSE 0 END) AS '2015',
    SUM(CASE WHEN acquisition_date = '2016' THEN 1 ELSE 0 END) AS '2016'

FROM 3years_data
GROUP BY classification
ORDER BY classification ASC

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

11. 서울숲 요일별 대기오염도 계산하기

https://solvesql.com/problems/weekday-stats-airpollution/

-- FROM절 서브쿼리 삽입, weekday_number_flag 제거 위함
SELECT
  weekday,
  no2,
  o3,
  co,
  so2,
  pm10,
  pm2_5

FROM(
      SELECT
          CASE 
              WHEN DAYOFWEEK(measured_at) = 1 THEN '일요일'
              WHEN DAYOFWEEK(measured_at) = 2 THEN '월요일'
              WHEN DAYOFWEEK(measured_at) = 3 THEN '화요일'
              WHEN DAYOFWEEK(measured_at) = 4 THEN '수요일'
              WHEN DAYOFWEEK(measured_at) = 5 THEN '목요일'
              WHEN DAYOFWEEK(measured_at) = 6 THEN '금요일'
              WHEN DAYOFWEEK(measured_at) = 7 THEN '토요일'
          END AS weekday,
          ROUND(AVG(no2), 4) AS no2,
          ROUND(AVG(o3), 4) AS o3,
          ROUND(AVG(co), 4) AS co,
          ROUND(AVG(so2), 4) AS so2,
          ROUND(AVG(pm10), 4) AS pm10,
          ROUND(AVG(pm2_5), 4) AS pm2_5,
          CASE 
              WHEN DAYOFWEEK(measured_at) = 1 THEN 7 -- 일요일 → 7
              ELSE DAYOFWEEK(measured_at) - 1       -- 월요일~토요일 → 1~6
          END AS weekday_number_flag
          
      FROM measurements
      GROUP BY weekday, weekday_number_flag
      ORDER BY weekday_number_flag
      ) AS sub

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

10. 최대값을 가진 행 찾기

https://solvesql.com/problems/max-row/

SELECT id
FROM points
WHERE x = (SELECT MAX(x) FROM points)
OR y = (SELECT MAX(y) FROM points)
ORDER BY id

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

다음엔 coalesce을 활용해보면 좋을 것 같다.

9. 게임 평점 예측하기 1

https://solvesql.com/problems/predict-game-scores-1/

SELECT
    COUNT(critic_score),
    COUNT(critic_count),
    COUNT(user_score),
    COUNT(user_count)
FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id

2. 경우의 수 확인

WITH fillna_critic_info AS (
SELECT
games.genre_id AS genre_id,
ROUND(AVG(critic_score),3) AS AVG_critic_score,
CEIL(AVG(critic_count)) AS AVG_critic_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id
GROUP BY games.genre_id
),


fillna_user_info AS (
SELECT
games.genre_id AS genre_id,
ROUND(AVG(user_score),3) AS AVG_user_score,
CEIL(AVG(user_count)) AS AVG_user_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id
GROUP BY games.genre_id
),


fillna_all_info AS (
SELECT
games.genre_id AS genre_id,
ROUND(AVG(critic_score),3) AS AVG_critic_score,
CEIL(AVG(critic_count)) AS AVG_critic_count,
ROUND(AVG(user_score),3) AS AVG_user_score,
CEIL(AVG(user_count)) AS AVG_user_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id
GROUP BY games.genre_id
),​

all_null AS(

-- 1. ALL IS NULL
SELECT
games.genre_id,
game_id,
games.name


FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id


WHERE critic_score IS NULL
AND critic_count IS NULL
AND user_score IS NULL
AND user_count IS NULL
AND year >= 2015
),


missing_user_info AS (

-- 2.critic_score, count IS NOT.NULL
SELECT
games.genre_id,
game_id,
games.name,
critic_score,
critic_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id

WHERE critic_score IS NOT NULL
AND critic_count IS NOT NULL
AND user_score IS NULL
AND user_count IS NULL
AND year >= 2015
),


missing_critic_info AS(

-- 3.user_score, count IS NOT.NULL
SELECT
games.genre_id,
game_id,
games.name,
user_score,
user_count
FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id

WHERE critic_score IS NULL
AND critic_count IS NULL
AND user_score IS NOT NULL
AND user_count IS NOT NULL
AND year >= 2015
)

-- 유저 정보 필링
SELECT
missing_user_info.game_id,
name,
critic_score,
critic_count,
AVG_user_score AS user_score,
AVG_user_count AS user_count

FROM missing_user_info
INNER JOIN fillna_user_info ON missing_user_info.genre_id = fillna_user_info.genre_id

UNION ALL

-- 평론가 정보 필링

SELECT
missing_critic_info.game_id,
name,
AVG_critic_score AS critic_score,
AVG_critic_count AS critic_count,
user_score,
user_count

FROM missing_critic_info
INNER JOIN fillna_critic_info ON missing_critic_info.genre_id = fillna_critic_info.genre_id

UNION ALL

-- 유저 & 평론가 정보 필링

SELECT
all_null.game_id,
name,
AVG_critic_score AS critic_score,
AVG_critic_count AS critic_count,
AVG_user_score AS user_score,
AVG_user_count AS user_count

FROM all_null
INNER JOIN fillna_all_info ON all_null.genre_id = fillna_all_info.genre_id

#. 전체쿼리

2015년 이후에 발매한 게임 중 누락된 평점 정보가 있는 게임에 대하여
같은 장르를 가진 전체 게임의 평균 평점과 평균 평론가/사용자 수를 사용해 누락된 정보를 채우는 쿼리 작성


-- 데이터 확인

-- SELECT 
--   COUNT(critic_score),
--   COUNT(critic_count),
--   COUNT(user_score),
--   COUNT(user_count)
-- FROM games
-- INNER JOIN genres ON games.genre_id = genres.genre_id




-- 누락 정보 조인을 위한 테이블

WITH fillna_critic_info AS (
  SELECT
      games.genre_id AS genre_id,
      ROUND(AVG(critic_score),3) AS AVG_critic_score,      
      CEIL(AVG(critic_count)) AS AVG_critic_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id
  GROUP BY games.genre_id
), 


fillna_user_info AS (
  SELECT
      games.genre_id AS genre_id,
      ROUND(AVG(user_score),3) AS AVG_user_score,      
      CEIL(AVG(user_count)) AS AVG_user_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id
  GROUP BY games.genre_id
), 


fillna_all_info AS (
  SELECT
      games.genre_id AS genre_id,
      ROUND(AVG(critic_score),3) AS AVG_critic_score,      
      CEIL(AVG(critic_count)) AS AVG_critic_count,
      ROUND(AVG(user_score),3) AS AVG_user_score,      
      CEIL(AVG(user_count)) AS AVG_user_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id
  GROUP BY games.genre_id
),   



-- 3개 테이블로 분할

all_null AS(

-- 1. ALL IS NULL
SELECT
  games.genre_id,
  game_id,
  games.name


FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id


WHERE critic_score IS NULL
AND critic_count IS NULL
AND user_score IS NULL
AND user_count IS NULL
AND year >= 2015
),


missing_user_info AS (

-- 2.critic_score, count IS NOT.NULL
  SELECT
    games.genre_id,
    game_id,
    games.name,
    critic_score,
    critic_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id

  WHERE critic_score IS NOT NULL
  AND critic_count IS NOT NULL
  AND user_score IS NULL
  AND user_count IS NULL
  AND year >= 2015
),


missing_critic_info AS(

-- 3.user_score, count IS NOT.NULL 
  SELECT
    games.genre_id,
    game_id,
    games.name,
    user_score,
    user_count
  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id

  WHERE critic_score IS NULL
  AND critic_count IS NULL
  AND user_score IS NOT NULL
  AND user_count IS NOT NULL
  AND year >= 2015
)



-- 누락정보 INNER 조인 + UNION ALL

-- 유저 정보 필링
SELECT
  missing_user_info.game_id,
  name,
  critic_score,
  critic_count,
  AVG_user_score AS user_score,
  AVG_user_count AS user_count

FROM missing_user_info
INNER JOIN fillna_user_info ON missing_user_info.genre_id = fillna_user_info.genre_id

UNION ALL

-- 평론가 정보 필링

SELECT
  missing_critic_info.game_id,
  name,
  AVG_critic_score AS critic_score,
  AVG_critic_count AS critic_count,
  user_score,
  user_count

FROM missing_critic_info
INNER JOIN fillna_critic_info ON missing_critic_info.genre_id = fillna_critic_info.genre_id

UNION ALL

-- 유저 & 평론가 정보 필링

SELECT
  all_null.game_id,
  name,
  AVG_critic_score AS critic_score,
  AVG_critic_count AS critic_count,
  AVG_user_score AS user_score,
  AVG_user_count AS user_count

FROM all_null
INNER JOIN fillna_all_info ON all_null.genre_id = fillna_all_info.genre_id

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

8. 온라인 쇼핑몰의 월 별 매출액 집계

https://solvesql.com/problems/shoppingmall-monthly-summary/

SELECT 
    LEFT(order_date, 7) AS order_month,
    SUM(CASE WHEN orders.order_id NOT REGEXP 'C' THEN price*quantity ELSE 0 END) AS ordered_amount,
    SUM(CASE WHEN orders.order_id REGEXP 'C' THEN price*quantity ELSE 0 END) AS canceled_amount,

    # total_amount
    SUM(CASE WHEN orders.order_id NOT REGEXP 'C' THEN price*quantity ELSE 0 END) + 
    SUM(CASE WHEN orders.order_id REGEXP 'C' THEN price*quantity ELSE 0 END) AS total_amount

FROM orders
LEFT JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY LEFT(order_date, 7)
ORDER BY LEFT(order_date, 7) ASC