with pool as (
select
tx_id,
pool_id,
count (tx_id) as TXs_1
from osmosis.core.fact_liquidity_provider_actions
where action = 'pool_joined'
and TX_STATUS = 'SUCCEEDED'
and block_timestamp::date = '2022-11-29'
group by 1,2
)
select
b.pool_id as Pool_number,
count(distinct b.tx_id) as TXs,
count(distinct liquidity_provider_address) as Providers
from osmosis.core.fact_liquidity_provider_actions a
join pool b on a.tx_id = b.Tx_id
group by 1
order by 2 desc
limit 10