ramishoowOvertime Markets - Profitable Traders
Updated 2022-08-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
›
⌄
--(Top 5 games with profits)
with tab1 as (select game_contract, game_name, sport from (values ('0x43f2e3efae7aa5f14a9ae4242d66e54e3e9ad5ea','Philadelphia Phillies vs Miami Marlins','Baseball'),
('0x540cbd2e5653ee49ed9b3c77567043c71e316391','Philadelphia Phillies vs Miami Marlins','Baseball'),('0xf41a1ee8b4c1d7fdc855c2ec54d6404c133b9a97','Cincinnati Reds vs Chicago Cubs','Baseball'),
('0xb3c805d1cd4005077af407ff4fc295fcccf9687c','Cincinnati Reds vs Chicago Cubs','Baseball'), ('0xe72d64a6fbdb810980463a8d0aec0b055bce5302','Cincinnati Reds vs Chicago Cubs','Baseball'),
('0x235185266523fc58aba588ac869388a26bdb177f','Salernitana vs AS Roma','Soccer'), ('0xd058811b0e33b8119e1076535854f51158549c43','Cádiz vs Real Sociedad','Soccer'),
('0xbcfbe3c3d05e56ba74c521681bd1555f0dcff091','Seattle Mariners vs New York Yankees','Baseball'), ('0xc8050327fea017839e4584b7e416412d71f20a35','Seattle Mariners vs New York Yankees','Baseball') )
as tb (game_contract, game_name, sport)), user_claim as (SELECT event_inputs:account::string as wallet, game_name, sport, event_inputs:value/1e18 as value
from optimism.core.fact_event_logs,tab1 where event_name is not null and origin_function_signature = '0x85149258' and BLOCK_TIMESTAMP > '2022-02-01' and contract_address = game_contract and event_inputs:value > 0),
tab2 as (select tx_hash, game_name, sport from optimism.core.fact_token_transfers join tab1 where to_address = game_contract and contract_address ilike '0x8c6f28f2F1A3C87F0f938b96d27520d9751ec8d9'
and raw_amount is not null and BLOCK_TIMESTAMP > '2022-02-01' ),
tab3 as ( select origin_from_address as wallet, game_name, sport, raw_amount/pow(10,18) as value from optimism.core.fact_token_transfers, tab2 where to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and from_address != '0x0000000000000000000000000000000000000000' and optimism.core.fact_token_transfers.tx_hash = tab2.tx_hash )
select game_name, sport, sum(value) as profit from ((select wallet, game_name, sport, value from user_claim) union ALL (select wallet, game_name, sport, -(1)* value from tab3))
group by 1,2
order by 3 DESC
--'0x0000000000000000000000000000000000000000'
Run a query to Download Data