mhmTop 20 games received the most activity based on total bids volume (USD)
Updated 2022-08-26Copy Reference Fork
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 games_info as (
SELECT
concat('0x',substr(data,27,40)) as game_contract,
TRIM(HEX_DECODE_STRING(substr(data,3+13*64,64)), '\u0000') as home_team,
TRIM(HEX_DECODE_STRING( substr(data,3+15*64,64)), '\u0000') as away_team,
case when tokenflow_eth.hextoint(substr(data,3+17*64,64)) like '90%' then tokenflow_eth.hextoint(substr(data,3+17*64,64))
when tokenflow_eth.hextoint(substr(data,3+18*64,64)) like '90%' then tokenflow_eth.hextoint(substr(data,3+18*64,64))
when tokenflow_eth.hextoint(substr(data,3+19*64,64)) like '90%' then tokenflow_eth.hextoint(substr(data,3+19*64,64))
when tokenflow_eth.hextoint(substr(data,3+20*64,64)) like '90%' then tokenflow_eth.hextoint(substr(data,3+20*64,64))
end as game_code,
case when game_code = '9002' then 'Football'
when game_code = '9003' then 'Baseball'
when game_code = '9007' then 'MMA'
when game_code = '9010' then 'Soccer'
when game_code = '9011' then 'Soccer'
when game_code = '9012' then 'Soccer'
when game_code = '9013' then 'Soccer'
when game_code = '9014' then 'Soccer'
when game_code = '9015' then 'Soccer'
end as sport
from
optimism.core.fact_event_logs
where
ORIGIN_FUNCTION_SIGNATURE = '0xac2c957c'
and topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'
and contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'
),
pays as (
select symbol as token, event_inputs:value/pow(10,decimals) as amount, *
from optimism.core.fact_event_logs l left join optimism.core.dim_contracts c on l.contract_address = c.address
where (origin_function_signature = '0x8875eb84' or origin_function_signature = '0x6cc5a6ff')
and origin_to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and event_inputs:from = origin_from_address
and event_inputs:to = origin_to_address
Run a query to Download Data