-- liquidity_provider,

uniswapv3.lp_actions.pool_name,

sum(uniswapv3.lp_actions.amount0_usd + uniswapv3.lp_actions.amount1_usd) as swap_volume,

sum(fee_usd) as total_fees,

sum(uniswapv3.position_collected_fees.amount0_usd + uniswapv3.position_collected_fees.amount1_usd) as fees_collected,

avg(fee_usd) as avg_fees,

count(uniswapv3.lp_actions.tx_id) as position_changes,

count(DISTINCT uniswapv3.lp_actions.liquidity_provider) as providers,

total_fees/fees_collected*100 as fees_percentage_of_profit

FROM uniswapv3.lp_actions

INNER JOIN ethereum.transactions on uniswapv3.lp_actions.tx_id = ethereum.transactions.tx_id

INNER JOIN uniswapv3.pools on uniswapv3.pools.pool_name = uniswapv3.lp_actions.pool_name

INNER JOIN uniswapv3.position_collected_fees on uniswapv3.position_collected_fees.pool_name = uniswapv3.lp_actions.pool_name

WHERE action = 'DECREASE_LIQUIDITY' --'INCREASE_LIQUIDITY' --

AND uniswapv3.lp_actions.block_timestamp >= '2021-05-05'

AND uniswapv3.lp_actions.amount0_usd >= 1

AND uniswapv3.lp_actions.amount1_usd >= 1

AND uniswapv3.pools.tick_spacing * (uniswapv3.lp_actions.tick_upper - uniswapv3.lp_actions.tick_lower)/100000 < 10

GROUP BY uniswapv3.lp_actions.pool_name

HAVING swap_volume >= 100000

AND providers > 10

AND fees_collected > 1

ORDER BY uniswapv3.lp_actions.pool_name