Deebs-DeFi-j9fRbzEVMOS Weekly WHALEs
    Updated 2023-10-23
    -- forked from 6da8a9ef-b80a-423f-a95f-3f18b2efd42d

    -- forked from eb7138fa-9edd-4330-a26e-5f2941da1573

    -- forked from f6b7dbe2-6069-4747-8476-4e50f5b488d9

    -- forked from 164b6dd3-a48e-43a2-96fe-13088ff79277

    WITH swaps AS (
    SELECT
    sum(TO_AMOUNT/POW(10, TO_DECIMAL)) AS weekly_tokens_purchased,
    TRADER,
    POOL_IDS AS pool,
    TO_CURRENCY AS token_address,
    SUBSTRING(TRADER, 38, 43) as trader_short_address
    from osmosis.core.fact_swaps
    WHERE BLOCK_TIMESTAMP > CURRENT_DATE - 7
    AND TO_CURRENCY='ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A'
    GROUP BY 2,4,3,5
    ),

    labels AS (
    SELECT
    CURRENCY AS token_address,
    SYMBOL AS token_name,
    avg(PRICE) AS average_price_usd
    FROM osmosis.core.ez_prices
    WHERE RECORDED_HOUR > CURRENT_DATE - 7
    GROUP BY 1,2
    )

    SELECT
    s.TRADER,
    s.trader_short_address,
    s.weekly_tokens_purchased,
    s.weekly_tokens_purchased * l.average_price_usd AS value_USD,
    Run a query to Download Data