WITH 
user_first_login AS (
SELECT 
usr_id,
MIN(DATE(login_time)) AS first_login_date
FROM user_login_log
WHERE login_time >= '2024-01-01' 
AND login_time < '2025-04-01'
GROUP BY usr_id
),
cohorts AS (
SELECT 
usr_id,
DATE_FORMAT(first_login_date, '%Y-%m-01') AS cohort_month
FROM user_first_login
WHERE first_login_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY usr_id, DATE_FORMAT(first_login_date, '%Y-%m-01')
),
user_login_months AS (
SELECT 
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
FROM user_login_log
WHERE login_time >= '2024-01-01' 
AND login_time < '2025-04-01'
GROUP BY usr_id, DATE_FORMAT(login_time, '%Y-%m-01')
),
retention_data AS (
SELECT 
c.cohort_month,
c.usr_id,
MAX(CASE WHEN u.login_month = DATE_ADD(c.cohort_month, INTERVAL 1 MONTH) THEN 1 ELSE 0 END) AS retained_1m
FROM cohorts c
LEFT JOIN user_login_months u 
ON c.usr_id = u.usr_id
AND u.login_month IN (
DATE_ADD(c.cohort_month, INTERVAL 1 MONTH)
)
GROUP BY c.cohort_month, c.usr_id
),
retention_summary AS (
SELECT 
cohort_month,
COUNT(usr_id) AS total_users,
SUM(retained_1m) AS retained_1m_count
FROM retention_data
GROUP BY cohort_month
),
final_table AS (
SELECT 
cohort_month,
total_users,
CASE WHEN LAST_DAY(DATE_ADD(cohort_month, INTERVAL 1 MONTH)) < CURRENT_DATE
 THEN ROUND(retained_1m_count * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS retention_1m_rate
FROM retention_summary
ORDER BY cohort_month
)
select 
cohort_month AS current_month,
retention_1m_rate AS t_plus_1_month_retention_rate
from final_table;

WITH 
login_data AS (
SELECT 
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
FROM user_login_log
WHERE login_time >= '2024-01-01' 
AND login_time < '2025-04-01' 
GROUP BY usr_id, DATE_FORMAT(login_time, '%Y-%m-01')
),
months AS (
SELECT '2024-01-01' AS cohort_month
UNION ALL SELECT '2024-02-01'
UNION ALL SELECT '2024-03-01'
UNION ALL SELECT '2024-04-01'
UNION ALL SELECT '2024-05-01'
UNION ALL SELECT '2024-06-01'
UNION ALL SELECT '2024-07-01'
UNION ALL SELECT '2024-08-01'
UNION ALL SELECT '2024-09-01'
UNION ALL SELECT '2024-10-01'
UNION ALL SELECT '2024-11-01'
UNION ALL SELECT '2024-12-01'
),
user_active_months AS (
SELECT 
usr_id,
login_month
FROM login_data
GROUP BY usr_id, login_month
),
cohorts AS (
SELECT 
login_month AS cohort_month,
usr_id
FROM user_active_months
WHERE login_month BETWEEN '2024-01-01' AND '2024-12-01'
GROUP BY cohort_month, usr_id
),
retention_data AS (
SELECT 
c.cohort_month,
c.usr_id,
MAX(CASE WHEN u.login_month = DATE_ADD(c.cohort_month, INTERVAL 1 MONTH) THEN 1 ELSE 0 END) AS retained_1m
FROM cohorts c
LEFT JOIN user_active_months u 
ON c.usr_id = u.usr_id
AND u.login_month IN (
DATE_ADD(c.cohort_month, INTERVAL 1 MONTH)
)
GROUP BY c.cohort_month, c.usr_id
),
retention_summary AS (
SELECT 
cohort_month,
COUNT(usr_id) AS total_users,
SUM(retained_1m) AS retained_1m_count
FROM retention_data
GROUP BY cohort_month
),
final_table AS (
SELECT 
cohort_month,
total_users,
CASE WHEN LAST_DAY(DATE_ADD(cohort_month, INTERVAL 1 MONTH)) < CURRENT_DATE
 THEN ROUND(retained_1m_count * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS retention_1m_rate
FROM retention_summary
ORDER BY cohort_month
)
select 
cohort_month AS current_month,
retention_1m_rate AS t_plus_1_month_retention_rate 
FROM final_table
WHERE cohort_month != '2024-12-01';

WITH 
login_data AS (
SELECT 
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
FROM user_login_log
WHERE login_time >= '2024-01-01' 
AND login_time < '2025-04-01' 
GROUP BY usr_id, DATE_FORMAT(login_time, '%Y-%m-01')
),
months AS (
SELECT '2024-01-01' AS cohort_month
UNION ALL SELECT '2024-02-01'
UNION ALL SELECT '2024-03-01'
UNION ALL SELECT '2024-04-01'
UNION ALL SELECT '2024-05-01'
UNION ALL SELECT '2024-06-01'
UNION ALL SELECT '2024-07-01'
UNION ALL SELECT '2024-08-01'
UNION ALL SELECT '2024-09-01'
UNION ALL SELECT '2024-10-01'
UNION ALL SELECT '2024-11-01'
UNION ALL SELECT '2024-12-01'
),
user_active_months AS (
SELECT 
usr_id,
login_month
FROM login_data
GROUP BY usr_id, login_month
),
cohorts AS (
SELECT 
login_month AS cohort_month,
usr_id
FROM user_active_months
WHERE login_month BETWEEN '2024-01-01' AND '2024-12-01'
GROUP BY cohort_month, usr_id
),
retention_data AS (
SELECT 
c.cohort_month,
c.usr_id,
MAX(CASE WHEN u.login_month = DATE_ADD(c.cohort_month, INTERVAL 1 MONTH) THEN 1 ELSE 0 END) AS retained_1m
FROM cohorts c
LEFT JOIN user_active_months u 
ON c.usr_id = u.usr_id
AND u.login_month IN (
DATE_ADD(c.cohort_month, INTERVAL 1 MONTH)
)
GROUP BY c.cohort_month, c.usr_id
),
retention_summary AS (
SELECT 
cohort_month,
COUNT(usr_id) AS total_users,
SUM(retained_1m) AS retained_1m_count
FROM retention_data
GROUP BY cohort_month
),
final_table AS (
SELECT 
cohort_month,
total_users,
CASE WHEN LAST_DAY(DATE_ADD(cohort_month, INTERVAL 1 MONTH)) < CURRENT_DATE
 THEN ROUND(retained_1m_count * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS retention_1m_rate
FROM retention_summary
ORDER BY cohort_month
)
select 
cohort_month AS current_month,
retention_1m_rate AS t_plus_1_month_retention_rate 
FROM final_table;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY usr_id, DATE(login_time))
,
user_retention AS (
SELECT 
T1.login_date AS first_login_date,
T1.usr_id,
MAX(CASE WHEN T2.login_date = T1.login_date + INTERVAL 1 DAY THEN 1 ELSE 0 END) AS retained_1d,
MAX(CASE WHEN T2.login_date = T1.login_date + INTERVAL 3 DAY THEN 1 ELSE 0 END) AS retained_3d,
MAX(CASE WHEN T2.login_date = T1.login_date + INTERVAL 7 DAY THEN 1 ELSE 0 END) AS retained_7d,
MAX(CASE WHEN T2.login_date = T1.login_date + INTERVAL 14 DAY THEN 1 ELSE 0 END) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date IN (
T1.login_date + INTERVAL 1 DAY,
T1.login_date + INTERVAL 3 DAY,
T1.login_date + INTERVAL 7 DAY,
T1.login_date + INTERVAL 14 DAY
)
GROUP BY T1.login_date, T1.usr_id)
,
retention_summary AS (
SELECT 
first_login_date,
COUNT(usr_id) AS total_users,
SUM(retained_1d) AS retained_1d_users,
SUM(retained_3d) AS retained_3d_users,
SUM(retained_7d) AS retained_7d_users,
SUM(retained_14d) AS retained_14d_users
FROM user_retention
GROUP BY first_login_date)
SELECT 
first_login_date,
CASE WHEN first_login_date + INTERVAL 1 DAY <= CURRENT_DATE
 THEN ROUND(retained_1d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_1_retention_rate,
CASE WHEN first_login_date + INTERVAL 3 DAY <= CURRENT_DATE
 THEN ROUND(retained_3d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_3_retention_rate,
CASE WHEN first_login_date + INTERVAL 7 DAY <= CURRENT_DATE
 THEN ROUND(retained_7d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_7_retention_rate,
CASE WHEN first_login_date + INTERVAL 14 DAY <= CURRENT_DATE
 THEN ROUND(retained_14d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_14_retention_rate
FROM retention_summary
WHERE first_login_date >= CURRENT_DATE - INTERVAL 30 DAY
ORDER BY first_login_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 120 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS first_login_date,
T1.usr_id,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS retained_1d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 3 DAY) THEN 1 ELSE 0 END) AS retained_3d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS retained_7d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 14 DAY) THEN 1 ELSE 0 END) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date IN (
DATE_ADD(T1.login_date, INTERVAL 1 DAY),
DATE_ADD(T1.login_date, INTERVAL 3 DAY),
DATE_ADD(T1.login_date, INTERVAL 7 DAY),
DATE_ADD(T1.login_date, INTERVAL 14 DAY)
)
GROUP BY T1.login_date, T1.usr_id
)
SELECT 
first_login_date,
ROUND(SUM(retained_1d) * 100.0 / COUNT(usr_id), 2) AS t_plus_1_retention_rate,
ROUND(SUM(retained_3d) * 100.0 / COUNT(usr_id), 2) AS t_plus_3_retention_rate,
ROUND(SUM(retained_7d) * 100.0 / COUNT(usr_id), 2) AS t_plus_7_retention_rate,
ROUND(SUM(retained_14d) * 100.0 / COUNT(usr_id), 2) AS t_plus_14_retention_rate
FROM user_retention
WHERE first_login_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY first_login_date
ORDER BY first_login_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS first_login_date,
T1.usr_id,
MAX(CASE WHEN T2.login_date IS NOT NULL 
AND T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY 
 AND T1.login_date + INTERVAL 3 DAY 
 THEN 1 ELSE 0 END) AS retained_3d,
MAX(CASE WHEN T2.login_date IS NOT NULL 
AND T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY 
 AND T1.login_date + INTERVAL 7 DAY 
 THEN 1 ELSE 0 END) AS retained_7d,
MAX(CASE WHEN T2.login_date IS NOT NULL 
AND T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY 
 AND T1.login_date + INTERVAL 14 DAY 
 THEN 1 ELSE 0 END) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date > T1.login_date
AND T2.login_date <= T1.login_date + INTERVAL 14 DAY
GROUP BY T1.login_date, T1.usr_id
), 
retention_summary AS (
SELECT 
first_login_date,
COUNT(usr_id) AS total_users,
SUM(retained_3d) AS retained_3d_users,
SUM(retained_7d) AS retained_7d_users,
SUM(retained_14d) AS retained_14d_users
FROM user_retention
GROUP BY first_login_date
)
SELECT 
first_login_date,
CASE WHEN first_login_date + INTERVAL 3 DAY <= CURRENT_DATE
 THEN ROUND(retained_3d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_3_retention_rate,
CASE WHEN first_login_date + INTERVAL 7 DAY <= CURRENT_DATE
 THEN ROUND(retained_7d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_7_retention_rate,
CASE WHEN first_login_date + INTERVAL 14 DAY <= CURRENT_DATE
 THEN ROUND(retained_14d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_14_retention_rate
FROM retention_summary
WHERE first_login_date >= CURRENT_DATE - INTERVAL 90 DAY
ORDER BY first_login_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS first_login_date,
T1.usr_id,
MAX(T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY AND T1.login_date + INTERVAL 3 DAY) AS retained_3d,
MAX(T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY AND T1.login_date + INTERVAL 7 DAY) AS retained_7d,
MAX(T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY AND T1.login_date + INTERVAL 14 DAY) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date > T1.login_date
GROUP BY T1.login_date, T1.usr_id
),
retention_summary AS (
SELECT 
first_login_date,
COUNT(usr_id) AS total_users,
SUM(retained_3d) AS retained_3d_users,
SUM(retained_7d) AS retained_7d_users,
SUM(retained_14d) AS retained_14d_users
FROM user_retention
GROUP BY first_login_date
)
SELECT 
first_login_date,
CASE WHEN first_login_date + INTERVAL 3 DAY <= CURRENT_DATE
 THEN ROUND(retained_3d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_3_retention_rate,
CASE WHEN first_login_date + INTERVAL 7 DAY <= CURRENT_DATE
 THEN ROUND(retained_7d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_7_retention_rate,
CASE WHEN first_login_date + INTERVAL 14 DAY <= CURRENT_DATE
 THEN ROUND(retained_14d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_14_retention_rate
FROM retention_summary
WHERE first_login_date >= CURRENT_DATE - INTERVAL 90 DAY
ORDER BY first_login_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS first_login_date,
T1.usr_id,
MAX(CASE WHEN T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY AND T1.login_date + INTERVAL 3 DAY THEN 1 ELSE 0 END) AS retained_3d,
MAX(CASE WHEN T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY AND T1.login_date + INTERVAL 7 DAY THEN 1 ELSE 0 END) AS retained_7d,
MAX(CASE WHEN T2.login_date BETWEEN T1.login_date + INTERVAL 1 DAY AND T1.login_date + INTERVAL 14 DAY THEN 1 ELSE 0 END) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date > T1.login_date
GROUP BY T1.login_date, T1.usr_id
),
retention_summary AS (
SELECT 
first_login_date,
COUNT(usr_id) AS total_users,
SUM(retained_3d) AS retained_3d_users,
SUM(retained_7d) AS retained_7d_users,
SUM(retained_14d) AS retained_14d_users
FROM user_retention
GROUP BY first_login_date
)
SELECT 
first_login_date,
CASE WHEN first_login_date + INTERVAL 3 DAY <= CURRENT_DATE
 THEN ROUND(retained_3d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_3_retention_rate,
CASE WHEN first_login_date + INTERVAL 7 DAY <= CURRENT_DATE
 THEN ROUND(retained_7d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_7_retention_rate,
CASE WHEN first_login_date + INTERVAL 14 DAY <= CURRENT_DATE
 THEN ROUND(retained_14d_users * 100.0 / NULLIF(total_users, 0), 2)
 ELSE NULL END AS t_plus_14_retention_rate
FROM retention_summary
WHERE first_login_date >= CURRENT_DATE - INTERVAL 90 DAY
ORDER BY first_login_date;

WITH daily_unique_logins AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
retention_days AS (
    SELECT
        dul1.usr_id,
        dul1.login_date AS first_login_date,
        dul2.login_date AS next_day_login_date,
        DATEDIFF(dul2.login_date, dul1.login_date) AS days_diff
    FROM
        daily_unique_logins dul1
    LEFT JOIN
        daily_unique_logins dul2
    ON
        dul1.usr_id = dul2.usr_id AND
        dul2.login_date BETWEEN dul1.login_date + INTERVAL 1 DAY AND dul1.login_date + INTERVAL 14 DAY
)
SELECT
    first_login_date,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff BETWEEN 1 AND 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff BETWEEN 1 AND 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff BETWEEN 1 AND 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

WITH login_base AS (
SELECT usr_id, DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 104 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS first_login_date,
T1.usr_id,
MAX(T2.login_date BETWEEN DATE_ADD(T1.login_date, INTERVAL 1 DAY) AND DATE_ADD(T1.login_date, INTERVAL 3 DAY)) AS retained_3d,
MAX(T2.login_date BETWEEN DATE_ADD(T1.login_date, INTERVAL 1 DAY) AND DATE_ADD(T1.login_date, INTERVAL 7 DAY)) AS retained_7d,
MAX(T2.login_date BETWEEN DATE_ADD(T1.login_date, INTERVAL 1 DAY) AND DATE_ADD(T1.login_date, INTERVAL 14 DAY)) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date > T1.login_date
GROUP BY T1.login_date, T1.usr_id
),
retention_summary AS (
SELECT 
first_login_date,
COUNT(usr_id) AS total_users,
SUM(retained_3d) AS retained_3d_users,
SUM(retained_7d) AS retained_7d_users,
SUM(retained_14d) AS retained_14d_users
FROM user_retention
GROUP BY first_login_date
)
SELECT 
first_login_date,
CASE WHEN first_login_date + INTERVAL 3 DAY <= CURRENT_DATE
 THEN ROUND(retained_3d_users * 100.0 / total_users, 2)
 ELSE NULL END AS t_plus_3_retention_rate,
CASE WHEN first_login_date + INTERVAL 7 DAY <= CURRENT_DATE
 THEN ROUND(retained_7d_users * 100.0 / total_users, 2)
 ELSE NULL END AS t_plus_7_retention_rate,
CASE WHEN first_login_date + INTERVAL 14 DAY <= CURRENT_DATE
 THEN ROUND(retained_14d_users * 100.0 / total_users, 2)
 ELSE NULL END AS t_plus_14_retention_rate
FROM retention_summary
WHERE first_login_date >= CURRENT_DATE - INTERVAL 90 DAY
ORDER BY first_login_date;

WITH login_base AS (
SELECT usr_id, DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 104 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS cohort_date,
T1.usr_id,
MAX(T2.login_date BETWEEN DATE_ADD(T1.login_date, INTERVAL 1 DAY) AND DATE_ADD(T1.login_date, INTERVAL 3 DAY)) AS retained_3d,
MAX(T2.login_date BETWEEN DATE_ADD(T1.login_date, INTERVAL 1 DAY) AND DATE_ADD(T1.login_date, INTERVAL 7 DAY)) AS retained_7d,
MAX(T2.login_date BETWEEN DATE_ADD(T1.login_date, INTERVAL 1 DAY) AND DATE_ADD(T1.login_date, INTERVAL 14 DAY)) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date > T1.login_date
GROUP BY T1.login_date, T1.usr_id
),
retention_summary AS (
SELECT 
cohort_date,
COUNT(usr_id) AS total_users,
SUM(retained_3d) AS retained_3d_users,
SUM(retained_7d) AS retained_7d_users,
SUM(retained_14d) AS retained_14d_users
FROM user_retention
GROUP BY cohort_date
)
SELECT 
cohort_date,
CASE WHEN cohort_date + INTERVAL 3 DAY <= CURRENT_DATE
 THEN ROUND(retained_3d_users * 100.0 / total_users, 2)
 ELSE NULL END AS t3_retention_rate,
CASE WHEN cohort_date + INTERVAL 7 DAY <= CURRENT_DATE
 THEN ROUND(retained_7d_users * 100.0 / total_users, 2)
 ELSE NULL END AS t7_retention_rate,
CASE WHEN cohort_date + INTERVAL 14 DAY <= CURRENT_DATE
 THEN ROUND(retained_14d_users * 100.0 / total_users, 2)
 ELSE NULL END AS t14_retention_rate
FROM retention_summary
WHERE cohort_date >= CURRENT_DATE - INTERVAL 90 DAY
ORDER BY cohort_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 120 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS cohort_date,
T1.usr_id,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS retained_1d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 3 DAY) THEN 1 ELSE 0 END) AS retained_3d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS retained_7d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 14 DAY) THEN 1 ELSE 0 END) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date IN (
DATE_ADD(T1.login_date, INTERVAL 1 DAY),
DATE_ADD(T1.login_date, INTERVAL 3 DAY),
DATE_ADD(T1.login_date, INTERVAL 7 DAY),
DATE_ADD(T1.login_date, INTERVAL 14 DAY)
)
GROUP BY T1.login_date, T1.usr_id
)
SELECT 
cohort_date,
ROUND(SUM(retained_1d) * 100.0 / COUNT(usr_id), 2) AS t1_retention,
ROUND(SUM(retained_3d) * 100.0 / COUNT(usr_id), 2) AS t3_retention,
ROUND(SUM(retained_7d) * 100.0 / COUNT(usr_id), 2) AS t7_retention,
ROUND(SUM(retained_14d) * 100.0 / COUNT(usr_id), 2) AS t14_retention
FROM user_retention
WHERE cohort_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY cohort_date
ORDER BY cohort_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= CURRENT_DATE - INTERVAL 120 DAY
GROUP BY usr_id, DATE(login_time)
),
user_retention AS (
SELECT 
T1.login_date AS cohort_date,
T1.usr_id,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS retained_1d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 3 DAY) THEN 1 ELSE 0 END) AS retained_3d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS retained_7d,
MAX(CASE WHEN T2.login_date = DATE_ADD(T1.login_date, INTERVAL 14 DAY) THEN 1 ELSE 0 END) AS retained_14d
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id
AND T2.login_date IN (
DATE_ADD(T1.login_date, INTERVAL 1 DAY),
DATE_ADD(T1.login_date, INTERVAL 3 DAY),
DATE_ADD(T1.login_date, INTERVAL 7 DAY),
DATE_ADD(T1.login_date, INTERVAL 14 DAY)
)
GROUP BY T1.login_date, T1.usr_id
)
SELECT 
cohort_date,
COUNT(usr_id) AS total_users,
ROUND(SUM(retained_1d) * 100.0 / COUNT(usr_id), 2) AS t1_retention,
ROUND(SUM(retained_3d) * 100.0 / COUNT(usr_id), 2) AS t3_retention,
ROUND(SUM(retained_7d) * 100.0 / COUNT(usr_id), 2) AS t7_retention,
ROUND(SUM(retained_14d) * 100.0 / COUNT(usr_id), 2) AS t14_retention
FROM user_retention
WHERE cohort_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY cohort_date
ORDER BY cohort_date;

WITH login_base AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 90
GROUP BY usr_id, DATE(login_time)
),
user_future_logins AS (
SELECT 
T1.usr_id,
T1.login_date,
T2.login_date AS after_date,
DATEDIFF(T2.login_date, T1.login_date) AS date_gap
FROM login_base T1
LEFT JOIN login_base T2 
ON T1.usr_id = T2.usr_id 
AND T2.login_date >= T1.login_date
),
retention_calculation AS (
SELECT 
login_date,
COUNT(DISTINCT usr_id) AS denominator,
COUNT(DISTINCT CASE 
WHEN date_gap = 1 AND login_date + INTERVAL 1 DAY <= CURRENT_DATE 
THEN usr_id END) AS numerator_1,
COUNT(DISTINCT CASE 
WHEN date_gap = 3 AND login_date + INTERVAL 3 DAY <= CURRENT_DATE 
THEN usr_id END) AS numerator_3,
COUNT(DISTINCT CASE 
WHEN date_gap = 7 AND login_date + INTERVAL 7 DAY <= CURRENT_DATE 
THEN usr_id END) AS numerator_7,
COUNT(DISTINCT CASE 
WHEN date_gap = 14 AND login_date + INTERVAL 14 DAY <= CURRENT_DATE 
THEN usr_id END) AS numerator_14
FROM user_future_logins
GROUP BY login_date
)
SELECT 
login_date AS first_login_date,
ROUND(COALESCE(numerator_1 * 100.0 / NULLIF(denominator, 0), 0), 2) AS t_plus_1_retention_rate,
ROUND(COALESCE(numerator_3 * 100.0 / NULLIF(denominator, 0), 0), 2) AS t_plus_3_retention_rate,
ROUND(COALESCE(numerator_7 * 100.0 / NULLIF(denominator, 0), 0), 2) AS t_plus_7_retention_rate,
ROUND(COALESCE(numerator_14 * 100.0 / NULLIF(denominator, 0), 0), 2) AS t_plus_14_retention_rate
FROM retention_calculation
WHERE login_date >= CURRENT_DATE - 30;

WITH 
90_criteria_list AS (
select usr_id,
DATE(login_time) AS login_date
from user_login_log
where datediff(current_date, date(login_time)) <= 90 ORDER BY login_date
),
after_days AS (
SELECT * FROM (
select T1.*,
T2.login_date AS after_date
from 90_criteria_list AS T1
LEFT JOIN 90_criteria_list AS T2
ON T1.usr_id = T2.usr_id
) AS TEM
WHERE login_date <= after_date
),
full_gaps AS (
select login_date, usr_id, after_date,
DATEDIFF(after_date, login_date) AS date_gap
from after_days
),
preparation AS (
select login_date,
COUNT(distinct usr_id) AS denominator,
COUNT(DISTINCT case when date_gap =1 thenusr_id else NULL END ) AS numerator_1,
COUNT(DISTINCT case when date_gap =3 thenusr_id else NULL END ) AS numerator_3,
COUNT(DISTINCT case when date_gap =7 thenusr_id else NULL END ) AS numerator_7,
COUNT(DISTINCT case when date_gap =14 thenusr_id else NULL END ) AS numerator_14
from full_gaps
GROUP BY login_date
)
select 
login_date AS first_login_date, 
numerator_1 / denominator AS t_plus_1_retention_rate,
numerator_3 / denominator AS t_plus_3_retention_rate,
numerator_7 / denominator AS t_plus_7_retention_rate,
numerator_14 / denominator AS t_plus_14_retention_rate
from preparation;

select * 
from didi_order_rcd 
where DATE(call_time) = '2021-05-03'
AND DATE(cancel_time) <> '1970-01-01'
order by call_time ASC;

select * 
from didi_order_rcd 
where DATE(call_time) = '2021-05-03'
AND cancel_time is not null;

WITH daily_flow AS (
SELECTDISTINCT usr_id,
DATE(login_time) as login_date
FROM user_login_log
WHERE DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 30
),
daily_vs_tomorrow AS (
SELECT 
T_now.usr_id, 
T_now.login_date as now_date, 
T_plus_1day.login_date as now_plus_1_date 
FROM 
daily_flow as T_now 
LEFT JOIN 
daily_flow as T_plus_1day 
ON 
T_now.usr_id = T_plus_1day.usr_id 
AND T_now.login_date + INTERVAL 1 DAY = T_plus_1day.login_date
)
SELECT 
	now_date AS first_login_date,
CONCAT(ROUND(AVG(CASE WHEN now_plus_1_date IS NOT NULL THEN 1.0 ELSE 0.0 END) *100, 2), '%') AS T_plus_1day_retention_rate
FROM daily_vs_tomorrow
GROUP BY now_date
order by now_date ASC;

WITH daily_flow AS (
SELECTDISTINCT usr_id,
DATE(login_time) as login_date
FROM user_login_log
WHERE DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 30
),
daily_vs_tomorrow AS (
SELECT 
T_now.usr_id, 
T_now.login_date as now_date, 
T_plus_1day.login_date as now_plus_1_date 
FROM 
daily_flow as T_now 
LEFT JOIN 
daily_flow as T_plus_1day 
ON 
T_now.usr_id = T_plus_1day.usr_id 
AND T_now.login_date + INTERVAL 1 DAY = T_plus_1day.login_date
)
SELECT 
	now_date AS first_login_date,
CONCAT(ROUND(AVG(now_plus_1_date is not null) *100, 2), '%') AS T_plus_1day_retention_rate
FROM daily_vs_tomorrow
GROUP BY now_date
order by now_date ASC;

WITH daily_flow AS (
SELECT usr_id,
DATE(login_time) as login_date
FROM user_login_log
WHERE DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 30
),
daily_vs_tomorrow AS (
SELECT 
T_now.usr_id, 
T_now.login_date as now_date, 
T_plus_1day.login_date as now_plus_1_date 
FROM 
daily_flow as T_now 
LEFT JOIN 
daily_flow as T_plus_1day 
ON 
T_now.usr_id = T_plus_1day.usr_id 
AND T_now.login_date + INTERVAL 1 DAY = T_plus_1day.login_date
)
SELECT 
	now_date AS first_login_date,
CONCAT(ROUND(AVG(now_plus_1_date is not null) *100, 2), '%') AS T_plus_1day_retention_rate
FROM daily_vs_tomorrow
GROUP BY now_date
order by now_date ASC;