문제에 쓰는 공통 샘플 테이블 (SQLite / MySQL 스타일)

-- users: 사용자 기본 정보
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  signup_date DATE,
  user_type VARCHAR(10), -- free / paid / guest
  country VARCHAR(2)
);

INSERT INTO users VALUES
(1,'2025-11-25','free','KR'),
(2,'2025-11-26','paid','KR'),
(3,'2025-11-27','free','US'),
(4,'2025-11-27','guest','KR'),
(5,'2025-11-28','free','KR'),
(6,'2025-12-01','paid','US'),
(7,'2025-12-02','free','KR'),
(8,'2025-12-05','guest','KR'),
(9,'2025-12-07','paid','KR'),
(10,'2025-12-10','free','US');

-- events: 사용자 행동 이벤트 (view, signup, purchase 등)
CREATE TABLE events (
  event_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  event_type VARCHAR(20),
  event_date DATE
);

-- (샘플)
INSERT INTO events (user_id, event_type, event_date) VALUES
(1,'view','2025-12-01'),
(1,'signup','2025-12-02'),
(1,'purchase','2025-12-05'),
(2,'view','2025-12-01'),
(2,'purchase','2025-12-03'),
(3,'view','2025-12-02'),
(3,'signup','2025-12-03'),
(3,'view','2025-12-10'),
(4,'view','2025-12-02'),
(5,'view','2025-12-03'),
(5,'signup','2025-12-04'),
(6,'view','2025-12-05'),
(6,'signup','2025-12-06'),
(6,'purchase','2025-12-10'),
(7,'view','2025-12-02'),
(7,'purchase','2025-12-15'),
(8,'view','2025-12-06'),
(9,'view','2025-12-07'),
(9,'signup','2025-12-08'),
(10,'view','2025-12-10'),
(10,'signup','2025-12-11');

-- experiments: A/B 실험 할당 및 전환 플래그(문제7 전용)
CREATE TABLE experiments (
  user_id INT PRIMARY KEY,
  variant CHAR(1), -- 'A' or 'B'
  assigned_date DATE,
  converted INT, -- 0/1
  conversion_date DATE
);

INSERT INTO experiments VALUES
(1,'A','2025-12-01',1,'2025-12-05'),
(2,'A','2025-12-01',1,'2025-12-03'),
(3,'A','2025-12-01',0,NULL),
(4,'B','2025-12-01',0,NULL),
(5,'B','2025-12-01',1,'2025-12-04'),
(6,'B','2025-12-01',1,'2025-12-10'),
(7,'A','2025-12-01',0,NULL),
(8,'B','2025-12-01',0,NULL),
(9,'A','2025-12-01',1,'2025-12-08'),
(10,'B','2025-12-01',0,NULL);

---

문제 1 (쉬움 · 10분) — 국가별 유저 수

질문: users 테이블을 이용해 국가별 사용자 수를 구하라.
기대 출력 (정답 예시):

country	user_count
KR	7
US	3

힌트: GROUP BY country 사용. NULL 없음 가정.
채점 포인트: 정확한 집계, 정렬 요구가 없으면 값만 맞으면 정답.

select country , count(distinct user_id) as user_count
from users
group by country;

---

문제 2 (쉬움 · 12분) — 가입~첫구매 평균 소요일수

질문: users와 events를 사용해 구매 이벤트(purchase) 를 한 유저들만 대상으로 first_purchase_date - signup_date의 평균(일 단위)을 구하라. (구매가 없는 유저는 제외)
기대 출력(스칼라): avg_days_to_first_purchase = 9.75 (소수 둘째 자리 반올림 허용)

힌트: 먼저 각 유저의 첫 구매일을 구한 뒤 signup과 차이를 계산. DATEDIFF 또는 DB별 날짜 함수 주의.
채점 포인트: null 제외 처리, 날짜 차이 계산 단위(일) 확인.

---

문제 2 에서

select *
from users u join events e
on u.user_id = e.user_id
where exists (
	select 1
	from events
	where event_type = "purchase"
)

구매 이벤트(purchase) 를 한 유저들만 대상으로 만 조회하려고 했었는데, 이거 exists 사용을 잘못해서 모든 결과가 잘 나오고 있었다.

서브쿼리가 바깥 쿼리와 아무 관계가 없어서 일어난 사단인데,

내가 적은 exists 조건을 봐 보면

• events 테이블에 purchase 이벤트가 1건이라도 있으면
• EXISTS는 항상 TRUE

그 결과 모든 row가 필터 없이 통과하게 되는 것.
그래서 구매한 적 없는 유저(3,4,5,8,10번)까지 전부 출력된 것이다..

내가 원하는 조건대로 exists를 쓰려면 이렇게 썼어야 했다.

select *
from users u join events e
on u.user_id = e.user_id
where exists (
	select 1
	from events p
	where p.user_id = u.user_id
    and p.event_type = 'purchase'
)

이벤트 타입이 'purchase' 인 이벤트만 존재하는 테이블 events p 에서
존재하는 p.user_id 들과 같은 id를 가진 유저들을 추리고싶은 거니까
where p.user_id = u.user_id 조건을 써야한다

하지만 해당 문제는

---

조금 어렵게 생각하고 있었나 보다. 사실 구매를 했는지 여부만 보면 가입일은 아이디별로 정해져 있기 때문에 event_type 만 where절로 필터링 해서 나온 조건들로만 집계했다

with first_purchase as (
	select u.user_id, u.signup_date, min(e.event_date) first_purchase
	from users u join events e
	on u.user_id = e.user_id
	where e.event_type = 'purchase'
	group by 1
)
select avg(DATEDIFF(first_purchase ,signup_date)) avg_days_to_first_purchase
from first_purchase 

---

문제 3 (쉬움 → 중간 · 15분) — 이벤트가 많은 상위 3 유저

질문: events에서 이벤트 수가 많은 상위 3명의 user_id와 event_count를 내림차순으로 출력하라. 동률 처리: 동률이면 user_id 오름차순.
기대 출력:

user_id	event_count
1	3
3	3
6	3

힌트: GROUP BY user_id, ORDER BY event_count DESC, user_id ASC, LIMIT 3.
채점 포인트: 동률 정렬 규칙 준수, 정확한 집계.

select u.user_id, count(e.event_id) as event_count
from events e join users u
on e.user_id = u.user_id
group by u.user_id
order by 2 desc, 1
limit 3

---

문제 4 (중간 · 25분) — 퍼널 / 7일 리텐션 (cohort retention)

질문: 각 signup_date 별 코호트(같은 signup_date로 묶음)에 대해 cohort_size(그 날 가입한 고유 유저 수)와 가입일 기준 +7일에 이벤트(어떤 이벤트든) 가 발생한 고유 유저 수(retained_count) 및 7일 리텐션 비율(retention_7d)을 구하라. (출력 컬럼: signup_date, cohort_size, retained_count, retention_7d)
기대 출력 (부분):

signup_date	cohort_size	retained_count	retention_7d
2025-11-25	1	1	1.0
2025-11-26	1	1	1.0
2025-11-27	2	0	0.0
2025-11-28	1	0	0.0
2025-12-01	1	0	0.0
...	...	...	...

(샘플 데이터 전체 코호트에 대한 값 포함)

힌트 / 접근:

1. 각 유저의 signup_date를 기준으로 signup_date + INTERVAL 7 DAY를 계산.
2. events에서 event_date = signup_date + 7인 경우를 카운트(고유 유저 수).
3. 조인 후 코호트 단위로 COUNT(DISTINCT user_id)로 집계.
   채점 포인트: DISTINCT 사용으로 중복 이벤트 처리, 날짜 더하기 연산 정확성, 코호트가 없는 경우 0 처리.

# retention d+7
# 가입일 +7일에 다시행동한 코호트 유저 수 / 가입일 코호트 유저 수


SELECT 
u.signup_date,
count(distinct u.user_id) as cohort_size,
count(distinct case
	when e.event_date = DATE_ADD(u.signup_Date, INTERVAL 7 DAY)
	then u.user_id
end) as retained_count,
count(distinct case
	when e.event_date = DATE_ADD(u.signup_date, INTERVAL 7 day)
	THEN u.user_id
end
) * 1.0 / count(distinct u.user_id) as retention_7d
from users u 
left join events e
on u.user_id = e.user_id
group by u.signup_date
order by u.signup_date

• 코호트 유저는 전부 포함 (LEFT JOIN)
• 리텐션 조건은 CASE WHEN으로만 계산
• 이벤트가 없는 유저도 cohort_size에 포함됨 → 정의적으로 정확
• INNER JOIN은 조인에 성공한 행만 남김 → 이벤트 없는 유저는 사라짐.

---

LEFT JOIN을 써야 하는 경우

• “전체 모수(기준 집합)를 유지해야 하는 분석”
• 코호트, 리텐션, 퍼널 시작 단계
• 분모가 users / sessions / signups 같은 경우

INNER JOIN을 써도 되는 경우

• “특정 행동을 한 사람만 보고 싶을 때”
• 예: 구매 유저 분석, 전환 후 행동 분석
• 이미 분모가 이벤트 기반일 때

---

문제 5 (중간→상 · 20분) — MAU (월간 활성 사용자) by user_type

질문: 2025-12월(2025-12-01 ~ 2025-12-31) 동안 적어도 한 번이라도 이벤트를 발생시킨 유저의 고유 수를 user_type별로 구하라. (출력: user_type, mau)
기대 출력:

user_type	mau
free	5
guest	2
paid	3

힌트: events.event_date를 월 단위로 필터한 다음 DISTINCT user_id와 JOIN users로 user_type을 가져온다.
채점 포인트: 월 필터(YYYY-MM), user_type 매칭, 중복 user_id 제거.

SELECT u.user_type, count(distinct u.user_id)
from users u join events e
on u.user_id = e.user_id
and e.event_date >= '2025-12-01'
and e.event_date <= '2025-12-31'
group by u.user_type

---

문제 6 (중상 · 25분) — 7일 롤링 활성 사용자(어느 특정 날짜 기준)

질문: 2025-12-10을 기준으로 직전 7일(12-04 ~ 12-10, inclusive) 동안 이벤트를 발생시킨 고유 유저 수를 user_type별로 구하라. (출력: user_type, active_7d)
기대 출력:

user_type	active_7d
free	4
guest	1
paid	2

힌트: 날짜 범위 필터 후 DISTINCT user_id로 집계. 실무에선 윈도우 함수나 날짜 시계열을 잘 다룸.
채점 포인트: 날짜 범위 정확성(+inclusive), user_type 매칭, 중복 제거.

select u.user_type, count(distinct u.user_id) as active_7d
from users u join events e
on u.user_id = e.user_id
and e.event_date <= '2025-12-10'
and e.event_date >= '2025-12-04'
group by user_type

---

좋습니다 — 요청하신 대로 원래 문제를 수정한 최종 버전(시험지에 바로 붙여넣을 수 있는 형태)을 깔끔하게 정리해 드립니다.
문제 본문, 예시 데이터(스키마 + 샘플), 기대 출력(샘플), 풀기 힌트, 모범답안(두 가지: z-score만 SQL / z + p-value 근사식(SQL)), 채점 기준을 모두 포함합니다.
시험지/문제은행에 바로 넣으시면 됩니다.

---

문제 7 (어려움 · 30분) — A/B 테스트 통계 지표 산출 (SQL로 통계지표 계산)

experiments 테이블을 이용해 각 버전(variant A/B)에 대해 users, conversions, conversion_rate를 구하고, A vs B의 차이에 대한 z-score와 two-sided p-value(근사 허용)를 SQL로 계산하라.

주의: 일부 DB는 표준정규분포 CDF(Φ)나 ERF 함수를 제공하지 않으므로 p-value는 SQL 내 근사(Abramowitz–Stegun 등)로 계산하거나, SQL로 z-score까지만 제출하고 p-value는 Python/R로 계산해 함께 제출해도 채점 대상이 된다.

출력 요구

1. variant, users, conversions, conv_rate (variant별 summary table)
2. 별도 블록(또는 한 행)으로 z_score (필수)
3. (선택) p_value (two-sided, 근사 허용) — SQL 근사 제출 시 근사 방법 명시

샘플 기대값 (우리 샘플데이터 기준)

• Variant A: users=5, conversions=3, conv_rate=0.6
• Variant B: users=5, conversions=2, conv_rate=0.4
• pooled p = 5/10 = 0.5
• z ≈ -0.6324555 (p_B - p_A 기준)
• p_value ≈ 0.5271 (two-sided, 근사)

힌트(중요)

• $p_A = x_A / n_A,; p_B = x_B / n_B$
• pooled $p = \dfrac{x_A + x_B}{n_A + n_B}$
• $SE = \sqrt{p_{pool}(1-p_{pool})\left(\dfrac{1}{n_A}+\dfrac{1}{n_B}\right)}$
• $z = \dfrac{p_B - p_A}{SE}$
• two-sided $p_value = 2 \times (1 - \Phi(|z|))$ (Φ는 표준정규 CDF)
• DB에 Φ/ERF가 없으면 Abramowitz–Stegun 근사 등으로 p-value를 계산하거나, z만 제출하고 Python/R로 p-value를 계산해서 함께 제출 가능

---

예시 테이블 스키마 및 샘플 데이터 (시험지에 포함 권장)

CREATE TABLE experiments (
  user_id INT PRIMARY KEY,
  variant CHAR(1), -- 'A' or 'B'
  assigned_date DATE,
  converted INT, -- 0 or 1
  conversion_date DATE
);

INSERT INTO experiments VALUES
(1,'A','2025-12-01',1,'2025-12-05'),
(2,'A','2025-12-01',1,'2025-12-03'),
(3,'A','2025-12-01',0,NULL),
(4,'B','2025-12-01',0,NULL),
(5,'B','2025-12-01',1,'2025-12-04'),
(6,'B','2025-12-01',1,'2025-12-10'),
(7,'A','2025-12-01',0,NULL),
(8,'B','2025-12-01',0,NULL),
(9,'A','2025-12-01',1,'2025-12-08'),
(10,'B','2025-12-01',0,NULL);

(위 샘플에서 A: users=5, conversions=3, B: users=5, conversions=2)

---

채점 포인트

1. variant별 users, conversions, conv_rate 정확성 (중복·NULL 처리) — 40%
2. pooled_p·SE·z 공식 구현 정확성 (수치 형변환, 0나누기 방지) — 35%
3. p-value 처리(근사/외부 계산 허용 여부 명시) 및 근사식 구현(선택) — 15%
4. 쿼리 가독성·예외처리(주석, NULLIF 등) — 10%

---

일단 내 답안

with ab_result as (
	select variant, 
	count(distinct u.user_id) users , 
	count(if(e.converted = 1, 1, null)) conversions,
	count(if(e.converted = 1, 1, null) ) /count(distinct u.user_id) conv_rate
	from experiments e join users u
	on e.user_id = u.user_id
	group by variant
), 
pooled_calc as (
	select sum(users) sum_n, sum(conversions) sum_x, sum(conversions)/sum(users) pooled_p
	from ab_result
),
se_calc as (
	select 
	SQRT(
			( select pooled_p from pooled_calc ) * 
			( 1-(select pooled_p from pooled_calc)) *
			( 1/(select max(users) from ab_result where variant ='A' ) +
			( 1/(select max(users) from ab_result where variant ='B' )) 
		)
	) as se_val
), 
z_calc as (
select  
	((select conv_rate from ab_result where variant = 'B') 
	- (select conv_rate from ab_result where variant = 'A'))
	/ se_val as z_score
	from se_calc 
)
select z_score
from z_calc

모범답안 예시

SQL로 variant별 집계 + z-score (MySQL 문법, 안전한 NULL/0 처리)

WITH conversions AS (
    SELECT
        variant,
        COUNT(DISTINCT user_id) AS users,
        COUNT(IF(converted = 1, 1, NULL)) AS conversions,
        COUNT(IF(converted = 1, 1, NULL)) * 1.0 / COUNT(DISTINCT user_id) AS conv_rate
    FROM experiments
    GROUP BY variant
),
ab AS (
    SELECT
        a.users AS n_a,
        a.conversions AS x_a,
        a.conv_rate AS p_a,
        b.users AS n_b,
        b.conversions AS x_b,
        b.conv_rate AS p_b
    FROM conversions a
    JOIN conversions b
      ON a.variant = 'A'
     AND b.variant = 'B'
),
stats AS (
    SELECT *,
        (x_a + x_b) * 1.0 / NULLIF(n_a + n_b, 0) AS pooled_p
    FROM ab
)
SELECT
    n_a, x_a, p_a,
    n_b, x_b, p_b,
    pooled_p,
    -- 표준오차 (SE)
    SQRT(
      pooled_p * (1 - pooled_p)
      * (1.0 / NULLIF(n_a, 0) + 1.0 / NULLIF(n_b, 0))
    ) AS se,
    -- z-score
    (p_b - p_a) /
    NULLIF(
      SQRT(
        pooled_p * (1 - pooled_p)
        * (1.0 / NULLIF(n_a, 0) + 1.0 / NULLIF(n_b, 0))
      ), 0
    ) AS z_score
FROM stats;

• 출력에 variant별 summary(table)와 별도 z_score row(또는 같은 쿼리의 마지막 컬럼) 제출 권장.

• NULLIF로 0나누기 방지.

• 이 쿼리는 z와 p_value_approx(two-sided)를 반환합니다.

• 시험 제출 시 “Abramowitz–Stegun 근사 사용”이라고 주석/설명 추가하면 좋음.

보충 설명: pooled_p, SE, z — 왜 쓰는가 (요약)

• pooled_p: 귀무가설 (H_0: p_A = p_B) 하에서 두 그룹을 하나로 합친 전체 전환율 추정치. 표준오차 계산에 사용.
• SE (표준오차): 두 비율 (p_B - p_A) 차이의 분포에서 표준편차 역할을 한다. 표본 크기와 pooled_p에 의존.
• z-score: 차이가 SE의 몇 배인지를 나타내는 척도. |z|가 클수록 단순 우연으로 발생할 가능성이 낮음(즉 통계적으로 유의할 가능성 높음).
• 사용처: 대규모 샘플의 A/B 테스트에서 두 비율(전환율 등)의 차이가 우연인지 아닌지 빠르게 판단할 때 표준적으로 사용.
• 한계: 작은 표본, 희귀 전환(very low p), 또는 비독립 표본에서는 z-test(정규근사)가 부정확 → Fisher exact test 또는 베이지안 방법 권장.