mondov2023-05-15 06:27 PM
    Updated 2023-05-15
    SELECT
    DATE_TRUNC('day', HOUR) AS DAY,
    AVG(CASE WHEN SYMBOL = 'DOGE' THEN PRICE END) AS DOGE_PRICE,
    AVG(CASE WHEN SYMBOL = 'WBTC' THEN PRICE END) AS WBTC_PRICE,
    AVG(CASE WHEN SYMBOL = 'SHIB' THEN PRICE END) AS SHIB_PRICE,
    AVG(CASE WHEN SYMBOL = 'FLOKI' THEN PRICE END) AS FLOKI_PRICE,
    AVG(CASE WHEN SYMBOL = 'ELON' THEN PRICE END) AS ELON_PRICE,
    AVG(CASE WHEN SYMBOL = 'PEPE' THEN PRICE END) AS PEPE_PRICE,
    AVG(CASE WHEN SYMBOL = 'CHAD' THEN PRICE END) AS CHAD_PRICE
    FROM (
    SELECT *
    FROM ethereum.core.fact_hourly_token_prices
    WHERE HOUR >= '2021-01-01' AND SYMBOL IN ('WBTC', 'SHIB', 'FLOKI', 'ELON', 'PEPE', 'CHAD')
    UNION ALL
    SELECT *
    FROM bsc.core.fact_hourly_token_prices
    WHERE HOUR >= '2021-01-01' AND SYMBOL = 'DOGE'
    ) t
    GROUP BY 1
    ORDER BY 1;