SELECT recorded_hour::date AS date

, currency

, avg(price) AS price

FROM osmosis.core.ez_prices

WHERE symbol IN ('ARB')

GROUP BY 1, 2

), transfer AS

SELECT trn.block_timestamp::date AS date

, transfer_type

, sender

, receiver

, amount / power(10, decimal) AS amount_trn

, amount / power(10, decimal) * price AS amount_trn_usd

, foreign_chain

, CASE when foreign_chain IS NULL then 'Internal Chain' else 'Foregin Chain' end AS chain_type

FROM osmosis.core.fact_transfers trn

JOIN arb_price prc on trn.block_timestamp::date = prc.date

AND trn.currency = prc.currency

WHERE tx_succeeded = true

), rcv AS

SELECT receiver

, sum(amount_trn_usd) AS amount_rcv_usd

FROM transfer

GROUP BY 1

), snd AS

SELECT sender

, sum(amount_trn_usd) AS amount_snd_usd

FROM transfer

GROUP BY 1

), summary AS