SELECT

DISTINCT l.pool_id[0] as pool_id,

assets

FROM osmosis.core.fact_liquidity_provider_actions l

LEFT OUTER JOIN osmosis.core.dim_liquidity_pools p

ON l.pool_id[0] = p.pool_id

WHERE array_size(assets) <= 2

),

max_ts AS (

SELECT

symbol,

max(recorded_at) as time

FROM osmosis.core.dim_prices

WHERE recorded_at >= CURRENT_DATE

GROUP BY symbol

),

price AS (

SELECT

time,

t.symbol,

price

FROM max_ts t

LEFT OUTER JOIN osmosis.core.dim_prices p

ON t.symbol = p.symbol

AND t.time = p.recorded_at

SELECT

pool_id,

concat(pool_id, ': ', upper(address_name)) as pool_name,

assets[0] :asset_address as asset_1,

assets[1] :asset_address as asset_2,

l.label as token_1,

l.project_name as symbol_1,

ll.label as token_2,

ll.project_name as symbol_2