데이터 분석

세 명이 서로 친구인 관계 찾기✔️0215 복습 소셜 네트워크 분석에서는 세 명의 사용자가 서로 친구 관계인 경우를 중요하게 생각한다.  일반적인 사용자는 세 명의 사용자가 서로 친구인 경우가 다수 있지만,  스팸 사용자 또는 친구 관계를 무작위로 맺는 사용자의 경우 전체 친구 수에 비해 세 명의 사용자가 친구인 경우가 많지 않아 이상 사용자 탐지에 유용하게 쓸 수 있기 때문이다. 문제 조건1. ID가 3820인 사용자를 포함해 세 명의 사용자가 친구 관계인 경우를 출력2. 중복된 세 친구 관계를 제외하기 위해 user_a_id 를 만족하도록 출력  SolutionWITH T1 AS ( SELECT e.user_a_id, e.user_b_id, e2.user_c_id FR..

유량(Flow)과 저량(Stock)✔️0215 복습 ‘연도별로 새롭게 소장하게 된 작품의 수’와 같이 일정 기간 동안 측정되는 지표를 ‘유량(Flow) 지표’라고 하고, ‘누적 소장 작품 수’와 같이 특정 시점에 측정되는 지표를 ‘저량(Stock) 지표’라고 한다. 문제 조건1. 연도별로 새롭게 소장하게 된 작품의 수와, 연도별 누적 소장 작품 수를 계산2. 저량 지표에 변화가 없는 연도는 출력되지 않아야 하고, 소장 년도 정보가 없는 작품은 집계에서 제외3. 소장 연도 컬럼 기준 오름차순 정렬 SolutionWITH T1 AS ( SELECT strftime('%Y', acquisition_date) as year, COUNT(*) AS cnt FROM artworks WHERE a..

친구 수 집계하기 ✔️0215 복습문제 조건1. 데이터베이스에 포함된 모든 사용자에 대해 각 사용자의 친구 수를 집계2. 친구 수가 많은 사용자부터 출력하며, 만약 친구 수가 같은 사용자가 여럿이라면 그 사이에서는 사용자 ID가 작은 사용자가 먼저 출력 SolutionWITH friends_count AS ( SELECT user_id, COUNT(CASE WHEN user_id = e.user_a_id THEN 1 END) + COUNT(CASE WHEN user_id = e.user_b_id THEN 1 END) AS num_friends FROM users u LEFT JOIN edges e ON u.user_id = e.user_a_id OR u.user_i..

세션 유지 시간을 10분으로 재정의하기  ✔️0214 복습문제 조건1. 세션을 종료하는 기존의 기준(30분 이상)을 사용자가 10분 이상 행동하지 않을 때로 수정2. 사용자 'a8Xu9GO6TB’의 세션을 재정의해 세션 ID를 계산하는 쿼리를 작성 3. 재정의한 세션 ID는 1부터 시작해 세션 시작 시간이 빠른 순서대로 1씩 증가하는 자연수일 것4. 이벤트 발생 시각이 빠른 순서대로 정렬  SolutionWITH step1 AS ( SELECT user_pseudo_id , event_timestamp_kst , LAG(event_timestamp_kst, 1) OVER (ORDER BY event_timestamp_kst) AS last_event -- 이후에 전체 유저에 확장시..

미세먼지 수치의 계절간 차이 ✔️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..