Alir3zaUntitled Query
    Updated 2022-08-25
    with Over1 as ( SELECT concat('0x',substr(data,27,40)) as game_contract , TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+12*64,64) )as INTEGER) as n
    , TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+14*64,64) )as INTEGER) as n2 , TRIM (HEX_DECODE_STRING( substr(data,3+13*64,64) ) ) as home_team
    , TRIM(HEX_DECODE_STRING( substr(data,3+15*64,64) )) as away_team ,ethereum.public.udf_hex_to_int(substr(data,3+17*64,64)) as n3
    from optimism.core.fact_event_logs where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f' AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval'2 week'),
    Over2 as ( SELECT concat('0x',substr(data,27,40)) as game_contract, TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+12*64,64) )as INTEGER) as n
    , TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+14*64,64) )as INTEGER) as n2 ,TRIM (HEX_DECODE_STRING( substr(data,3+13*64,64) )) as home_team ,
    TRIM(HEX_DECODE_STRING( substr(data,3+15*64,64) )) as away_team ,ethereum.public.udf_hex_to_int( substr(data,3+18*64,64)) as n3
    from optimism.core.fact_event_logs
    where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval'2 week'),
    Over3 as (SELECT concat('0x',substr(data,27,40)) as game_contract, TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+12*64,64) )as INTEGER) as n , TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+14*64,64) )as INTEGER) as n2
    , TRIM(HEX_DECODE_STRING( substr(data,3+13*64,64) )) as home_team , TRIM(HEX_DECODE_STRING( substr(data,3+15*64,64) )) as away_team ,ethereum.public.udf_hex_to_int( substr(data,3+18*64,64)) as n3
    from optimism.core.fact_event_logs
    where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval'2 week'),
    over4 as ( SELECT concat('0x',substr(data,27,40)) as game_contract, TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+12*64,64) )as INTEGER) as n
    , TRY_CAST(ethereum.public.udf_hex_to_int( substr(data,3+15*64,64) )as INTEGER) as n2 ,TRIM( HEX_DECODE_STRING( substr(data,3+13*64,64) )) as home_team
    ,TRIM (HEX_DECODE_STRING( substr(data,3+16*64,64) )) as away_team ,ethereum.public.udf_hex_to_int( substr(data,3+19*64,64)) as n3
    from optimism.core.fact_event_logs
    where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'AND BLOCK_TIMESTAMP >= CURRENT_DATE -14),
    Alls as ( select * from Over1 union all select * from over4 union all select * from Over3 union all select * from Over2 ),
    Table1 AS ( select GAME_CONTRACT,HOME_TEAM,AWAY_TEAM, N3 as "LEAGUE ID", IFF (N3 = '9002','American football', IFF (N3 = '9003','BASEBALL',IFF (N3 = '9007','MMA','SOCCER'))) as SPORT,case
    when N3 = '9002' then 'NFL'
    when N3 = '9003' then 'MLB'
    when N3 = '9007' then 'MMA'
    when N3 = '9010' then 'MLS'
    when N3 = '9011' then 'EPL'
    when N3 = '9012' then 'Ligue 1'
    when N3 = '9013' then 'Bundesliga'
    when N3 = '9014' then 'La Liga'
    when N3 = '9015' then 'Serie A' end as LEAGUE from Alls WHERE N3 IN ('9002','9003','9007','9010','9011','9012','9013','9014','9015') ),