superflyUntitled Query
    Updated 2022-11-29
    WITH min1 as (SELECT date_trunc('hour', RECORDED_AT) as day1,
    avg(price) as price
    FROM osmosis.core.dim_prices
    LEFT outer JOIN osmosis.core.dim_labels ON PROJECT_NAME LIKE symbol
    WHERE symbol LIKE 'OSMO'
    GROUP BY 1 ), min2 AS (
    SELECT DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS DAY,
    CASE WHEN not TO_CURRENCY LIKE 'uosmo' THEN TO_CURRENCY ELSE FROM_CURRENCY END AS token_address,
    AVG(CASE WHEN not TO_CURRENCY LIKE 'uosmo' THEN ((FROM_AMOUNT/POWER(10, FROM_DECIMAL)) * PRICE)/(TO_AMOUNT/POWER(10, TO_DECIMAL))
    ELSE ((TO_AMOUNT/POWER(10, TO_DECIMAL)) * PRICE)/(FROM_AMOUNT/POWER(10, FROM_DECIMAL)) END) AS AVG_PRICE
    FROM osmosis.core.fact_swaps LEFT outer JOIN osmosis.core.dim_labels
    ON to_currency LIKE address
    OR from_currency LIKE address
    LEFT outer join min1
    ON date_trunc('day', block_timestamp) = day1
    WHERE PROJECT_NAME LIKE 'OSMO'
    GROUP BY 1,2
    UNION SELECT date_trunc('hour', RECORDED_AT) as day,
    'uosmo' as token_address,
    avg(price) as avg_price
    FROM osmosis.core.dim_prices
    LEFT outer JOIN osmosis.core.dim_labels
    ON PROJECT_NAME LIKE symbol
    WHERE symbol LIKE 'OSMO'
    GROUP BY 1,2
    )SELECT
    date_trunc('day', block_timestamp) as day,
    CASE WHEN deposits > 1 THEN 'HeadFirst Dive (Both Assets)'
    WHEN OSMO_DEPOSITS > 0 THEN 'Carefully Wade In (Single Side)'
    WHEN non_DEPOSITS > 0 THEN 'single lp(non-osmo LP)' END as deposit_type,
    count(*) as events,
    sum(VOLUME) as volume
    FROM (SELECT
    block_timestamp,
    tx_id,
    count(DISTINCT currency) as deposits,
    Run a query to Download Data