abulashour
5/2/2020 - 7:31 AM

Most Engaged Users (based on # of sessions)

with data as (
select fieldMobileIDLast
, user_Id
, fieldEventTimestampLast
, user_last_seen_date
, fieldEmailLast
, totaldaysplayed
, fielduserlevellast
, count(distinct(e.eventdate)) as numdays
, count(distinct(sessionid)) as sessions
, row_number() over (partition by fieldMobileIDLast order by fieldEventTimestampLast desc) as rn
from user_metrics_live as u
inner join events_live as e
on u.user_id = e.userid 
where fielduserlevellast > 4 and user_last_seen_date>= current_date - 7
and fieldEmailLast not like ''
group by 1,2,3,4,5,6,7
)
select
fieldEmailLast from data 
where rn = 1 and fielduserlevellast > 4
and user_last_seen_date>= current_date - 7
and fieldEmailLast not like ''
order by numdays desc, sessions desc