-- forked from stream2 - atom phases x @ https://flipsidecrypto.xyz/edit/queries/f6d37832-5502-4bbb-8c58-6ae66afbab5e with pr_atom as (SELECT RECORDED_HOUR::date as datex, avg(PRICE) as price from osmosis.core.ez_prices where RECORDED_HOUR::date >= '2023-01-01' and SYMBOL = 'OSMO' GROUP by 1 ), t0_at as (SELECT DISTINCT tx_id as allhash from osmosis.core.fact_msg_attributes where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'stream_id' and ATTRIBUTE_VALUE = '1'), tdep_at as (SELECT DISTINCT tx_id as hash, case when ATTRIBUTE_VALUE = 'subscribe_pending' then 'Bootstrapping' when ATTRIBUTE_VALUE = 'subscribe' then 'Streaming' end AS phase from osmosis.core.fact_msg_attributes join t0_at on tx_id=allhash where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'action' and ATTRIBUTE_VALUE in ('subscribe_pending','subscribe')), tdep2_at as (SELECT DISTINCT ATTRIBUTE_VALUE as wallet, hash, phase from osmosis.core.fact_msg_attributes join tdep_at on tx_id=hash where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'owner' ), tdep3_at as (SELECT DISTINCT wallet, hash, ATTRIBUTE_VALUE/pow(10,6) as amount, amount*price as usd, date_trunc('hour',block_timestamp) as time, phase from osmosis.core.fact_msg_attributes join tdep2_at on tx_id=hash join pr_atom on block_timestamp::date=datex where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'in_amount' ), twit1_at as (SELECT DISTINCT tx_id as hash, case when ATTRIBUTE_VALUE = 'withdraw_pending' then 'Bootstrapping' when ATTRIBUTE_VALUE = 'withdraw' then 'Streaming' end AS phase from osmosis.core.fact_msg_attributes join t0_at on tx_id=allhash where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'action' and ATTRIBUTE_VALUE in ('withdraw_pending','withdraw')), twit2_at as (SELECT DISTINCT ATTRIBUTE_VALUE as wallet, hash, phase from osmosis.core.fact_msg_attributes join twit1_at on tx_id=hash where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'operator_target' ), twit3_at as (SELECT DISTINCT wallet, hash, ATTRIBUTE_VALUE/pow(10,6) as amount, amount*price as usd, date_trunc('hour',block_timestamp) as time, phase from osmosis.core.fact_msg_attributes join twit2_at on tx_id=hash join pr_atom on block_timestamp::date=datex where block_timestamp::date >= '2023-04-01' and block_timestamp::date < '2023-05-01' and ATTRIBUTE_KEY = 'withdraw_amount' ), all_tx as (SELECT *, 'Deposited' as action from tdep3_at UNION SELECT *, 'Withdrawn' as action from twit3_at ) SELECT count(DISTINCT hash ) as Deposits,case when usd < '10' then 'Less than 10 $USD' when usd >= '10' and usd < '50' then 'Between 10~50 $USD' when usd >= '50' and usd < '100' then 'Between 50~100 $USD' when usd >= '100' and usd < '500' then 'Between 100~500 $USD' when usd >= '500' and usd < '1000' then 'Between 500~1000 $USD' when usd >= '1000' then 'More than 1000 $USD' end as tier,action from all_tx where action = 'Deposited' GROUP by 2,action

🌊🔄StreamSwap: ⚛🎬$ATOM/$FLIX vs 🧪🎬$OSMO/$FLIX