OneDataAnalystMinimum, Average and Maximum of Ratio by Derivatives
Updated 2022-09-12Copy 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
31
32
33
34
35
36
›
⌄
WITH ETH as (
SELECT Date_Trunc('day',hour) as dt1, avg(price) as ETH
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
GROUP by 1),
stETH as (
SELECT Date_Trunc('day',hour) as dt2, avg(price) as stETH
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address = lower('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84')
GROUP by 1),
rETH as (
SELECT Date_Trunc('day',hour) as dt3, avg(price) as rETH
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address = lower('0xae78736Cd615f374D3085123A210448E74Fc6393')
GROUP by 1),
aETH as (
SELECT Date_Trunc('day',hour) as dt4, avg(price) as aETH
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address = lower('0xE95A203B1a91a908F9B9CE46459d101078c2c3cb')
GROUP by 1),
t1 AS (
SELECT dt1 AS Date, ETH, stETH, rETH, aETH,
stETH/ETH AS st, rETH/ETH AS r, aETH/ETH AS a
FROM ETH
LEFT JOIN stETH ON ETH.dt1 = stETH.dt2
LEFT JOIN rETH ON ETH.dt1 = rETH.dt3
LEFT JOIN aETH ON ETH.dt1 = aETH.dt4 ),
t2 AS(
SELECT 'stETH' AS Derivative,MIN(st) AS MIN_Ratio,AVG(st) AS AVG_Ratio, MAX(st) AS MAX_Ratio
Run a query to Download Data