nsa2000the most popular wallets according to the LP deposit value
    Updated 2022-10-26
    WITH price1 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
    ), TAB1 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,
    MEDIAN(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 MEDIAN_PRICE,
    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 price1
    ON date_trunc('day', block_timestamp) = day1
    WHERE PROJECT_NAME LIKE 'OSMO'
    -- AND block_timestamp > '2022-09-01'
    GROUP BY 1,2

    ), price2 as (
    SELECT
    DAY as day1,
    PROJECT_NAME as pn,
    MEDIAN_PRICE as price
    -- AVG_PRICE
    FROM tab1
    LEFT outer JOIN osmosis.core.dim_labels
    Run a query to Download Data