주댕이의 공부 기록

# 링크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) ..

# strftimeSQL에서 날짜와 시간을 지정된 형식의 문자열로 변환하는 함수특히 SQLite에서 날짜와 시간 데이터를 처리할 때 유용하다.SQLite는 strftime에서 입력 값이 ISO 8601 형식(예: YYYY-MM-DD HH:MM:SS)일 때 가장 정확하게 동작한다.포맷 코드는 조합 가능하며, 필요한 데이터만 추출할 수 있다. # 기본 구문strftime(format, date/time_expression)format: 출력 문자열의 형식을 지정하는 포맷 문자열date/time_expression: 변환하고자 하는 날짜 또는 시간 표현 # 주요 포맷 코드코드설명예시 출력%Y4자리 연도2024%m2자리 월 (01~12)12%d2자리 일 (01~31)11%H2자리 시 (00~23, 24시간 형식..

# CTE(Common Table Expression)SQL 쿼리 내에서 임시로 사용할 수 있는 이름이 있는 결과 집합을 정의하는 방식일반적으로 긴 쿼리의 가독성을 높이고, 반복적인 서브쿼리를 간소화하며, 복잡한 쿼리를 단계별로 처리할 때 유용하다.CTE는 WITH 절을 사용하여 정의하며, CTE로 정의된 데이터는 해당 쿼리에서만 사용 가능하다. # 구조WITH cte_name AS ( -- CTE를 정의하는 쿼리 SELECT column1, column2, ... FROM table_name WHERE conditions)-- CTE를 활용한 주 쿼리SELECT *FROM cte_nameWHERE additional_conditions;WITH cte_name AS:cte_name..

# 링크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..