negin-khTop 10 games that received the most activity in terms of total $ volume
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 users as (select DISTINCT ORIGIN_FROM_ADDRESS as "Unique Users"
from optimism.core.fact_token_transfers
where ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and BLOCK_TIMESTAMP > CURRENT_DATE - 14),
tx as(select tx_hash
from optimism.core.fact_token_transfers
where ORIGIN_FROM_ADDRESS in ( select "Unique Users" from users )
and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and FROM_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
and TO_ADDRESS in ( select "Unique Users" from users )
and BLOCK_TIMESTAMP > CURRENT_DATE - 14
and contract_address != '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' --sUSD contract
and BLOCK_TIMESTAMP > CURRENT_DATE - 14),
susd as (select sum(TX_JSON:receipt:logs[0]:decoded:inputs:value/ pow(10,18))as value, tx_json:receipt:logs[1]:address as game,
CASE
when TX_JSON:receipt:logs[0]:address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' then 'susd'
else null
end as currency
from optimism.core.fact_transactions
where tx_hash in (SELECT tx_hash from tx)
and TX_JSON:receipt:logs[0]:address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'
and BLOCK_TIMESTAMP > CURRENT_DATE - 14
group by currency, game
order by value desc),
usdc as (select sum(TX_JSON:receipt:logs[0]:decoded:inputs:value/ pow(10,6))as value, tx_json:receipt:logs[10]:address as game,
CASE
when TX_JSON:receipt:logs[0]:address = '0x7f5c764cbc14f9669b88837ca1490cca17c31607' then 'usdc'
else null
end as currency
from optimism.core.fact_transactions
where tx_hash in (SELECT tx_hash from tx)
and TX_JSON:receipt:logs[0]:address = '0x7f5c764cbc14f9669b88837ca1490cca17c31607'
and BLOCK_TIMESTAMP > CURRENT_DATE - 14
group by currency, game
Run a query to Download Data