rezarwzOP volume
Updated 2022-10-06Copy 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
›
⌄
with trade as(
SELECT
block_timestamp::Date as date,
ORIGIN_FROM_ADDRESS,
tx_hash,
case
WHEN EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' then EVENT_INPUTS:amountOut/pow(10,(SELECT DECIMALS from optimism.core.dim_contracts where symbol='OP'))
when EVENT_INPUTS:tokenIn='0x4200000000000000000000000000000000000042' then EVENT_INPUTS:amountIn/pow(10,(SELECT DECIMALS from optimism.core.dim_contracts where symbol='OP'))
end as amount
from optimism.core.fact_event_logs
WHERE event_name='Swap' and amount is not null and ORIGIN_FUNCTION_SIGNATURE!='0x'),
average_price as (
SELECT
date(hour) as date,
avg(price) as price
FROM optimism.core.fact_hourly_token_prices
WHERE SYMBOL='OP'
GROUP by 1
)
SELECT
trade.date,
price,
sum(amount) as daily_volume,
sum(daily_volume) over (order by trade.date) as total_volume,
COUNT (DISTINCT ORIGIN_FROM_ADDRESS) as daily_traders,
sum(daily_traders) over (order by trade.date) as total_traders,
COUNT (DISTINCT tx_hash) as daily_trades,
sum(daily_trades) over (order by trade.date) as total_trades
FROM trade inner join average_price a on a.date=trade.date
GROUP BY trade.date,2
Run a query to Download Data