SELECT recorded_at::DATE AS price_day,

project_name,

address,

avg(price) AS pricedaily

FROM osmosis.core.dim_prices INNER JOIN osmosis.core.dim_labels

ON project_name = symbol

GROUP BY 1,2,3

),

add as (

select

LIQUIDITY_PROVIDER_ADDRESS as LP_add,

SUM(amount/pow(10, decimal)*pricedaily) AS join_amount

from osmosis.core.fact_liquidity_provider_actions

inner join price on address = currency and block_timestamp::date = price_day

where pool_id in ('1', '678', '704', '712', '722', '803', '674', '497', '9', '604',

'812', '498', '2', '584', '611', '3', '481', '585', '773', '10')

and action = 'pool_joined'

group by 1

),

remove as (

select

LIQUIDITY_PROVIDER_ADDRESS as LP_remove,

SUM(amount/pow(10, decimal)*pricedaily) AS remove_amount

from osmosis.core.fact_liquidity_provider_actions

inner join price on address = currency and block_timestamp::date = price_day

where pool_id in ('1', '678', '704', '712', '722', '803', '674', '497', '9', '604',

'812', '498', '2', '584', '611', '3', '481', '585', '773', '10')

and action = 'pool_exited'

group by 1

),

top_wallets as (

select LP_add, join_amount - remove_amount as Liquidity

from add inner join remove on LP_add = LP_remove

where liquidity is not null