Users who are currently VIP because of a level up gift
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