-- forked from 09 - types of balances @ https://flipsidecrypto.xyz/edit/queries/cb06e137-0e46-4a3c-93bb-d32fd6e74edc -- -- forked from 07 - types of active days @ https://flipsidecrypto.xyz/edit/queries/7cb0cd86-2817-40e2-8dbb-e19654087892 with NEAR_users AS ( SELECT min( a.block_timestamp ) AS min_date , a.trader AS users FROM near.core.ez_dex_swaps a GROUP BY 2 ) , rec_near AS ( SELECT a.tx_receiver AS users , sum( ( a.deposit / power( 10 , 24 ) ) ) AS rec_N_volume FROM near.core.fact_transfers a JOIN NEAR_users b ON a.tx_receiver = b.users GROUP BY 1 ORDER BY 2 DESC ) , sig_near AS ( SELECT a.tx_signer AS users , sum( ( a.deposit / power( 10 , 24 ) ) ) AS sig_N_volume FROM near.core.fact_transfers a JOIN NEAR_users b ON a.tx_signer = b.users GROUP BY 1 ORDER BY 2 DESC ) , rec_sig_near AS ( SELECT a.users AS users , sum( zeroifnull( a.rec_N_volume ) - zeroifnull( b.sig_N_volume ) ) AS all_balance FROM rec_near a full OUTER JOIN sig_near b ON a.users = b.users GROUP BY 1 HAVING all_balance > 0 ORDER BY 2 DESC ) , buy_near AS ( SELECT a.trader AS users , sum( a.amount_in ) AS in_balances_N FROM near.core.ez_dex_swaps a JOIN NEAR_users b ON a.trader = b.users WHERE a.token_in = 'wNEAR' GROUP BY 1 ORDER BY 2 DESC ) , sell_near AS ( SELECT a.trader AS users , sum( a.amount_out ) AS out_balances_N FROM near.core.ez_dex_swaps a JOIN NEAR_users b ON a.trader = b.users WHERE a.token_out = 'wNEAR' GROUP BY 1 ORDER BY 2 DESC ) , buy_sell_near AS ( SELECT a.users AS users , sum( zeroifnull( in_balances_N ) - zeroifnull( out_balances_N ) ) AS all_balance FROM buy_near a JOIN sell_near b ON a.users = b.users GROUP BY 1 ORDER BY 2 DESC ) , final AS ( SELECT users , all_balance FROM rec_sig_near UNION ALL SELECT users , all_balance FROM buy_sell_near ) , concols AS ( SELECT users AS users , sum( all_balance ) AS balances FROM buy_sell_near GROUP BY 1 ORDER BY 2 DESC ) SELECT date_trunc( 'week' , b.block_timestamp ) AS date , count( DISTINCT( users ) ) AS users , CASE when balances < 10 then 'a : < 10 Ⓝ' when balances BETWEEN 10 AND 100 then 'b : < 100 Ⓝ' when balances BETWEEN 100 AND 1000 then 'c : < 1K Ⓝ' when balances BETWEEN 1000 AND 10000 then 'd : < 10K Ⓝ' when balances BETWEEN 10000 AND 100000 then 'e : < 100K Ⓝ' when balances BETWEEN 100000 AND 1000000 then 'f : < 1M Ⓝ' when balances BETWEEN 1000000 AND 10000000 then 'g : < 10M Ⓝ' when balances BETWEEN 10000000 AND 100000000 then 'h : < 100M Ⓝ' when balances BETWEEN 100000000 AND 1000000000 then 'i : < 1B Ⓝ' when balances > 1000000000 then 'j : > 1B Ⓝ' END AS type FROM concols a JOIN near.core.ez_dex_swaps b ON a.users = b.trader GROUP BY 1 , 3 ORDER BY 1 DESC , len( type ) , type

Exploring DEX swaps for NEAR protocol analysis