데이터 분석

문제 상황 특정 기간 후 이탈률이 높아지는 지점을 식별하여 집중적인 고객 관리 전략을 수립하기 위한 비즈니스 시나리오를 가정하고, 유저들의 첫번째 주문일을 기준으로 코호트를 나눈 후 유저의 첫 번째 주문 이후의 주문 리텐션을 계산해 보았다. 단, 한 유저가 같은 날 여러 건의 주문을 했을 경우 하루에 한 건으로 집계한다. SolutionWITH step1 AS ( SELECT r.* , c.first_order_date , DATEDIFF(r.order_date, c.first_order_date) AS day_diff FROM records r JOIN customer_stats c ON r.customer_id = c.customer_id)SELECT first_orde..

문제 상황 고객별 인기 상품 정보를 바탕으로 교차 판매 및 상향 판매 전략을 수립하기 위한 비즈니스 시나리오를 가정하고, 1분기 동안의 고객별 인기 상품 Top3을 조회해 보았다. 단, 주문 수량이 10/9/9/8였다면 상위 주문 수량 3개는 10/9/8 이므로 총 4개의 상품명을 출력한다. SolutionWITH step1 AS ( SELECT customer_id , product_name , SUM(quantity) AS total_quantity FROM records WHERE order_date BETWEEN '2020-01-01' AND '2020-03-31' GROUP BY customer_id, product_name), step2 AS ( SELECT * ..

문제 상황 solvesql의 플레이그라운드 서비스는 사용자가 원하는 데이터베이스를 선택하여 자유롭게 분석해 볼 수 있는 환경을 제공한다. 이에 나는 매출 기여도가 높은 카테고리를 식별하여 이에 대한 재고 관리를 더욱 철저히 하고자 하는 비즈니스 시나리오를 가정하고, 이를 위해 카테고리별 매출액, 총매출액, 차지하는 비율을 집계해 보았다.  SolutionSELECT DISTINCT category , SUM(sales) OVER (PARTITION BY category) AS sales_category , SUM(sales) OVER () AS sales_total , ROUND(SUM(sales) OVER (PARTITION BY category) / SUM(sales) OVER ..

레스토랑의 요일별 VIP 문제 조건1. 요일별로 가장 높은 금액의 결제 내역을 출력2. 쿼리 결과는 테이블에 있는 모든 컬럼을 포함 Solution ✔️ 쿼리 1 : 다중 컬럼 서브쿼리를 이용하여 해결SELECT * FROM tipsWHERE (day, total_bill) IN ( SELECT day , MAX(total_bill) FROM tips GROUP BY day) ✅ 서브쿼리로 요일별 가장 높은 금액을 계산한다✅ 메인쿼리 WHERE절에 다중 컬럼 조건으로 서브쿼리 결과와 만족하는 행만 반환한다  ✔️ 쿼리 2 : ROW_NUMBER(), 윈도우 함수를 이용하여 해결SELECT total_bill , tip , sex , smoker , day ..

다음날도 서울숲의 미세먼지 농도는 나쁨 😢✔️0217 복습 문제 조건1. 당일의 미세먼지 농도보다 바로 다음날의 미세먼지 농도가 더 안 좋은 날을 출력2. 쿼리 결과는 당일, 다음날, 당일의 미세먼지 농도, 다음날의 미세먼지 농도 컬럼을 포함 Solution ✔️ 쿼리 1 : 셀프조인을 이용하여 해결SELECT m1.measured_at AS today , m2.measured_at AS next_day , m1.pm10 , m2.pm10 AS next_pm10FROM measurements m1 JOIN measurements m2 ON m2.measured_at = DATE_ADD(m1.measured_at, INTERVAL 1 DAY)WHERE m1.pm10 ✅ JOIN ..

세 명이 서로 친구인 관계 찾기✔️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..