with act1 as (

with tab1 as (

select LIQUIDITY_PROVIDER_ADDRESS,CURRENCY,AMOUNT/pow(10,decimal) as provide_amount

from osmosis.core.fact_liquidity_provider_actions

where ACTION = 'pool_joined'

and BLOCK_TIMESTAMP>= current_date-7

and TX_STATUS='SUCCEEDED'),

tab2 as (

select ADDRESS,PROJECT_NAME

from osmosis.core.dim_labels)

select PROJECT_NAME,sum(provide_amount)as total_amount,LIQUIDITY_PROVIDER_ADDRESS

from tab1

left join tab2

on tab1.CURRENCY=tab2.ADDRESS

group by 1,3),

act2 as (

select RECORDED_AT::date as date,SYMBOL,avg(PRICE)as token_price

from osmosis.core.dim_prices

where date>=current_date-7

group by 1,2)

select SYMBOL,total_amount*token_price as usd_provided,LIQUIDITY_PROVIDER_ADDRESS

from act1

left join act2

on act1.PROJECT_NAME=act2.SYMBOL)

select LIQUIDITY_PROVIDER_ADDRESS,sum(usd_provided)as total_usd,rank()over (order by total_usd)as rank

from task1

where SYMBOL is not null

and SYMBOL in ('INJ','OSMO','ATOM','EVMOS','CRO','AXL','BTSG','NGM','ION','STARS')

group by 1

order by 2 desc

limit 10