hour::date AS hour,

symbol,

AVG(price) AS price

FROM ethereum.price.ez_hourly_token_prices

WHERE symbol IN ('Cake','WBTC','WETH','USDD','USDT','USDC')

AND hour::date >= '2022-01-01'

GROUP BY 1,2

),

txs AS(

SELECT

block_timestamp,

sender,

receiver,

tx_hash,

source_chain_name,

amount_unadj,

bridge.token_address,

(CASE

when split_part(bridge.token_address, '::', 3) = 'BTCbOFT' THEN 8

when split_part(bridge.token_address, '::', 3) = 'CakeOFT' THEN 8

when split_part(bridge.token_address, '::', 3) = 'WBTC' THEN 6

when split_part(bridge.token_address, '::', 3) = 'WETH' THEN 6

when split_part(bridge.token_address, '::', 3) = 'USDD' THEN 6

when split_part(bridge.token_address, '::', 3) = 'USDT' THEN 6

when split_part(bridge.token_address, '::', 3) = 'USDC' THEN 6

end

) AS decimals,

amount_unadj / pow(10,decimals) AS amount,

(CASE

when split_part(bridge.token_address, '::', 3) = 'BTCbOFT' THEN 'WBTC'

when split_part(bridge.token_address, '::', 3) = 'CakeOFT' THEN 'Cake'

else split_part(bridge.token_address, '::', 3)

end