WITH base_data AS (

WITH MAIN AS (

SELECT

*,

COALESCE(UPPER(REGEXP_SUBSTR(token_id, '^nep141:([a-z]+)-', 1, 1, 'e', 1)), 'NEAR') as blockchainn,

CASE blockchainn

WHEN 'ETH' THEN 'ethereum'

WHEN 'ARB' THEN 'arbitrum'

ELSE lower(blockchainn)

END AS blockchain,

IFF(REGEXP_SUBSTR(token_id, '^nep141:([a-z]+)-', 1, 1, 'e', 1) is null,

SPLIT(token_id, ':') [1],

SPLIT(SPLIT(token_id, ':') [1], '-') [1]

) as token_address_a,

IFF(token_address_a like '%.omft.near',

SPLIT(token_address_a, '.') [0],

token_address_a

) as token_address

FROM near.defi.fact_intents

),

price as (

SELECT

hour,

token_address,

symbol,

decimals,

blockchain,

avg(price) avg_price

FROM near.price.ez_prices_hourly

where hour >= '2024-11-01'

GROUP BY 1,2,3,4,5

UNION ALL

SELECT

hour,

token_address,