WITH max_pos_blocks AS ( SELECT max(block_id) AS block_id, pool_address, liquidity_provider, nf_token_id FROM uniswapv3.positions GROUP BY pool_address, liquidity_provider, nf_token_id ), max_stats_blocks AS ( SELECT max(block_id) AS block_id, pool_address FROM j uniswapv3.pool_stats GROUP BY pool_address ), current_prices AS ( SELECT price_0_1 AS current_price_0_1, token0_balance_usd / token0_balance_adjusted AS price0_usd, price_1_0 AS current_price_1_0, token1_balance_usd / token1_balance_adjusted AS price1_usd, p.pool_address FROM uniswapv3.pool_stats p INNER JOIN max_stats_blocks mb ON mb.block_id = p.block_id AND mb.pool_address = p.pool_address --WHERE token0_balance_adjusted > 0 AND token1_balance_adjusted > 0 ) ,cte_results as( SELECT p.pool_address, pool_name, left(pool_name, REGEXP_INSTR(pool_name,'\\s\\d')) Pool_, p.liquidity_provider, p.nf_token_id, liquidity_adjusted, current_price_1_0, price_lower_1_0, price_upper_1_0, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN 'above price' WHEN current_price_1_0 < price_upper_1_0 and price_lower_1_0 <= current_price_1_0 THEN 'in price' ELSE 'below price' END AS position_state, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN liquidity_adjusted / sqrt(price_lower_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) WHEN current_price_1_0 < price_upper_1_0 THEN liquidity_adjusted / sqrt(current_price_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ELSE 0 END AS current_amount_0, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN ( liquidity_adjusted / sqrt(price_lower_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ) * COALESCE(price0_usd, 0) WHEN current_price_1_0 < price_upper_1_0 THEN ( liquidity_adjusted / sqrt(current_price_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ) * COALESCE(price0_usd, 0) ELSE 0 END AS current_amount_0_usd, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN 0 WHEN current_price_1_0 < price_upper_1_0 THEN liquidity_adjusted * sqrt(current_price_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ELSE liquidity_adjusted * sqrt(price_upper_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) END AS current_amount_1, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN 0 WHEN current_price_1_0 < price_upper_1_0 THEN ( liquidity_adjusted * sqrt(current_price_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ) * COALESCE(price1_usd, 0) ELSE ( liquidity_adjusted * sqrt(price_upper_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ) * COALESCE(price1_usd, 0) END AS current_amount_1_usd FROM uniswapv3.positions p INNER JOIN max_pos_blocks mb ON mb.block_id = p.block_id AND mb.pool_address = p.pool_address AND mb.liquidity_provider = p.liquidity_provider AND mb.nf_token_id = p.nf_token_id JOIN current_prices cp ON p.pool_address = cp.pool_address WHERE liquidity_adjusted > 0 ) , cte_lps as ( select SUM(current_amount_0_usd + current_amount_1_usd) Current_Total_USD_Value_Of_LP ,c1.liquidity_provider from cte_results c1 group by c1.liquidity_provider order by Current_Total_USD_Value_Of_LP desc limit 10 ) , cte_totals as ( select Current_Total_USD_Value_Of_LP AS Total_USD_ALL_POOLS ,a.liquidity_provider -- ,lp.total_usd_per_LP ,count(distinct a.pool_name) Total_pools ,count(distinct nf_token_id) Total_Pool_positions from uniswapv3.lp_actions a inner join cte_lps lp on lp.liquidity_provider = a.liquidity_provider group by a.liquidity_provider ,Current_Total_USD_Value_Of_LP -- ,Pool_Fees_USD order by lp.Current_Total_USD_Value_Of_LP desc ) select t.* ,SUM(ifnull(cf.amount1_USD,0) + ifnull(cf.amount0_usd,0) ) as Pool_Fees_Earned_USD from cte_totals t left join uniswapv3.Position_collected_fees cf on cf.liquidity_provider = t.liquidity_provider group by t.liquidity_provider , t.Total_pools , t.Total_Pool_positions ,Total_USD_ALL_POOLS order by Total_USD_ALL_POOLS desc

Query	Creator	Created
Uniswap nonsense uniswapv3 ethereum swaps evm dex	Sebly	Oct 30, 2023
Last value of day Aggregation uniswapv3 ethereum evm dex defi	Sebly	Oct 31, 2023
2023-10-30 03:33 PM uniswapv3 ethereum evm dex defi	Sebly	Oct 30, 2023

Sebly