query.sql

DROP VIEW IF EXISTS price_data CASCADE;

DROP VIEW IF EXISTS volatility CASCADE;

DROP VIEW IF EXISTS concepts.hourly_log_returns CASCADE;
CREATE MATERIALIZED VIEW concepts.hourly_log_returns AS
    SELECT
        candle_hour,
        symbol,
        LN(
            price/LAG(price) OVER(PARTITION BY symbol ORDER BY candle_hour)
        ) log_return
    FROM concepts.hourly_prices
    -- LAG() returns null for first row, causing a div by 0; skip that row.
    WHERE candle_hour > (
        SELECT candle_hour FROM concepts.hourly_prices
            WHERE symbol = symbol ORDER BY candle_hour ASC LIMIT 1
    );

DROP VIEW IF EXISTS concepts.hourly_volatility CASCADE;
CREATE MATERIALIZED VIEW concepts.hourly_volatility AS
    SELECT
        candle_hour,
        symbol,
        -- TODO: try to exclude, eg, first 12 price points for 12-hr vol.
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
            ) _12_hr,
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 23 PRECEDING AND CURRENT ROW
            ) _24_hr,
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 71 PRECEDING AND CURRENT ROW
            ) _72_hr,
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 167 PRECEDING AND CURRENT ROW
            ) _1_week,
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 671 PRECEDING AND CURRENT ROW
            ) _4_week,
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 2159 PRECEDING AND CURRENT ROW
            ) _90_day,
        stddev_samp(log_return)
            OVER(
                PARTITION BY symbol ORDER BY candle_hour
                ROWS BETWEEN 8639 PRECEDING AND CURRENT ROW
            ) annual
    FROM concepts.hourly_log_returns;

CREATE VIEW price_data AS
    SELECT
        candle_hour,
        symbol,
        price,
        volatility._12_hr as _12_hr_vol,
        volatility._24_hr as _24_hr_vol,
        volatility._72_hr as _72_hr_vol,
        volatility._1_week as _1_week_vol,
        volatility._4_week as _4_week_vol,
        volatility._90_day as _90_day_vol,
        volatility.annual as annual_vol
    FROM concepts.hourly_prices hp
    JOIN concepts.hourly_volatility hv
        ON hp.candle_hour = hv.candle_hour AND hp.symbol = hv.symbol
    WHERE concepts.hourly_prices.candle_hour > (
        SELECT candle_hour
        FROM concepts.hourly_prices
        ORDER BY candle_hour ASC
        LIMIT 1
    );

\copy (select * from price_data WHERE symbol = 'ETH' ORDER BY candle_hour DESC LIMIT 1000) to './eth_price_data.csv' CSV HEADER;