데이터 분석

문제 상황 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 ..

롤링 리텐션 다음과 같은 상황을 가정해 보자. "A 유저는 1월 1일에 y 서비스에 처음으로 접속했다. 첫 방문 (Day 0) 이후 바로 다음날(Day 1)에 접속하고, 2일 동안 (Day 2, 3) 접속을 하지 않다가 첫 방문 이후 4일째가 되던 날 (Day 4)에 다시 서비스를 이용하였다." 이런 경우 일 단위 클래식 리텐션 계산은 A 유저를 Day 2, 3 리텐션 계산에서 제외하게 된다. 사실 이 경우에는 A 유저를 Day 2, 3 시점에 y 서비스를 계속 사용하고 있다고 보는 의견도 함부로 무시할 수 없을 것이다. 이 점이 바로 클래식 리텐션 사용 시 놓칠 수 있는 부분이다.  이러한 한계를 보완하기 위해 롤링 리텐션 계산 방법이 있다. 이것은 '사용자가 이탈하지 않고 남아있는가?'에 초점을 맞춘..

유입 채널별 실전반 전환율 바로 전 게시글에서 다루었던 문제와 동일한 분석이며, 단지 유입 채널별로 값을 집계하는 부분에서 차이가 있다. 유입 채널은 유저가 특정 서비스에 방문할 수 있게 되는 경로로써 페이스북, 유튜브, 블로그 등 다양하게 존재한다. 어떤 채널로 들어오는 유저들의 전환율이 좋은지 등을 분석하고 채널의 특성과 운영 리소스를 고려하여 효율적인 전략을 세울 수 있다. 실전반 페이지를 본 이후에 스크롤을 내려본 세션은 얼마나 되는지, 또 스크롤을 내려 실전반 신청하기 버튼까지 클릭한 세션은 얼마나 되는지를 유입 채널별로 나누어 조회하는 쿼리를 작성해 보자. 문제 조건 1. 실전반 페이지를 본 세션은 아래 조건으로 찾는다page_title = “백문이불여일타 SQL 캠프 실전반”event_nam..

SQL 데이터 분석 캠프 실전반 전환율 실전반 페이지를 조회하는 것 부터 실전반 신청하기 버튼 클릭까지 이어지는 퍼널은 다음과 같이 구성한다. [실전반 페이지뷰 -> 스크롤 -> 실전반 신청하기 버튼 클릭] 실전반 페이지를 본 이후에 스크롤을 내려본 세션은 얼마나 되는지, 또 스크롤을 내려 실전반 신청하기 버튼까지 클릭한 세션은 얼마나 되는지를 조회하는 쿼리를 작성할 것. 문제 조건1. 실전반 페이지를 본 세션은 아래 조건으로 찾는다page_title = “백문이불여일타 SQL 캠프 실전반”event_name = “page_view”2. 실전반 페이지 내에서 스크롤을 한 세션은 아래 조건으로 찾는다page_title = “백문이불여일타 SQL 캠프 실전반”event_name = “scroll”3. 실전반..

페이지에서 스크롤을 내렸을까? 전체 세션을 입문반 페이지를 본 세션과 안 본 세션으로 나누고, 입문반 페이지를 본 세션에 대해서 스크롤을 한 세션과 하지 않은 세션으로 세분화 해 집계하려 한다. 참고로 개별 세션의 개수를 세어주려면 user_pseudo_id 와 ga_session_id 컬럼을 모두 고려할 것. 문제 조건1. 입문반 페이지를 본 세션은 아래 조건으로 찾는다page_title = “백문이불여일타 SQL 캠프 입문반”event_name = “page_view”2. 입문반 페이지 내에서 스크롤을 한 세션은 아래 조건으로 찾는다page_title = “백문이불여일타 SQL 캠프 입문반”event_name = “scroll”3. 동일 세션 내에서 이벤트들 간의 선후 관계를 고려할 것 Solutio..

입문반 페이지를 본 세션 찾기 개별 세션의 개수를 세어주려면 user_pseudo_id 와 ga_session_id 컬럼을 모두 고려해야 한다. 문제 조건1. 입문반 페이지를 본 세션은 아래 조건으로 찾는다page_title = “백문이불여일타 SQL 캠프 입문반”event_name = “page_view”2. 쿼리 결과는 '전체 세션 수', '입문반 페이지를 안 본 세션 수', '본 세션 수' 3개 컬럼을 포함한다 SolutionSELECT (SELECT COUNT(DISTINCT user_pseudo_id, ga_session_id) FROM ga) AS total , (SELECT COUNT(DISTINCT user_pseudo_id, ga_session_id) FROM ga) - C..

레스토랑의 요일별 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 ..

클래식 리텐션 Retention은 유저들이 어떤 서비스를 1회성이 아닌 지속적으로 사용하는지, 핵심 가치를 꾸준히 경험하는지를 측정한다. 사용자 유지와 관련한 지표 계산 방법 중에 클래식 리텐션, N-Day Retention이라고도 불리는 측정 방법이 있다. 다만 클래식 리텐션은 특정일에 접속했는지 여부는 고려하지만 그때까지 꾸준히 반복적으로 들어왔는지 여부는 고려하지 못한다는 점을 유념하자. 지속적 사용을 어떻게 정의할까?이에 대한 대답은 정답이 없다. 서비스의 특성, 서비스가 유저들로 하여금 기대하는 행동에 따라 이에 대한 정의는 '방문', '주문', '결제' 등 다양한 행위가 가능하다.  예를 들어 내가 새해를 맞이해서 사용하고 있는 스픽이라는 영어 말하기 앱이 있다. 이 서비스의 핵심 가치는 내가..

배송 예정일 예측 성공과 실패✔️0217 복습 Brazilian E-Commerce Public Dataset by Olist 데이터셋을 활용!: 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터 문제 조건1. 2017년 1월 한 달 동안 발생한 주문의 배송 예측2. 고객의 구매 일자별로 배송 예정 시각 안에 도착한 주문과, 그렇지 않은 주문을 각각 집계3. 배송 완료 또는 배송 예정 시각 데이터가 없는 경우 제외4. 주문 날짜를 기준으로 오름차순 정렬 Solution-- (1)SELECT DATE(order_purchase_timestamp) AS purchase_date , IFNULL(SUM(CASE WHEN order_delivered_customer_date order_e..