CryptoLionGrant V3 Price Predictions - BTC
Updated 2022-11-27
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
›
⌄
WITH dp as (
SELECT
date_trunc('day',hour) as day,
avg(price) as price_avg,
token_address,
symbol
FROM ethereum.token_prices_hourly
WHERE hour >= getdate() - interval '14 days'
GROUP BY day, token_address, symbol
ORDER BY day
),
t1 as (
SELECT
dp.day as day,
-- avg(price_lower_0_1_usd) as low0,
-- avg(price_upper_0_1_usd) as up0,
COUNT(CASE WHEN (price_upper_0_1_usd - price_avg)/price_avg*100 < 5 THEN 1 END) as under_5_percent,
COUNT(CASE WHEN (price_upper_0_1_usd - price_avg)/price_avg*100 <= 30 THEN 1 END) - under_5_percent as under_30_percent,
COUNT(CASE WHEN (price_upper_0_1_usd - price_avg)/price_avg*100 > 50 THEN 1 END) as over_50_percent,
COUNT(CASE WHEN (abs(price_lower_0_1_usd - price_avg))/price_avg*100 < 5 THEN 1 END) as under_5_percent_low,
COUNT(CASE WHEN (abs(price_lower_0_1_usd - price_avg))/price_avg*100 <= 30 THEN 1 END) - under_5_percent_low as under_30_percent_low,
COUNT(CASE WHEN (abs(price_lower_0_1_usd - price_avg))/price_avg*100 > 50 THEN 1 END) as over_50_percent_low,
-- avg(price_lower_1_0_usd) as low1,
-- avg(price_upper_1_0_usd) as up1,
count(liquidity_provider) as lp,
token1_address
FROM uniswapv3.positions
INNER JOIN dp on dp.day = date_trunc('day',block_timestamp) AND token_address = token1_address
WHERE block_timestamp >= getdate() - interval '14 days'
AND token1_symbol = 'WBTC'
AND price_upper_0_1_usd > price_avg
AND price_lower_0_1_usd < price_avg
GROUP BY day, token1_address
ORDER BY day
),
t0 as (
SELECT
dp.day as day,-- date_trunc('day',block_timestamp) as day,
avg(price_lower_1_0_usd) as low1,
avg(price_upper_1_0_usd) as up1,
COUNT(CASE WHEN (price_upper_1_0_usd - price_avg)/price_avg*100 < 5 THEN 1 END) as under_5_percent,
COUNT(CASE WHEN (price_upper_1_0_usd - price_avg)/price_avg*100 <= 30 THEN 1 END) - under_5_percent as under_30_percent,
COUNT(CASE WHEN (price_upper_1_0_usd - price_avg)/price_avg*100 > 50 THEN 1 END) as over_50_percent,
COUNT(CASE WHEN (abs(price_lower_1_0_usd - price_avg))/price_avg*100 < 5 THEN 1 END) as under_5_percent_low,
COUNT(CASE WHEN (abs(price_lower_1_0_usd - price_avg))/price_avg*100 <= 30 THEN 1 END) - under_5_percent_low as under_30_percent_low,
COUNT(CASE WHEN (abs(price_lower_1_0_usd - price_avg))/price_avg*100 > 50 THEN 1 END) as over_50_percent_low,
count(liquidity_provider) as lp,
token0_address
FROM uniswapv3.positions
INNER JOIN dp on dp.day = date_trunc('day',block_timestamp) AND token_address = token0_address
WHERE block_timestamp >= getdate() - interval '14 days'
AND token0_symbol = 'WBTC'
AND price_upper_1_0_usd > price_avg
AND price_lower_1_0_usd < price_avg
GROUP BY day, token0_address
ORDER BY day
)
SELECT
dp.day, dp.symbol,
price_avg,
t1.under_5_percent+t1.under_30_percent+t1.over_50_percent+t0.under_5_percent+t0.under_30_percent+t0.over_50_percent as sum_u,
t1.under_5_percent_low+t1.under_30_percent_low+t1.over_50_percent_low+t0.under_5_percent_low+t0.under_30_percent_low+t0.over_50_percent_low as sum_l,
t1.under_5_percent + t0.under_5_percent as under_5_percent,
t1.under_30_percent + t0.under_30_percent as under_30_percent,
t1.over_50_percent+t0.over_50_percent as over_50_percent,
t1.under_5_percent_low+t0.under_5_percent as under_5_percent_low,
t1.under_30_percent_low+t0.under_30_percent_low as under_30_percent_low,
t1.over_50_percent_low+t0.over_50_percent_low as over_50_percent_low,
price_avg*1.05 as u10,
price_avg*1.3 as u20,
price_avg*1.5 as u30,
price_avg*0.95 as l10,
price_avg*0.7 as l20,
price_avg*0.5 as l30,
t0.lp+t1.lp as lp
FROM dp
INNER JOIN t0 on dp.day = t0.day
INNER JOIN t1 on dp.day = t1.day
-- INNER JOIN t0 on dp.day = t0.day
WHERE dp.token_address = t0.token0_address
-- AND dp.token_address = t0.token1_address
ORDER BY dp.day