with osmopricet as (

select recorded_hour as date1,

price as OSMO_Price

from osmosis.core.ez_prices

where symbol = 'OSMO'

and recorded_hour >= '2022-12-01' )

select date_trunc(hour,block_timestamp) as date,

case when block_timestamp::date = '2022-12-10' then 'December 10'

when block_timestamp::date < '2022-12-10' then 'Previous Days'

when block_timestamp::date > '2022-12-10' then 'Next Days'

end as timespan,

case when sender in ('osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575','osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu')

and receiver not in ('osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575','osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu') then 'Outflow From Binance'

when receiver in ('osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575','osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu')

and sender not in ('osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575','osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu') then 'Inflow To Binance'

else 'Inner Binance Flow' end as type,

osmo_price,

count (distinct tx_id) as tx_count,

count (distinct sender) as Senders_Count,

sum (amount/pow(10,decimal)) as OSMO_Volume

from osmosis.core.fact_transfers t1 join osmopricet t2 on date_trunc(hour,t1.block_timestamp) = t2.date1

where tx_succeeded = 'TRUE'

and currency = 'uosmo'

and (receiver in ('osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575','osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu')

or sender in ('osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575','osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu'))

and block_timestamp::Date >= '2022-12-03' and block_timestamp::date < '2022-12-18'

group by 1,2,3,4