select

recorded_hour::date as date,

lower(

CASE id

WHEN 'tether' THEN 'usdt'

WHEN 'usd-coin' THEN 'usdc'

WHEN 'the-sandbox' THEN 'sand'

WHEN 'pangolin' THEN 'png'

WHEN 'yield-yak' THEN 'yak'

WHEN 'wrapped-avax' THEN 'wavax'

ELSE id

END

) as symbol,

avg(close) as price_usd

from

crosschain.core.fact_hourly_prices

where

recorded_hour >= CURRENT_DATE - {{n_days}}

group by

1,

2

),

p_txns as (

select

block_timestamp,

sender,

source,

destination,

router,

tx_hash,

token_address,

t.symbol,

token_amount * p.price_usd as token_amount_usd

from

(