select block_timestamp as fecha,

date_trunc ('hour', fecha) as hora,

case token_in_contract

when 'A.b19436aae4d94622.FiatToken' then 'USDC'

when 'A.1654653399040a61.FlowToken' then 'FLOW'

when 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then 'USDT'

when 'A.d01e482eb680ec9f.REVV' then 'REVV'

when 'A.3c5959b568896393.FUSD' then 'FUSD'

when 'A.0f9df91c9121c460.BloctoToken' then 'BLT'

when 'A.142fa6570b62fd97.StarlyToken' then 'STARLY'

when 'A.475755d2c9dccc3a.TeleportedSportiumToken' then 'SPRT'

end as token_for,

tx_id,

token_in_amount

--count (tx_id) as swaps,

--sum (token_in_amount) as token_for_vol

from flow.core.fact_swaps

where fecha > '2022-06-16'

),

hourly_price as (

select fs.fecha,

fs.hora,

p.price_usd,

fs.token_for,

case fs.token_for

when 'USDC' then fs.token_in_amount

when 'FLOW' then fs.token_in_amount * p.price_usd

when 'USDT' then fs.token_in_amount

when 'REVV' then fs.token_in_amount * p.price_usd

when 'FUSD' then fs.token_in_amount

when 'BLT' then fs.token_in_amount * p.price_usd

when 'STARLY' then fs.token_in_amount * p.price_usd

when 'SPRT' then fs.token_in_amount * 0.4