select date_trunc('hour', recorded_at) as fecha,

symbol,

avg (price) as avg_price

from osmosis.core.dim_prices

group by 1,2

),

swaps_temp as (

select s.block_timestamp,

s.trader as address,

l.project_name as symbol_from,

s.from_amount / pow(10,s.from_decimal) as amount_from,

case symbol_from

when 'USDC' then amount_from

when 'USDC.grv' then amount_from

when 'DAI' then amount_from

else amount_from * p.avg_price

end as amount_from_usd,

s.to_currency,

s.to_amount / pow(10,s.to_decimal) as amount_to

from osmosis.core.fact_swaps s

left join osmosis.core.dim_labels l

on s.from_currency = l.address

left join prices p

on l.project_name = p.symbol and date_trunc('hour', s.block_timestamp) = p.fecha

where s.tx_succeeded = TRUE

and s.block_timestamp <= '2022-12-31'

and s.block_timestamp >= '2022-01-01'

),

swaps_full as (

select st.block_timestamp,

st.address,

'SWAP' as type,

st.symbol_from,