create or replace table candivore.semantic_layer.T_MUTATIONS as
select A.calendar_entry_id,A.interval_date, A.lo_event_id, 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, to_varchar(mutations_pick[5]) pick5,to_varchar(mutations_pick[6]) pick6,to_varchar(mutations_pick[7]) pick7,to_varchar(mutations_pick[8]) pick8,to_varchar(mutations_pick[9]) pick9
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,pick5,pick6,pick7,pick8,pick9)) 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,to_varchar(mutation_options[5][0]) option50,to_varchar(mutation_options[5][1]) option51,to_varchar(mutation_options[5][2]) option52,to_varchar(mutation_options[6][0]) option60,to_varchar(mutation_options[6][1]) option61,to_varchar(mutation_options[6][2]) option62,to_varchar(mutation_options[7][0]) option70,to_varchar(mutation_options[7][1]) option71,to_varchar(mutation_options[7][2]) option72,to_varchar(mutation_options[8][0]) option80,to_varchar(mutation_options[8][1]) option81,to_varchar(mutation_options[8][2]) option82,to_varchar(mutation_options[9][0]) option90,to_varchar(mutation_options[9][1]) option91,to_varchar(mutation_options[9][2]) option92
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, option50, option51, option52)) 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 and A.interval_date = B.interval_date and A.lo_event_id = B.lo_event_id and A.resource_sub_type = B.resource_sub_type