MetiocreSushi: Top pools by count daily
    Updated 2022-05-19
    WITH add_liq AS (SELECT to_address_name as liq_pool, block_timestamp::date as date, sum(1) as deposits_count
    FROM ethereum.udm_events
    WHERE to_label = 'sushiswap' AND to_label_subtype = 'pool'
    and block_timestamp::date >= CURRENT_DATE - interval '90 days'
    AND origin_function_name IN ('addLiquidity', 'addLiquidityETH')
    AND amount_usd IS NOT NULL
    and liq_pool not like '%sushiswap%'
    GROUP BY 1,2),

    remove_liq AS (SELECT from_address_name as liq_pool, block_timestamp::date as date, sum(1) as withdraws_count
    FROM ethereum.udm_events
    WHERE from_label = 'sushiswap' AND from_label_subtype = 'pool'
    and block_timestamp::date >= CURRENT_DATE - interval '90 days'
    AND origin_function_name IN ('removeLiquidityETH', 'removeLiquidityETHSupportingFeeOnTransferTokens', 'removeLiquidityWithPermit',
    'removeLiquidityETHWithPermitSupportingFeeOnTransferTokens', 'removeLiquidity', 'removeLiquidityETHWithPermit')
    AND amount_usd IS NOT NULL
    and liq_pool not like '%sushiswap%'
    GROUP BY 1,2)


    SELECT add_liq.liq_pool, add_liq.date, deposits_count, withdraws_count, deposits_count+withdraws_count as total_transactions_count
    FROM add_liq LEFT JOIN remove_liq ON add_liq.liq_pool = remove_liq.liq_pool and add_liq.date=remove_liq.date
    where withdraws_count is not null
    -- ORDER BY total_transactions_count DESC
    -- LIMIT 20
    Run a query to Download Data