SELECT

DISTINCT liquidity_provider_address AS lp_address,

currency, amount, decimal, amount/pow(10,decimal) AS liquid_amount

FROM osmosis.core.fact_liquidity_provider_actions

WHERE decimal IS NOT NULL

AND block_timestamp >= '2022-01-01'

AND currency = 'uosmo'

AND tx_status = 'SUCCEEDED'

AND action = 'pool_joined'

ORDER BY 5 DESC

),

list AS (

SELECT

row_number() over( partition by address order by date ) row_num,

date,

address,

balance/pow(10,decimal) AS balance

FROM

osmosis.core.fact_daily_balances

WHERE currency = 'uosmo'

),

Balance AS(

SELECT

DISTINCT balance AS balance, address AS l_address

FROM list

WHERE row_num = 1

)

SELECT DISTINCT lp_address AS lp_address, sum(liquid_amount) AS liquid_amount, balance,

case

when balance >= 1000 then 'Whale'

when balance < 1000 then 'Not whale'

end as composition

FROM lp

JOIN balance bl ON lp.lp_address = bl.l_address

GROUP BY 1,3