shengyi-chiuUntitled Query
Updated 2022-10-05
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
›
⌄
--- all credit to naranjaninja , https://app.flipsidecrypto.com/velocity/queries/5107c468-5a0a-4179-a19d-16292df14191
with op_price_data as (
select date_trunc('day', hour) as date,
avg(price) as op_price
from optimism.core.fact_hourly_token_prices
where token_address = '0x4200000000000000000000000000000000000042'
group by 1
),
sushiswap_data as (
select date_trunc('day',block_timestamp) as date,
'Sushiswap' as exchange,
count(distinct tx_hash) as swaps,
count(distinct origin_from_address) as users_swapping,
sum(amount_in_usd) as volume_usd
from optimism.sushi.ez_swaps
where block_timestamp >= '2022-05-31' -- first OP price data date available
and token_in = '0x4200000000000000000000000000000000000042' or token_out = '0x4200000000000000000000000000000000000042'
group by 1
),
uniswap_data as (
select date_trunc('day',a.block_timestamp) as date,
'Uniswap' as exchange,
count(distinct a.tx_hash) as swaps,
count(distinct a.origin_from_address) as users_swapping,
sum((c.price * b.raw_amount)/pow(10, c.decimals)) as volume_usd
from optimism.core.fact_event_logs a
join optimism.core.fact_token_transfers b using(tx_hash)
join optimism.core.fact_hourly_token_prices c on c.token_address = b.contract_address and date_trunc('hour', b.block_timestamp) = c.hour
where a.event_name = 'Swap'
and a.origin_to_address in ('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','0xe592427a0aece92de3edee1f18e0157c05861564')
and a.tx_status ='SUCCESS'
and a.block_timestamp >= '2022-05-31' -- first OP price data date available
and token_address = '0x4200000000000000000000000000000000000042'
group by 1