31 rows yesterday at 3:43 PM
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
with
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' as usdc_weth_03_pool_address,
18 as weth_decimals,
6 as usdc_decimals,
prices as (
select
timestamp,
1 / (pow (1.0001, input_6_value_int32) / pow (10, weth_decimals - usdc_decimals)) as price
from
evm_events_ethereum_mainnet_v1
where
address = usdc_weth_03_pool_address
and signature = 'Swap(address,address,int256,int256,uint160,uint128,int24)'
and timestamp >= now() - interval 1 month
order by timestamp asc
)
select
date_trunc('day', timestamp) as chart_x,
argMin(price, timestamp) as chart_open,
argMax(price, timestamp) as chart_close,
min(price) as chart_min,
max(price) as chart_max
from
prices
group by chart_x
order by chart_x asc