WITH positions AS (

SELECT * FROM uniswapv3.positions

),

max_blocks AS (

SELECT

max(block_id) AS block_id,

pool_address,

liquidity_provider,

nf_token_id

FROM positions

GROUP BY

pool_address,

liquidity_provider,

nf_token_id

),

current_positions AS (

SELECT

pool_name,

liquidity_adjusted,

p.nf_token_id,

p.liquidity_provider

FROM positions p

INNER JOIN max_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

WHERE liquidity_adjusted > 0 AND pool_name = 'USDC-WETH 3000 60'

),

ranked_liquidity as (

select liquidity_adjusted, row_number() over (order by liquidity_adjusted desc) as rank

from current_positions

select 'usdc-weth' AS pool,

-- (1 − 2B) https://en.wikipedia.org/wiki/Gini_coefficient

1 - 2 * sum((liquidity_adjusted * (rank - 1) + liquidity_adjusted / 2)) / count(*) / sum(liquidity_adjusted) AS gini

from ranked_liquidity