MetiocreSushi: Top pools by count daily
Updated 2022-05-19
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
›
⌄
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