(select min(date) as datee,pool_id as pool_ids from

((select POOL_IDS[0] as pool_id,

date_trunc('day',BLOCK_TIMESTAMP) as date

from

osmosis.core.fact_swaps)

union all

(select POOL_IDS[0] as pool_id,

date_trunc('day',BLOCK_TIMESTAMP) as date

from

osmosis.core.fact_swaps)

)group by 2

,

t2 as

(select LABEL as token1 ,ASSETS[1]['asset_address'] as asset2,POOL_ID from osmosis.core.dim_liquidity_pools a

left join osmosis.core.dim_labels b on a.ASSETS[0]['asset_address']=b.address),

pools as

( select

case when LABEL is null then TOKEN1 else concat(TOKEN1,'___',LABEL) end as tokens,

case when LABEL is null then 'singlepool' else 'tokenpair' end as pooltypes

,CAST(POOL_ID AS string) as POOL_ID from t2 a left join

osmosis.core.dim_labels b on a.asset2=b.address)

select DATEE,POOL_IDS,TOKENS from t1 a left join pools b on a.pool_ids=b.POOL_ID

order by DATEE desc