HadisehOvertime Markets - Volume 1
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 t1 as (select DISTINCT ORIGIN_FROM_ADDRESS as unique_users
from optimism.core.fact_token_transfers
where BLOCK_TIMESTAMP::date > '2022-08-10'
and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
),
t2 as(
select tx_hash
from optimism.core.fact_token_transfers
where FROM_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and contract_address != '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
and ORIGIN_FROM_ADDRESS in ( select unique_users from t1 )
and TO_ADDRESS in ( select unique_users from t1 )
and BLOCK_TIMESTAMP::date > '2022-08-10'),
t3 as (
select sum(TX_JSON:receipt:logs[0]:decoded:inputs:value/ pow(10,18))as value, tx_json:receipt:logs[1]:address as game_name,
CASE
when TX_JSON:receipt:logs[0]:address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
then 'susd' else null
end as currency
from optimism.core.fact_transactions
where TX_JSON:receipt:logs[0]:address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
and tx_hash in (SELECT tx_hash from t2)
and BLOCK_TIMESTAMP::date > '2022-08-10'
group by currency, game_name order by value desc),
t4 as (
select sum(TX_JSON:receipt:logs[0]:decoded:inputs:value/ pow(10,6))as value,
tx_json:receipt:logs[10]:address as game_name,
CASE when TX_JSON:receipt:logs[0]:address = '0x7f5c764cbc14f9669b88837ca1490cca17c31607'
then 'usdc' else null
end as currency
from optimism.core.fact_transactions
where BLOCK_TIMESTAMP::date > '2022-08-10'
Run a query to Download Data