t as (

recorded_hour::date as date,

currency,

symbol,

avg(price) as price_usd

from osmosis.core.ez_prices

group by 1, 2, 3

),

t0 as (

c.pool_id,

concat('#', c.pool_id::int, '-', b.project_name, '/', a.project_name) as pool_name

from osmosis.core.dim_liquidity_pools c

inner join osmosis.core.dim_tokens a

inner join osmosis.core.dim_tokens b

on a.address = c.assets[1].asset_address

and b.address = c.assets[0].asset_address

),

t2 as (

select

pool_id[0]::int as pool_id,

count(distinct tx_id) as transactions,

sum(price_usd * (amount / pow(10, decimal))) as volume_usd

from osmosis.core.fact_liquidity_provider_actions a

join t

on block_timestamp::date = date and t.currency = a.currency

where action = 'pool_joined'

and block_timestamp >= '2023-01-01' and block_timestamp < '2023-04-01'

group by 1

order by volume_usd desc

limit 10

),