Cacher is the code snippet organizer for pro developers

select userid, eventdate, userlevel, isvip, vipexpiresat from 
(select a2.userid as userid, a2.eventdate as eventdate, a1.userlevel as userlevel, a1.isvip as isvip, a1.vipexpiresat as vipexpiresat,
ROW_NUMBER() OVER (PARTITION BY a1.userID ORDER BY a1.eventDate DESC) AS rn
from events_live as a1
inner join events_live as a2
on a1.userid = a2.userid 
where a2.action = 'levelUp' and a2.itemname = 'vip' and datediff(day, a2.eventdate, to_timestamp(a1.vipexpiresat, 'YYYY-MM-DD')) = 7
and a1.eventdate >= '2020-01-01' and a1.eventname = 'gameStarted' and a1.vipexpiresat is not null
group by 1,2,3,4,5, a1.eventdate, a1.userid) as tmp where rn = 1
group by 1,2,3,4,5