구름 아카이브

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

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

7. 기증품 비율 계산하기

https://solvesql.com/problems/ratio-of-gifts/

WITH gift as (
  SELECT
    count(*) as gift_count
  FROM artworks
  WHERE LOWER(credit) REGEXP 'gift'
)

SELECT ROUND((SELECT gift_count FROM gift)/count(*)*100, 3) AS ratio
FROM artworks

*** 대소문자 구분 필수

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

6. 게임을 10개 이상 발매한 퍼블리셔 찾기

https://solvesql.com/problems/publisher-with-many-games/

WITH publisher AS (
  SELECT
    publisher_id,
    COUNT(*) AS game_cnt
  FROM games
  GROUP BY publisher_id
  HAVING COUNT(*) >= 10
)

SELECT name
FROM companies
WHERE company_id IN (SELECT publisher_id FROM publisher)

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

5. 언더스코어(_)가 포함되지 않은 데이터 찾기

https://solvesql.com/problems/data-without-underscore/

SELECT 
  DISTINCT page_location
FROM ga
WHERE page_location NOT REGEXP '_'
-- GROUP BY page_location
ORDER BY page_location ASC;

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

4. 지자체별 따릉이 정류소 개수 세기

https://solvesql.com/problems/count-stations/

SELECT 
  local,
  COUNT(station_id) AS num_stations
FROM station
GROUP BY local
ORDER BY COUNT(station_id) ASC;

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

3. 제목이 모음으로 끝나지 않는 영화

https://solvesql.com/problems/film-ending-with-consonant/

SELECT 
  title
FROM film
WHERE rating NOT REGEXP 'P|G'
AND title NOT REGEXP '[AEIOUaeiou]$'