sebateau22-AHStdDev_Rune_BTC
Updated 2024-12-29
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
-- PRELIMINARY TABLES TO COMPUTE AVERAGE PRICE PER PERIOD --
btc_prices AS (
SELECT
DATE_TRUNC('week', BLOCK_TIMESTAMP) AS period,
AVG(ASSET_USD) AS avg_btc_price
FROM thorchain.price.fact_prices
WHERE POOL_NAME = 'BTC.BTC'
AND DATE_TRUNC('week', BLOCK_TIMESTAMP) >= '2021-04-01'
GROUP BY period
),
rune_prices AS (
SELECT
DATE_TRUNC('week', BLOCK_TIMESTAMP) AS period,
AVG(RUNE_USD) AS avg_rune_price
FROM thorchain.price.fact_prices
WHERE POOL_NAME = 'BTC.BTC'
AND DATE_TRUNC('week', BLOCK_TIMESTAMP) >= '2021-04-01'
GROUP BY period
),
-- RETURNS COMPUTATION FOR EACH ASSET --
btc_returns AS (
SELECT
period,
avg_btc_price,
LAG(avg_btc_price, 1) OVER (ORDER BY period) AS prev_btc_price,
(avg_btc_price - LAG(avg_btc_price, 1) OVER (ORDER BY period)) / LAG(avg_btc_price, 1) OVER (ORDER BY period) AS period_btc_return
FROM btc_prices
),
QueryRunArchived: QueryRun has been archived