데이터 분석

미세먼지 수치의 계절간 차이 ✔️0214 복습문제 조건1. 3~5월 'spring', 6~8월 'summer', 9~11월 'autumn', 나머지를 'winter'라고 이름을 붙이고 각 계절별 미세먼지 농도의 중앙값과 평균을 계산2. 평균값은 반올림 해 소수점 둘째 자리까지 표현 SolutionWITH step1 AS ( SELECT 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' A..

전국 카페 주소 데이터 정제하기 ✔️0213 복습문제 조건 1. 주소 정보에서 (시, 도) 정보와 (시, 군, 구) 정보를 추출하여 각 행정구역 별로 카페의 개수를 집계2. 카페 개수가 많은 행정구역 순으로 출력 Solution-- 최종 : 익숙해지면?!SELECT SUBSTR(address, 1, INSTR(address, ' ')-1) AS sido -- '서울특별시 종로구' -> '서울특별시' , SUBSTR(SUBSTR(address, INSTR(address, ' ')+1), 1, INSTR(SUBSTR(address, INSTR(address, ' ')+1), ' ')-1) AS sigungu -- '종로구 ~' , COUNT(cafe_id) AS cntFROM cafesGROU..

펭귄 날개와 몸무게의 상관 계수 ✔️0213 1118 복습문제 조건1. 펭귄 종에 따라 날개 길이와 몸무게의 피어슨 상관 계수를 구하기2. 상관 계수는 반올림 해 셋째 자리까지 출력 SolutionWITH statistics AS ( SELECT species -- 종별로 상관계수 구하기 , SUM(flipper_length_mm * body_mass_g) AS sum_xy , AVG(flipper_length_mm) AS mean_x , AVG(body_mass_g) AS mean_y , SUM(flipper_length_mm * flipper_length_mm) AS sum_xx , SUM(body_mass_g * body_mass_g) AS s..

멀티 플랫폼 게임 찾기✔️0212 1116 복습문제 조건1. 2012년 이후 출시된 게임들 중 둘 이상의 메이저 플랫폼 계열에 출시된 게임 이름을 출력2. 중복된 게임은 1번만 출력 메이저 플랫폼 계열:Sony: 'PS3', 'PS4', 'PSP', 'PSV'Nintendo: 'Wii', 'WiiU', 'DS', '3DS'Microsoft: 'X360', 'XONE' Solution-- 가장 최근 쿼리WITH major_platforms AS ( -- 관심있는 플랫폼들만 필터링 SELECT * FROM platforms WHERE name IN ('PS3','PS4','PSP','PSV','Wii','WiiU','DS','3DS','X360','XONE')), major_games AS ( --..

스테디셀러 작가 찾기✔️0212 0822 복습 문제 조건1. 5년 이상 연속으로 베스트셀러 작품 목록에 이름을 올린 소설 작가와 연도 정보를 출력2. 이때 서로 다른 작품이더라도 상관없음3. 단, 같은 해에 여러 작품으로 베스트셀러 목록에 들어가더라도 1번으로 카운트 SolutionWITH T1 AS ( SELECT DISTINCT author , year FROM books WHERE genre = 'Fiction' ), T2 AS ( SELECT author, year, LAG(year, 4) OVER (PARTITION BY author ORDER BY year) as lag_4 FROM T1 ) SELECT author, ..

폐쇄할 따릉이 정류소 찾기 2 ✔️0211 1015 복습확실히 2번째 풀어볼 때 훨씬 효율적이고 논리적으로 쿼리를 작성함 -> 아직도 완전 부족함 😵‍💫문제 조건1. 2019년 10월 한 달 동안 정류소에서 발생한 대여/반납 건수가 2018년 10월 같은 정류소에서 발생한 대여/반납 건수의 50% 이하인 정류소를 출력2. 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소는 결과에서 제외 Solution ✅강사님/*1. 정류소별 18년, 19년 반납 건수2. 정류소별 18년, 19년 대여 건수뭐 9월 대여 10월 반납, 10월에 대여 10월에 반납, 10월에 대여 11월에 반납 등 복잡하게 경우를 따질 이유가 없음*/WITH return_stats AS ( SELECT..

전력 소비량 이동 평균 구하기전력 소비량은 순간순간 크게 변화하는 값이기 때문에 트렌드를 보거나 수요 예측을 하기 위해 단순 이동 평균을 주로 사용한다고 함 문제 조건1. 2017년 1월 1일 0시부터 2017년 2월 1일 0시까지 10분 단위로 1시간 범위의 단순 이동 평균을 계산2. 이동 평균값은 소수점 셋째 자리에서 반올림하여 둘째 자리까지 표시3. 쿼리 결과에 아래 컬럼이 포함 end_at: 이동 평균 범위의 끝 시각 zone_quads : Quads 지역 전력 소비량의 1시간 단순 이동 평균 zone_smir : Smir 지역 전력 소비량의 1시간 단순 이동 평균 zone_boussafou : Boussafou 지역 전력 소비량의 1시간 단순 이동 평균 Solution..

게임 개발사의 주력 플랫폼 찾기 문제 조건1. 주력 플랫폼은 개발사마다 판매량이 가장 많은 플랫폼으로 정의2. 각 게임 개발사의 주력 플랫폼과 해당 플랫폼의 판매량 합계를 집계3. 판매량 합계가 동일한 플랫폼이 여러 개라면 모두 출력 Solution ✅MySQL-- 1014 버전WITH step1 AS ( SELECT developer_id , platform_id , SUM(sales_na + sales_eu + sales_jp + sales_other) AS sales_total FROM games WHERE developer_id IS NOT NULL GROUP BY developer_id, platform_id), step3 AS ( SELECT developer_i..

3년간 들어온 소장품 집계하기 문제 조건1. 각 분류에 대해 2014년부터 2016년까지 추가된 소장품 수 집계2. 분류 컬럼 기준 오름차순 정렬3. 집계하는 3년간 추가된 특정 분류의 소장품이 없더라도 해당 분류와 집계 내역을 결과 테이블에서 누락시키지 말 것 ⭐ Solution ✅SQLiteWITH T AS ( SELECT classification, substr(acquisition_date, 1, 4) as acquisition_year FROM artworks -- WHERE acquisition_date BETWEEN '2014-01-01' AND '2016-12-31' ) SELECT classification, sum(case when acquisiti..

서울숲 요일별 대기오염도 계산하기 문제 조건1. 요일별 대기 오염도 평균을 계산2. 대기 오염도 값은 소수점 다섯째 자리에서 반올림하여 넷째 자리까지 표현3. 월요일부터 일요일 순으로 출력 Solution ✅SQLite-- (1) %w 포맷WITH avg_pollution AS ( SELECT strftime('%w', measured_at) 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(p..