-- 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,

count(uniswapv3.position_collected_fees.tx_id) as profit_collections,

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

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

AND uniswapv3.lp_actions.amount0_usd >= 1

AND uniswapv3.lp_actions.amount1_usd >= 1

AND (abs((uniswapv3.lp_actions.price_upper_0_1_usd - uniswapv3.lp_actions.price_lower_0_1_usd)/token0_price) < 10

OR abs((uniswapv3.lp_actions.price_upper_1_0_usd - uniswapv3.lp_actions.price_lower_1_0_usd)/token1_price) < 10)

GROUP BY uniswapv3.lp_actions.pool_name

HAVING providers > 10

AND fees_collected > total_fees

ORDER BY uniswapv3.lp_actions.pool_name