QCC 3회차

1.임직원 로그인 빈도 분석

WITH success_logins AS (
    SELECT employee_id, COUNT(distinct login_id) AS unique_logins
    FROM qcc.logins
    WHERE login_result = 'SUCCESS'
      AND login_time >= '2023-07-01'
      AND login_time < '2023-10-01'
    GROUP BY employee_id
)
SELECT unique_logins, COUNT(1) AS employee_count
FROM success_logins
GROUP BY unique_logins
ORDER BY unique_logins

---

2.세 번째로 높은 급여를 받는 직원

WITH salary_ranked AS (
    SELECT employee_id, name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM qcc.employee_salary
)
SELECT employee_id, name, salary
FROM salary_ranked
WHERE rnk = 3
ORDER BY employee_id

---

3.부서 간 메세지 비율 계산

SELECT
  ROUND(100.0 * SUM(CASE WHEN e1.department != e2.department THEN 1 ELSE 0 END) / COUNT(*), 1) AS inter_department_msg_pct
FROM qcc.messages m
JOIN qcc.employees e1 ON m.sender_id = e1.employee_id
JOIN qcc.employees e2 ON m.receiver_id = e2.employee_id

---

4.(도전)광고 성과 Attribution 분석

WITH converted_users AS (
    SELECT DISTINCT us.user_id
    FROM qcc.ad_attribution a
    JOIN qcc.user_sessions us ON a.session_id = us.session_id
    WHERE a.converted = TRUE
), first_sessions AS (
    SELECT us.user_id, a.session_id, us.created_at, a.channel,
           ROW_NUMBER() OVER (PARTITION BY us.user_id ORDER BY us.created_at) AS rn
    FROM qcc.user_sessions us
    JOIN converted_users cu ON us.user_id = cu.user_id
    JOIN qcc.ad_attribution a ON us.session_id = a.session_id
)
SELECT user_id, channel
FROM first_sessions
WHERE rn = 1
ORDER BY user_id