Lv4. 온라인 쇼핑몰의 Stickinesshttps://solvesql.com/problems/stickiness-of-shoppingmall/SELECT d.order_date AS dt , COUNT(DISTINCT d.customer_id) AS dau , COUNT(DISTINCT w.customer_id) AS wau , ROUND(COUNT(DISTINCT d.customer_id) / COUNT(DISTINCT w.customer_id),2) AS stickinessFROM records AS d LEFT JOIN records AS w ON w.order_date BETWEEN DATE_ADD(d.order_date, INTERVAL -6 DAY) AND d.orde..

Lv4. 가구 판매의 비중이 높았던 날 찾기https://solvesql.com/problems/day-of-furniture/SELECT order_date , COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS 'furniture' , ROUND(COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) / COUNT(DISTINCT order_id) *100,2) AS 'furniture_pct'FROM recordsGROUP BY order_dateHAVING COUNT(DISTINCT order_id) >= 10 AND furniture_pct >= 40OR..

Lv4. 레스토랑 요일 별 구매금액 Top 3 영수증https://solvesql.com/problems/top-3-bill/WITH T1 AS ( SELECT * , RANK() OVER(PARTITION BY day ORDER BY total_bill DESC) AS RNK FROM tips)SELECT day, time, sex, total_billFROM T1WHERE RNK ❗WHERE 절에 3 이하 조건 달아서 3위까지의 데이터 추출+) WITH 절❗WINDOW 함수 RANK() OVER(PARTITION BY ORDER BY) 사용해서 순위 매기기❗중복을 허용이라 RANK() 사용 (중복 비 허용 시 DENSE_RANK() 사용)

Lv4. 입문반 페이지를 본 세션 찾기https://solvesql.com/problems/session-pv/WITH T1 AS ( SELECT user_pseudo_id , ga_session_id , event_name , page_title FROM ga WHERE page_title = '백문이불여일타 SQL 캠프 입문반' AND event_name = 'page_view')SELECT COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) AS total , COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) - COUNT(DISTINCT T1.user_pseudo_id, T1.ga_se..

Lv3. RFM 분석 3단계. 떠나간 VIPhttps://solvesql.com/problems/rfm-3-left-vip/WITH T1 AS ( SELECT * , CASE WHEN last_order_date >= '2020-12-01' THEN 'recent' ELSE 'past' END AS recency , CASE WHEN cnt_orders >= 3 THEN 'high' ELSE 'low' END AS frequency , CASE WHEN sum_sales >= 500 THEN 'high' ELSE 'low' END AS monetary FROM customer_stats), T2 AS ( SELECT recency, frequency, monetary , CO..

Lv3. 지역별 주문의 특징https://solvesql.com/problems/characteristics-of-orders/SELECT region AS Region , COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS 'Furniture' , COUNT(DISTINCT CASE WHEN category = 'Office Supplies' THEN order_id END) AS 'Office Supplies' , COUNT(DISTINCT CASE WHEN category = 'Technology' THEN order_id END) AS 'Technology'FROM recordsGROUP BY regionORDER BY..

Lv3. 할부는 몇 개월로 해드릴까요https://solvesql.com/problems/installment-month/SELECT payment_installments , COUNT(DISTINCT order_id) AS order_count , MIN(payment_value) AS min_value , MAX(payment_value) AS max_value , AVG(payment_value) AS avg_valueFROM olist_order_payments_datasetWHERE payment_type = 'credit_card'GROUP BY payment_installments❗COUNT(), MIN(), MAX(), AVG() 집계함수 사용해서 집계❗WHERE 절에 조건 충족시키..

Lv3. 복수 국적 메달 수상한 선수 찾기https://solvesql.com/problems/multiple-medalist/SELECT A.NAME -- A.ID,COUNT(DISTINCT R.team_id)FROM athletes as A INNER JOIN records AS R ON A.ID = R.athlete_id INNER JOIN games AS G ON R.game_id = G.id INNER JOIN teams AS T ON R.team_id = T.idWHERE G.year >= 2000 AND R.medal IS NOT NULLGROUP BY A.IDHAVING COUNT(DISTINCT R.team_id) > 1ORDER BY A.NAME ASC❗INNER JOIN으로 ..