주댕이의 공부 기록

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com14일차 - 폐쇄할 따릉이 정류소 찾기 2:https://solvesql.com/problems/find-unnecessary-station-2/ https://solvesql.com/problems/find-unnecessary-station-2/ solvesql.com # 풀이 WITH usage_counts AS ( SELECT station_id, strftime('%Y-%m', usage_at) AS month, ..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com14일차 - 전력 소비량 이동 평균 구하기: https://solvesql.com/problems/moving-average-of-power-consumption/ https://solvesql.com/problems/moving-average-of-power-consumption/ solvesql.com # 풀이SELECT DATETIME(measured_at, '+10 minutes') AS end_at, ROUND(AVG(zone..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com13일차 - 게임 개발사의 주력 플랫폼 찾기: https://solvesql.com/problems/main-platform-of-game-developers/ https://solvesql.com/problems/main-platform-of-game-developers/ solvesql.com # 풀이WITH PlatformSales AS ( SELECT c.name AS developer, p.name AS platfo..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com12일차 - 3년간 들어온 소장품 집계하기: https://solvesql.com/problems/summary-of-artworks-in-3-years/ https://solvesql.com/problems/summary-of-artworks-in-3-years/ solvesql.com # 풀이SELECT classification, COUNT(*) FILTER (WHERE strftime('%Y', acquisition_date) ..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com11일차 - 서울숲 요일별 대기오염도 계산하기: https://solvesql.com/problems/weekday-stats-airpollution/ https://solvesql.com/problems/weekday-stats-airpollution/ solvesql.com # 풀이SELECT CASE strftime('%w', measured_at) WHEN '1' THEN '월요일' WHEN '2' THEN '화요일'..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com10일차 - 최대값을 가진 행 찾기: https://solvesql.com/problems/max-row/ https://solvesql.com/problems/max-row/ solvesql.com # 풀이SELECT idFROM pointsWHERE x = (SELECT MAX(x) FROM points) OR y = (SELECT MAX(y) FROM points)ORDER BY id ASCSELECT id: id 컬럼을 선택한다.F..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com9일차 - 게임 평점 예측하기 1: https://solvesql.com/problems/predict-game-scores-1/ https://solvesql.com/problems/predict-game-scores-1/ solvesql.com # 풀이WITH GenreAverages AS ( SELECT genre_id, ROUND(AVG(critic_score),3) AS avg_critic_score, CAST(..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com8일차 - 온라인 쇼핑몰의 월 별 매출액 집계: https://solvesql.com/problems/shoppingmall-monthly-summary/ https://solvesql.com/problems/shoppingmall-monthly-summary/ solvesql.com # 풀이SELECT strftime('%Y-%m', o.order_date) AS order_month, SUM(CASE WHEN o.order_id N..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com7일차 - 기증품 비율 계산하기: https://solvesql.com/problems/ratio-of-gifts/ https://solvesql.com/problems/ratio-of-gifts/ solvesql.com # 풀이SELECT ROUND( CAST( SUM( CASE WHEN credit LIKE '%gift%' THEN 1 ELSE 0 END ) AS FLOAT ) / COUNT(..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com6일차 - 게임을 10개 이상 발매한 퍼블리셔 찾기: https://solvesql.com/problems/publisher-with-many-games/  https://solvesql.com/problems/publisher-with-many-games/ solvesql.com # 풀이SELECT c.nameFROM companies AS cJOIN games AS g  ON c.company_id = g.publisher_idGROU..