date_trunc('day', recorded_at) as date,

avg(price) as avg_daily_price

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by date)

select

date_trunc('day', block_timestamp) as date,

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

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

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

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

else 'Binance Innerflow' end as type,

count (distinct tx_id) as tx_count,

count (distinct sender) as Senders_Count,

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

sum (amount*avg_daily_price/pow(10,decimal)) as USD_Volume

from osmosis.core.fact_transfers t1 join osmo_price t2 on t1.block_timestamp::date = t2.date

where tx_status = 'SUCCEEDED'

and currency = 'uosmo'

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

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

group by 1,2