with prices_near AS (

SELECT

timestamp::date AS day,

token_contract,

symbol,

CASE

WHEN symbol = 'wNEAR' THEN 'Native Coin'

WHEN symbol IN ('USDC', 'USDT', 'USN', 'DAI') THEN 'Stablecoins'

ELSE 'Other Tokens'

END AS token_type,

AVG(price_usd) AS price

FROM

near.core.fact_prices

WHERE

timestamp::date BETWEEN (CURRENT_DATE -180) AND (CURRENT_DATE - 1)

AND price_usd > 0

GROUP BY

day,

token_contract,

symbol

),

NEAR AS (

SELECT

swaps.block_timestamp,

swaps.tx_hash AS tx,

-- CASE

-- WHEN swaps.platform LIKE '%jumbo_exchange%' THEN 'Jumbo'

-- WHEN swaps.platform LIKE '%ref-finance%' THEN 'Ref Finance'

-- ELSE 'Others'

'Ref Finance' AS dex,

swaps.trader AS swapper,

swaps.token_in_contract AS from_token_contract,

swaps.token_out_contract AS to_token_contract,

swaps.token_in AS from_token,

swaps.token_out AS to_token,