WITH tokens AS (

select

TOKEN_CONTRACT as contract_address,

symbol,

DECIMALS,

case

WHEN symbol in ('USDT', 'USN', 'USDC', 'cUSD', 'DAI', 'nUSDO') THEN 'stable'

ELSE 'non-stable' end as type

FROM near.core.dim_token_labels

DATE_trunc('{{Frequency}}' , block_timestamp) as date,

SUM(

CASE

WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.DECIMALS)

WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.DECIMALS), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.DECIMALS))

WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.DECIMALS) * p.price

WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.DECIMALS), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.DECIMALS)) * p2.price

ELSE 0

END

) as volume,

SUM(volume) OVER (ORDER BY date) as aggregate

FROM near.core.fact_receipts

LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address

LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address

LEFT JOIN (

SELECT DATE(timestamp) as p_date, symbol, AVG(price_usd) as price

FROM near.core.fact_prices

GROUP BY 1, 2

) p ON DATE(block_timestamp) = p_date AND p.symbol ILIKE t.symbol

LEFT JOIN (

SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price

FROM near.core.fact_prices