KuramaSommelier - Eth vs btc vs ethbtc pool
Updated 2022-11-10
999
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
›
⌄
⌄
-- we create a first table which will contain the amount inputed in USD
with table_aux as (select 5000 as amount_usd_inputed from dual),
-- Aux table with the average price in ETH when entering
table_aux_2 as (select avg(price) as average_price_entrance_eth from ethereum.core.fact_hourly_token_prices
where lower(token_address) = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
and to_date(hour) = '2022-10-27'),
-- Aux table with the average price in BTC when entering
table_aux_3 as (select avg(price) as average_price_entrance_btc from ethereum.core.fact_hourly_token_prices
where lower(token_address) = lower('0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599')
and to_date(hour) = '2022-10-27'),
/*
-- Aux table with the average price in Eth-Btc-Momentum in UniswapV3 pool when entering
table_aux_4_1 as (select avg(price_1_0) as average_price_entrance_ethbtc from ethereum.uniswapv3.ez_swaps
where pool_address = '0xffd96cda41d3b28f0b58ce5dcb02bc51224c1331'
and to_date(block_timestamp) = '2022-10-27'),
*/
-- Aux table with the average price in Eth-Btc-Momentum in UniswapV3 pool when entering
table_aux_4_1 as (
select a.hour as date, -- avg(price_swap) as average_price_ethbtc
lag(avg(price_1_0)) ignore nulls over (order by a.hour asc) as average_price_ethbtc
from (select distinct hour as hour from ethereum.core.fact_hourly_token_prices where to_date(hour) >= '2022-10-24') a
left join ethereum.uniswapv3.ez_swaps b
on a.hour = date_trunc('hour',b.block_timestamp)
where pool_address = '0xffd96cda41d3b28f0b58ce5dcb02bc51224c1331'
group by a.hour ),
table_aux_4 as (select avg(average_price_ethbtc) as average_price_entrance_ethbtc from table_aux_4_1
where date_trunc('day',date) = '2022-10-27'),
-----------------------------------------------------------------------------
-- The following part is dedicated only to calculate ETH-BTC-Momentum price on the cellar
Run a query to Download Data