WITH dates AS (

SELECT date_day AS date

FROM crosschain.core.dim_dates

WHERE date_day >= CURRENT_DATE - 365 AND date_day < CURRENT_DATE

),

pools_0 AS (

SELECT pool_id, value:asset_address AS currency

FROM osmosis.core.dim_liquidity_pools,

LATERAL FLATTEN(input => assets)

),

pools AS (

SELECT pool_id,

ARRAY_AGG(currency) AS assets,

ARRAY_AGG(NVL(project_name, currency)) AS symbols

FROM pools_0

LEFT JOIN osmosis.core.dim_tokens ON address = currency

GROUP BY pool_id

),

st_pools AS (

SELECT MAX_BY(p.pool_id, d.token_0_amount) AS pool_id, assets

FROM pools p

LEFT JOIN osmosis.core.fact_pool_hour d ON p.pool_id = d.pool_id

WHERE ARRAY_SIZE(assets) = 2

AND (symbols[0] = 'st' || symbols[1] OR symbols[1] = 'st' || symbols[0])

AND token_0_amount > 100

GROUP BY assets

),

st_pool_prices AS (

SELECT date, pool_id, assets,

median(

CASE

WHEN from_currency = assets[0] THEN from_amount / to_amount

WHEN from_currency = assets[1] THEN to_amount / from_amount

END