SQL 코딩 테스트 연습

유예지·2024년 9월 26일
sql
  • 출처 : ChatGPT

값 수정

문제

UPDATE enrollments
SET year = 2015
WHERE id BETWEEN 20 AND 100;

1. AARRR 집계

문제 1. Acquisition(유입) 계산

  • 문제 :
    사용자 로그 테이블 user_logs가 있습니다. 이 테이블에는 사용자가 앱에 접속한 내역이 저장되어 있습니다. 다음은 테이블의 스키마입니다.
  • user_id : 사용자 고유 ID
  • event_time : 이벤트 발생 시간 (로그인 시간)
  • source : 사용자 유입 경로 (예: 'Google', 'Facebook', 'Organic')

유입 경로별로 최근 30일 동안 신규 유입된 사용자의 수를 계산하는 SQL 쿼리를 작성하세요.

SELECT source, COUNT(DISTINCT user_id) AS new_users
FROM user_logs
WHERE event_time >= NOW() - INTERVAL 30 DAY
GROUP BY source;

문제 2. Activation(활성화) 계산

  • 문제 :
    user_logs 테이블에서 신규 사용자가 처음으로 앱에 로그인한 후 특정 액션(예: 'purchase')을 수행하는지 확인하고자 합니다. event_type이라는 컬럼이 추가되어 있고, 이 컬럼에는 이벤트 유형(로그인, 구매 등)이 기록됩니다. event_type이 'login'인 사용자가 'purchase'를 했는지 확인하여 활성화 비율을 계산하는 SQL 쿼리를 작성하세요.
WITH first_login AS (
SELECT user_id, MIN(event_time) AS first_login_time
FROM user_logs
WHERE event_type = 'login'
GROUP BY user_id),
first_purchase AS (
SELECT user_id, MIN(event_time) AS first_purchase_time
FROM user_logs
WHERE event_type = 'purchase'
GROUP BY user_id)

SELECT COUNT(fp.user_id) * 100.0 / COUNT(fl.user_id) AS activation_rate
FROM first_login fl LEFT JOIN first_purchase fp
ON fl.user_id = fp.user_id
AND fp.first_purchase_time >= fl.first_login_time;

문제 3. Retention(유지율) 계산

  • 문제 :
    user_logs 테이블에서 사용자가 특정 날짜에 앱에 접속한 후 7일 후에도 다시 접속했는지 확인하여 7일차 리텐션율을 계산하는 SQL 쿼리를 작성하세요.
WITH initial_users AS (
SELECT user_id, MIN(DATE(event_time)) AS first_login_day
FROM user_logs
WHERE event_type = 'login'
GROUP BY user_id),
retained_users AS (
SELECT user_id, MIN(DATE(event_time)) AS return_day
FROM user_logs
WHERE event_type = 'login'
AND DATE(event_time) >= first_login_day + INTERVAL 7 DAY
GROUP BY user_id)

SELECT COUNT(DISTINCT ru.user_id) * 100.0 / COUNT(DISTINCT iu.user_id) AS retention_rate
FROM initial_users iu LEFT JOIN retained_users ru
ON iu.user_id = ru.user_id;

문제 4. Referral(추천 사용자) 집계

  • 문제 :
    사용자 추천 이벤트가 기록된 테이블 referrals이 있습니다. 이 테이블에는 사용자가 다른 사용자를 추천한 내역이 저장되어 있습니다. 테이블의 스키마는 다음과 같습니다.
  • referrer_id : 추천한 사용자 ID
  • referred_id : 추천 받은 사용자 ID
  • referral_time : 추천이 발생한 시간

최근 30일 동안 각 사용자가 추천한 사용자의 수를 계산하는 SQL 쿼리를 작성하세요.

SELECT referrer_id, COUNT(DISTINCT referred_id) AS referral_count
FROM referrals
WHERE referral_time >= NOW() - INTERVAL 30 DAY
GROUP BY referrer_id
ORDER BY referral_count DESC;

문제 5. Revenue(수익) 집계

  • 문제 :
    구매 내역을 저장한 테이블 purchases가 있습니다. 이 테이블의 스키마는 다음과 같습니다.
  • user_id : 구매한 사용자 ID
  • purchase_amount : 구매 금액
  • purchase_time : 구매가 발생한 시간

최근 7일 동안 구매한 사용자의 평균 구매 금액(ARPU, Average Revenue Per User)을 계산하는 SQL 쿼리를 작성하세요.

WITH recent_purchases AS (
SELECT user_id, SUM(purchase_amont) AS total_amount
FROM purchases
WHERE purchase_time >= NOW() - INTERVAL 7 DAY
GROUP BY user_id)

SELECT AVG(total_amount) AS arpu
FROM recent_purchases;

2. AB Test

문제 1. A/B 테스트 성과 분석 (전환율 비교)

  • 문제 :
    A/B 테스트 데이터를 담고 있는 테이블 ab_test_results가 있습니다. 이 테이블의 스키마는 다음과 같습니다.
  • user_id : 사용자 고유 ID
  • group : 사용자가 속한 그룹 ('A' 또는 'B')
  • event_type : 사용자가 발생시킨 이벤트 ('view', 'conversion')
  • event_time : 이벤트 발생 시간

두 그룹의 전환율을 비교하는 SQL 쿼리를 작성하세요. 전환율은 conversion 이벤트를 발생시킨 사용자 비율로 계산합니다.

WITH total_users AS (
SELECT group, COUNT(DISTINCT user_id) AS total_users
FROM ab_test_results
WHERE event_type = 'view'
GROUP BY group),
converted_users AS (
SELECT group, COUNT(DISTINCT user_id) AS converted_users
FROM ab_test_results
WHERE event_type = 'conversion'
GROUP BY group)

SELECT tu.group, cu.converted_users * 100.0 / tu.total_users AS conversion_rate
FROM total_users tu JOIN converted_users
ON tu.group = cu.group;

문제 2. A/B 테스트 유효성 검증 (통계적 유의성)

  • 문제 :
    ab_test_results 테이블에서 각 그룹의 전환율을 기반으로 A/B 테스트가 통계적으로 유의미한 차이를 보이는지 검증하고자 합니다. 이를 위해 각 그룹의 전환율 및 표본 수를 계산하는 SQL 쿼리를 작성하고, 차후에 이를 기반으로 통계적 검증(Z-test 등)을 수행할 수 있도록 준비합니다.
WITH total_users AS (
SELECT group, COUNT(DISTINCT user_id) AS total_users
FROM ab_test_results
WHERE event_type = 'view'
GROUP BY group),
converted_users AS (
SELECT group, COUNT(DISTINCT user_id) AS converted_users
FROM ab_test_results
WHERE event_type = 'conversion'
GROUP BY group)

SELECT tu.group, tu.total_users, cu.converted_users,
cu.converted_users * 100.0 / tu.total_users AS conversion_rate
FROM total_users tu JOIN converted_users cu
ON tu.group = cu.group;

문제 3. A/B 테스트에서 클릭률(CTR) 비교

  • 문제 :
    A/B 테스트 데이터 테이블 ab_test_clicks에서 클릭 이벤트를 분석하고자 합니다. 테이블의 스키마는 다음과 같습니다.
  • user_id : 사용자 고유 ID
  • group : 사용자가 속한 그룹 ('A' 또는 'B')
  • event_type : 이벤트 유형 ('impression', 'click')
  • event_time : 이벤트 발생 시간

각 그룹에서 클릭률(CTR, Click-Through Rate)을 계산하는 SQL 쿼리를 작성하세요. 클릭률은 클릭 수를 노출 수(impressions)로 나누어 계산합니다.

WITH impressions AS (
SELECT group, COUNT(DISTINCT user_id) AS impression_count
FROM ab_test_clicks
WHERE event_type = 'impression'
GROUP BY group),
clicks AS (
SELECT group, COUNT(DISTINCT user_id) AS click_count
FROM ab_test_clicks
WHERE event_type = 'click'
GROUP BY group)

SELECT i.group, c.click_count * 100.0 / i.impression_count AS ctr
FROM impressions i JOIN clicks c
ON i.group = c.group;

문제 4. A/B 테스트에서 구매 전환율(CVR) 분석

  • 문제 :
    A/B 테스트 데이터 테이블 ab_test_purchases에서 각 그룹의 구매 전환율을 분석하려고 합니다. 테이블의 스키마는 다음과 같습니다.
  • user_id : 사용자 고유 ID
  • group : 사용자가 속한 그룹 ('A' 또는 'B')
  • event_type : 이벤트 유형 ('view', 'purchase')
  • event_time : 이벤트 발생 시간

각 그룹에서 제품 페이지를 본 사용자 중에서 실제로 구매를 완료한 사용자의 비율(구매 전환율, CVR)을 계산하는 SQL 쿼리를 작성하세요.

WITH views AS (
SELECT group, COUNT(DISTINCT user_id) AS view_count
FROM ab_test_purchases
WHERE event_type = 'view'
GROUP BY group),
purchases AS (
SELECT group, COUNT(DISTINCT user_id) AS purchase_count
FROM ab_test_purchases
WHERE event_type = 'purchase'
GROUP BY group)

SELECT v.group, p.purchase_count * 100.0 / v.view_count
FROM views v JOIN purchases p
ON v.group = p.group;

문제 5. A/B 테스트 실험 기간 동안 유지율 분석

  • 문제 :
    A/B 테스트 데이터를 기반으로 실험 기간 동안 유지율을 분석합니다. 사용자가 첫 방문 이후 7일 후에 다시 방문했는지 확인하려고 합니다. ab_test_retention 테이블의 스키마는 다음과 같습니다.
  • user_id : 사용자 고유 ID
  • group : 사용자가 속한 그룹 ('A' 또는 'B')
  • event_time : 이벤트 발생 시간
  • event_type : 이벤트 유형 ('first_visit', 'return_visit')

첫 방문 후 7일 후에 돌아온 사용자의 비율(7일차 유지율)을 각 그룹별로 계산하는 SQL 쿼리를 작성하세요.

WITH first_visits AS (
SELECT user_id, group, MIN(DATE(event_time)) AS first_visit_day
FROM ab_test_retention
WHERE event_type = 'first_visit'
GROUP BY user_id, group),
return_visits AS (
SELECT user_id, group, MIN(DATE(event_time)) AS return_visit_day
FROM ab_test_retention
WHERE event_type = 'return_visit'
GROUP BY user_id, group)

SELECT fv.group,
COUNT(DISTINCT rv.user_id) * 100.0 / COUNT(DISTINCT fv.user_id) AS day_7_retention_rate
FROM first_visits fv JOIN return_visits rv
ON fv.user_id = rv.user_id
AND return_visit_day >= first_visit_day + INTERVAL 7 DAY
GROUP BY fv.group;
profile
유예지
이전 포스트

[docker 명령어] 용량 확인

다음 포스트

poetry 가상환경 세팅 및 활성화 방법

0개의 댓글