SELECT $1 AS name, $2 AS address, $3 AS is_inflow, $4 AS is_outflow

FROM (

VALUES

('Binance', 'osmo129uhlqcsvmehxgzcsdxksnsyz94dvea907e575', true, false),

('Binance', 'osmo1v75g7hkmgks087p32yz9l3c88l9h02p2q5mr0t', false, true),

('Binance', 'osmo1krmqr8zhvteevq4hnl7q0pxtff6xpnqz4ckxcu', false, true),

('Binance', 'osmo1p3ucd3ptpw902fluyjzhq3ffgq4ntddas7rd8z', false, false),

('CRO', 'osmo1fzg6vwffwl6aaag3h5rhmchc3esnagx4wrwpv4', true, true)

)

),

inflows AS (

SELECT block_timestamp, name, amount / POW(10, 6) AS amount_osmo, amount_osmo * price AS amount_usd

FROM cex_addresses a

JOIN osmosis.core.fact_transfers t ON receiver = address AND t.currency = 'uosmo' AND sender NOT IN (SELECT address FROM cex_addresses)

LEFT JOIN osmosis.price.ez_prices p ON TRUNC(block_timestamp, 'hour') = recorded_hour AND p.currency = 'uosmo'

WHERE is_inflow

),

outflows AS (

SELECT block_timestamp, name, amount / POW(10, 6) AS amount_osmo, amount_osmo * price AS amount_usd

FROM cex_addresses

JOIN osmosis.core.fact_transfers ON sender = address AND currency = 'uosmo' AND receiver NOT IN (SELECT address FROM cex_addresses)

LEFT JOIN osmosis.price.ez_prices p ON TRUNC(block_timestamp, 'hour') = recorded_hour AND p.currency = 'uosmo'

WHERE is_outflow

),

daily_inflows AS (

SELECT TRUNC(block_timestamp, 'day') AS day, sum(amount_osmo) AS amount_osmo, sum(amount_usd) AS amount_usd

FROM inflows

GROUP BY ALL

),

daily_outflows AS (

SELECT TRUNC(block_timestamp, 'day') AS day, sum(amount_osmo) AS amount_osmo, sum(amount_usd) AS amount_usd

FROM outflows

GROUP BY ALL