Untitled

CREATE OR REPLACE TABLE candivore.semantic_layer.T_MUTATIONS AS

SELECT A.calendar_entry_id, A.lo_event_id,A.interval_date, A.picks, A.booster_name, A.resource_sub_type , A.matches_picked , A.matches_won , A.winrate , B.matches_shown FROM

 

(SELECT interval_date,calendar_entry_id, lo_event_id, picks, resource_sub_type,booster_name, COUNT(DISTINCT match_id) matches_picked, COUNT(DISTINCT CASE WHEN is_won = 1 THEN match_id ELSE NULL END) matches_won, matches_won/matches_picked winrate FROM

(SELECT DATE(derived_tstamp) interval_date,calendar_entry_id, is_won, match_id,resource_sub_type,booster_name,lo_event_id

,to_varchar(mutations_pick[0]) pick0, to_varchar(mutations_pick[1]) pick1, to_varchar(mutations_pick[2]) pick2, to_varchar(mutations_pick[3]) pick3, to_varchar(mutations_pick[4]) pick4

FROM candivore.prod.f_user_match WHERE lo_event_id LIKE '%mutation%' AND DATE(derived_tstamp) >= DATEADD(DAY,-181,GETDATE())) UNPIVOT(picks FOR pick_nm IN (pick0, pick1, pick2, pick3, pick4)) GROUP BY 1,2,3,4,5,6) A

 

LEFT JOIN

 

(SELECT interval_date,calendar_entry_id, lo_event_id, picks, resource_sub_type,booster_name, COUNT(DISTINCT match_id) matches_shown FROM

(SELECT DATE(derived_tstamp) interval_date,calendar_entry_id, lo_event_id, is_won, match_id,resource_sub_type,booster_name

,to_varchar(mutation_options[0][0]) option00,to_varchar(mutation_options[0][1]) option01,to_varchar(mutation_options[0][2]) option02,to_varchar(mutation_options[1][0]) option10,to_varchar(mutation_options[1][1]) option11,to_varchar(mutation_options[1][2]) option12,to_varchar(mutation_options[2][0]) option20,to_varchar(mutation_options[2][1]) option21,to_varchar(mutation_options[2][2]) option22,to_varchar(mutation_options[3][0]) option30,to_varchar(mutation_options[3][1]) option31,to_varchar(mutation_options[3][2]) option32,to_varchar(mutation_options[4][0]) option40,to_varchar(mutation_options[4][1]) option41,to_varchar(mutation_options[4][2]) option42

FROM candivore.prod.f_user_match WHERE lo_event_id LIKE '%mutation%' AND DATE(derived_tstamp) >= DATEADD(DAY,-181,GETDATE())) UNPIVOT(picks FOR pick_nm IN (option00, option01, option02, option10, option11, option12, option20, option21, option22, option30, option31, option32, option40, option41, option42)) GROUP BY 1,2,3,4,5,6

) B ON A.calendar_entry_id = B.calendar_entry_id AND A.picks = B.picks AND A.booster_name = B.booster_name