Cacher is the code snippet organizer for pro developers

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