rakhisanjayaUntitled Query
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH data AS (
SELECT
date_trunc('day', block_timestamp) as date1,
sum(amount_in_usd) as Daily_Volume,
avg(price) as WETH_Price,
lag(avg(price)) over (order by date_trunc('day', block_timestamp)) as prev_price
FROM ethereum.core.ez_dex_swaps
LEFT OUTER JOIN ethereum.core.fact_hourly_token_prices
ON date_trunc('hour', block_timestamp) = hour
where symbol like 'WETH'
AND ( symbol_in like 'WETH'
OR symbol_out like 'WETH' )
GROUP BY 1
)
SELECT
date1,
Daily_Volume,
WETH_Price,
SUM(CASE WHEN WETH_Price > prev_price THEN Daily_Volume ELSE 0 END) OVER (ORDER BY date1) -
SUM(CASE WHEN WETH_Price < prev_price THEN Daily_Volume ELSE 0 END) OVER (ORDER BY date1) AS OBV
FROM data
WHERE date1 > current_date - 90
Run a query to Download Data