abulashour
5/2/2020 - 1:42 PM

Churned Users Analysis

Selecting avg days stayed in game, chipsbalance, num_matches, % of churned users as spenders, churned spenders last purchase

### General
select user_id, user_first_seen_date as first_seen,
user_last_seen_date as last_seen, fieldchipsbalancelast, fielduserlevellast,
eventmatchstartedcount, sum(eventmatchlostcount + eventmatchwoncount) as matches_completed from user_metrics_live
where user_last_seen_date < current_date - 7
group by 1,2,3,4,5,6
order by user_id

### Number of churned spenders and last product purchased
select userid, eventtimestamp, productid, gauserstartdate, lastseen  from(
select userid, eventTimestamp, productid, gauserstartdate, realcurrencyamount, user_last_seen_date as lastseen,
ROW_NUMBER() OVER (PARTITION BY userID ORDER BY eventTimestamp DESC) AS rn
    FROM events_live
    inner join user_metrics_live
    on events_live.userid = user_metrics_live.user_id
    where realcurrencyamount > 0 and user_last_seen_date < current_date - 7
) as tmp
where rn = 1

### Number of churned non-spenders
select userid, eventtimestamp, gauserstartdate, lastseen  from(
select userid, eventTimestamp, gauserstartdate, user_last_seen_date as lastseen,
ROW_NUMBER() OVER (PARTITION BY userID ORDER BY eventTimestamp DESC) AS rn
    FROM events_live
    inner join user_metrics_live
    on events_live.userid = user_metrics_live.user_id
    where user_last_seen_date < current_date - 7 and (realcurrencyamount > 0 or realcurrencyamount is null)
) as tmp
where rn = 1

### Last match
select userid, stamp, outcome, lastseen, gauserstartdate
from(
select userid, eventTimestamp as stamp, eventname as outcome, gauserstartdate, user_last_seen_date as lastseen,
ROW_NUMBER() OVER (PARTITION BY userID ORDER BY eventTimestamp DESC) AS rn
    FROM events_live
    inner join user_metrics_live
    on events_live.userid = user_metrics_live.user_id
    where (eventname = 'matchWon' or eventname = 'matchLost') and eventlevel = 1
    and user_last_seen_date < current_date - 7
) as tmp 
where rn = 1
order by userid desc