theericstoneUniswap Protocol TVL
    Updated 2021-05-24
    with pairs as (
    select distinct pair
    from (
    select distinct pool_address as pair
    from ethereum.dex_liquidity_pools
    where platform = 'uniswap-v2'
    UNION
    select from_address as pair from (select count(tx_id) as ntx, from_address
    from ethereum.udm_events where LOWER(origin_function_name) like 'swap%'
    and from_label = 'uniswap'
    and from_address <> '0x7a250d5630b4cf539739df2c5dacb4c659f2488d' -- v2 router
    and block_timestamp > getdate() - interval '2 months'
    group by 2) where ntx > 2
    )
    ),

    liquid AS (
    SELECT
    rawbals.*,
    row_number() over (partition by day, pair order by liquidity desc) as rn from (
    SELECT
    date_trunc('day', balance_date) as day,
    user_address as pair,
    contract_address as token,
    sum(amount_usd) as liquidity
    --sum(balance) as native_liquidity,
    --row_number() over(partition by date_trunc('day', balance_date), user_address order by balance_date) as rn
    FROM ethereum.erc20_balances b
    LEFT JOIN pairs ON b.user_address = pairs.pair
    WHERE balance_date > getdate() - interval '2 weeks'
    AND user_address IN (pairs.pair)
    AND balance != 0
    AND amount_usd IS NOT NULL
    GROUP BY 1,2,3
    ) rawbals
    ),
    Run a query to Download Data