Untitled

select distinct segment_level from (

SELECT * FROM

(select event_slot,calendar_entry_id,LO_EVENT_ID,CASE

when segment like '%_3799' ESCAPE '_' then '1300-3799'

when segment like '%_6999' ESCAPE '_' then '3800-6999'

when segment like '%_999999' ESCAPE '_' then '30000+'

when segment like '%_9999999' ESCAPE '_' then '30000+'

when segment like '%_19999' ESCAPE '_' then '10000-19999'

when segment like '%_29999' ESCAPE '_' then '20000-29999'

when segment like '%_9999' ESCAPE '_'then '7000-9999'

else 'unsegmented by trophies' end segment_level,event_level, count(distinct case when action = 'claim_prize' then user_id end) users_claimed, count(distinct case when action = 'points_earned' then user_id end) users_passed from CANDIVORE.PROD.F_LIVE_EVENT_PROGRESSION

where (event_slot = 'Team' or event_slot = 'Main') and date(derived_tstamp) > DATEADD(DAY,-11,GETDATE()) group by 1,2,3,4,5) A

 

 

left join

 

((SELECT LO_ENTRY_ID , to_char(to_timestamp(lo_entry_start_ts), 'YYYY-MM-DD') start_date FROM (SELECT * FROM (select *,CASE when lo_entry_ts is null then lo_entry_update_ts else lo_entry_ts end lo_entry_ts2,row_number() OVER (partition by lo_entry_id order by lo_entry_ts2 desc) rn FROM CANDIVORE.PROD.F_LIVEOPS_CALENDAR) where rn = 1)))

C

ON A.calendar_entry_id = C.lo_entry_id

) where start_date = '2023-07-23'