with base as (with scr_prices as (select

date(recorded_at) as days,

avg(price) as price,

sum(VOLUME_24H) as vol

from

osmosis.core.dim_prices

where

symbol = 'OSMO'

and days >= CURRENT_DATE - 30

group by 1

order by 1)

select

'To Binance' as path ,

count (distinct tx_id) as txs,

sum (AMOUNT/1e6) as native_volume,

sum ((AMOUNT/1e6)*price) as USD_Volume,

avg((AMOUNT/1e6)*price) as avg_USD_Volume,

avg (AMOUNT/1e6) as avg_native_volume

from osmosis.core.fact_transfers sw

inner join scr_prices pr on sw.block_timestamp::date = pr.days

where

receiver ilike 'osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575' and currency = 'uosmo'

and

block_timestamp >= '2022-10-28'

and

tx_status = 'SUCCEEDED'

select *,

USD_VOLUME/txs as usd_per_tx,

NATIVE_VOLUME/txs as osmo_per_tx

from base