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