-- forked from Cosmos Hub - IBC Transfer ATOM @ https://flipsidecrypto.xyz/edit/queries/b697c5dc-ce82-470e-9cc4-0c2770a9a964

-- forked from SocioCrypto / transfers @ https://flipsidecrypto.xyz/SocioCrypto/q/untitled-query-8QeS85

WITH Temp AS

SELECT

block_timestamp::date as day,

'CosmosHub' AS blockchain,

count(DISTINCT sender) as n_senders,

count(DISTINCT tx_id) as n_transfers,

sum(amount)/pow(10,6) as amnt,

median(amount)/pow(10,6) as med_amnt

FROM cosmos.core.fact_transfers

WHERE currency IN ('ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2', 'uatom')

AND block_timestamp >= {{start_date}}

AND transfer_type IN ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

GROUP by day

UNION ALL

SELECT

block_timestamp::date as day,

'Osmosis' AS blockchain,

count(DISTINCT sender) as n_senders,

count(DISTINCT tx_id) as n_transfers,

sum(amount)/pow(10,6) as amnt,

median(amount)/pow(10,6) as med_amnt

FROM osmosis.core.fact_transfers

WHERE currency IN ('ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2', 'uatom')

AND block_timestamp >= {{start_date}}

AND transfer_type IN ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

GROUP by day