select hour, avg(BTC) as BTC, avg(ETH) as ETH, avg(MATIC) as MATIC, avg(LUNA) as LUNA, avg(LINK) as LINK, avg(FTT) as FTT, avg(ENJ) as ENJ, avg(AAVE) as AAVE, avg(SNX) as SNX, avg(DYDX) as DYDX, avg(BOND) as BOND, avg(APE) as APE, avg(YFI) as YFI from (

SELECT hour

, case when symbol = 'WBTC' then price else null end as BTC

, case when symbol = 'WETH' then price else null end as ETH

, case when symbol = 'MATIC' then price else null end as MATIC

, case when symbol = 'LUNA' then price else null end as LUNA

, case when symbol = 'LINK' then price else null end as LINK

, case when symbol = 'FTX Token' then price else null end as FTT

, case when symbol = 'ENJ' then price else null end as ENJ

, case when symbol = 'AAVE' then price else null end as AAVE

, case when symbol = 'SNX' then price else null end as SNX

, case when symbol = 'DYDX' then price else null end as DYDX

, case when symbol = 'BOND' then price else null end as BOND

, case when symbol = 'APE' then price else null end as APE

, case when symbol = 'YFI' then price else null end as YFI

FROM (

SELECT hour

, symbol

, price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol IN ('WBTC', 'WETH', 'MATIC', 'LUNA', 'LINK', 'FTX Token', 'ENJ', 'AAVE', 'SNX', 'DYDX', 'BOND', 'APE', 'YFI')

AND hour>='2021-12-16 00:00'

) as p

) as pp GROUP BY 1

),

quantites as (

SELECT hour,

case when hour < '2022-04-07 08:00' then 0.00042

when hour < '2022-07-09 09:00' then 0.000492

else 0.000371 end as btcQuantity,

case when hour < '2022-04-07 08:00' then 0.005214

when hour < '2022-07-09 09:00' then 0.006613

else 0.006613 end as ethQuantity,

case when hour < '2022-07-09 09:00' then 0

else 8.928914 end as maticQuantity,