sebateau22-AHBollinger Band
Updated 2024-10-13
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
›
⌄
WITH rune_prices AS (
-- Fetch daily average RUNE prices
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS period,
AVG(RUNE_USD) AS avg_rune_price
FROM thorchain.price.fact_prices
WHERE POOL_NAME = 'BTC.BTC'
AND DATE_TRUNC('day', BLOCK_TIMESTAMP) >= '2023-01-01'
GROUP BY period
),
-- Calculate the 20-day moving average and standard deviation
bollinger_bands AS (
SELECT
period,
avg_rune_price,
AVG(avg_rune_price) OVER (ORDER BY period ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS moving_avg,
STDDEV(avg_rune_price) OVER (ORDER BY period ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS stddev
FROM rune_prices
)
-- Final selection of Bollinger Bands
SELECT
period,
avg_rune_price,
moving_avg,
(moving_avg + 2 * stddev) AS upper_band,
(moving_avg - 2 * stddev) AS lower_band
FROM bollinger_bands
ORDER BY period;
QueryRunArchived: QueryRun has been archived