Tables
erDiagram
s[Signups] {
user_id int "UK"
time_stamp datetime
}
c[Confirmations] {
user_id int "PK, FK"
time_stamp datetime "PK"
action ENUM
}
c }| .. || s : request
Mermaid
복사
DataFlow(tables)
confirmations -> where action confirmed -> groupby user_id(user_cnt)
confirmations -> groupby user_id(total_cnt)
signups -> left join user_cnt -> left join total_cnt -> result
Answer
WITH user_cnt AS (
SELECT
user_id,
COUNT(user_id) AS user_cnt
FROM Confirmations
WHERE action = "confirmed"
GROUP BY user_id
),
total_cnt AS (
SELECT
user_id,
COUNT(user_id) AS total_cnt
FROM Confirmations
GROUP BY user_id
)
SELECT
s.user_id,
ROUND(COALESCE(user_cnt / total_cnt, 0), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN user_cnt uc
ON s.user_id = uc.user_id
LEFT JOIN total_cnt tc
ON s.user_id = tc.user_id
SQL
복사