WITH pool_positions_blocks AS ( SELECT nf_token_id, pool_address, liquidity_provider, Max(block_id) AS block_id FROM uniswapv3.positions WHERE block_timestamp >= DATEADD(day, -50, getDate()) GROUP BY pool_address, liquidity_provider, nf_token_id ), pool_stats_blocks AS ( SELECT Max(block_id) AS block_id, pool_address FROM uniswapv3.pool_stats WHERE block_timestamp >= DATEADD(day, -50, getDate()) GROUP BY pool_address ), current_prices AS ( SELECT pstat.pool_address, 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 FROM uniswapv3.pool_stats pstat INNER JOIN pool_stats_blocks mpblk ON mpblk.block_id = pstat.block_id AND mpblk.pool_address = pstat.pool_address ), current_results AS ( SELECT pos.pool_address, pool_name, pos.liquidity_provider, pos.nf_token_id, liquidity_adjusted, current_price_1_0, LEFT(pool_name, Regexp_instr(pool_name,'\\s\\d')) pool_, 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 curr_amt0, 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 amt0_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 amount1, 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 amt1_usd FROM uniswapv3.positions pos INNER JOIN pool_positions_blocks pos_blks ON pos_blks.block_id = pos.block_id AND pos_blks.pool_address = pos.pool_address AND pos_blks.liquidity_provider = pos.liquidity_provider AND pos_blks.nf_token_id = pos.nf_token_id JOIN current_prices curr_prc ON pos.pool_address = curr_prc.pool_address WHERE liquidity_adjusted > 0 ) , current_lps AS ( SELECT Sum(amt0_usd + amt1_usd) current_total_usd_value_of_lp , curr_rslt.liquidity_provider FROM current_results curr_rslt GROUP BY curr_rslt.liquidity_provider ORDER BY current_total_usd_value_of_lp DESC limit 10 ) , current_tot AS ( SELECT current_total_usd_value_of_lp AS tot_pools_usd, lp_act.liquidity_provider, Count(DISTINCT lp_act.pool_name) total_pools , Count(DISTINCT nf_token_id) tot_pools_pos FROM uniswapv3.lp_actions lp_act INNER JOIN current_lps liq_pool ON liq_pool.liquidity_provider = lp_act.liquidity_provider GROUP BY lp_act.liquidity_provider, current_total_usd_value_of_lp ORDER BY liq_pool.current_total_usd_value_of_lp DESC ) SELECT tot_pools_usd as supplied_pool_amount, ctot.liquidity_provider as whale, row_number() over(order by supplied_pool_amount desc) as rank_num, concat(chr(rank_num + 64), '.', whale) as label, total_pools FROM current_tot ctot LEFT JOIN uniswapv3.position_collected_fees pos_fees ON pos_fees.liquidity_provider = ctot.liquidity_provider GROUP BY ctot.liquidity_provider, ctot.total_pools, ctot.tot_pools_pos, tot_pools_usd ORDER BY tot_pools_usd DESC

How does Whales diversify?