Player retention

retention.sql

WITH login_log AS
(
    SELECT ckey, TIMESTAMPDIFF(MONTH, '2018-01-01', DATETIME) AS login_month #Change the timestamp to the start of your first year
    FROM ss13_connection_log #table housing the connection data
    GROUP BY 1,2
    ORDER BY 1,2
),
time_lapse AS
(
SELECT ckey, login_month, LAG(login_month, 1) over (PARTITION BY ckey ORDER BY ckey, login_month) AS Lag
FROM login_log
),
time_diff_calc AS
(
SELECT ckey, login_month, Lag, login_month - Lag AS time_diff
FROM time_lapse
),
player_categorized AS
(
SELECT  ckey,
        login_month,
        CASE 
            WHEN time_diff = 1 THEN 'retained'
            WHEN time_diff > 1 THEN 'returning'
            WHEN time_diff IS NULL then 'new'
        END AS player_type
FROM time_diff_calc
)
SELECT login_month, player_type, COUNT(ckey)
FROM player_categorized
GROUP BY 1, 2