Alir3zaUntitled Query
Updated 2022-08-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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') ),