with Transfers as( select BLOCK_TIMESTAMP, TX_SIGNER as user_address, (deposit / pow(10, 24) + transaction_fee / pow(10,24)) * -1 as amount FROM near.core.fact_transfers where status ilike 'true' UNION ALL select BLOCK_TIMESTAMP, TX_RECEIVER as user_address, deposit / pow(10, 24) as amount FROM near.core.fact_transfers where status ilike 'true' ), SWAP_FEE_JOIN as( select a.*, b.transaction_fee as transaction_fee from near.core.ez_dex_swaps as a inner join near.core.fact_transactions as b on a.tx_hash = b.tx_hash and a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP ), SWAPS as( select BLOCK_TIMESTAMP, trader as user_address, (amount_in + transaction_fee/pow(10,24))* -1 as amount from( SWAP_FEE_JOIN ) where token_in='NEAR' UNION ALL select BLOCK_TIMESTAMP, trader as user_address, (amount_out + transaction_fee/pow(10,24)) as amount from( SWAP_FEE_JOIN ) where token_out='NEAR' ), joined_function_transaction as( select a.*, b.TX_signer as user_address from near.core.fact_actions_events_function_call as a inner join near.core.fact_transactions as b on a.tx_hash = b.tx_hash and a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP ), stake_unstake as( select block_timestamp, user_address, (deposit / pow(10, 24)) * -1 as amount FROM joined_function_transaction where method_name = 'deposit_and_stake' or method_name = 'stake' union ALL select block_timestamp, user_address, try_parse_json(args):min_expected_near/ pow(10, 24) as amount from joined_function_transaction where method_name = 'liquid_unstake' and amount > 0 union ALL select block_timestamp, user_address, try_parse_json(args):min_amount_out/ pow(10, 24) as amount from joined_function_transaction where method_name ilike 'instant_unstake' and amount > 0 ), unstake_withdraw_transactions AS( select * FROM near.core.fact_actions_events_function_call where method_name = 'withdraw_unstaked' ), joined_unstake_withdraw_receipt as( select a.block_timestamp as block_timestamp, try_parse_json(b.logs[0]):account_id as user_address, try_parse_json(b.logs[0]):amount / pow(10, 24) as amount from unstake_withdraw_transactions as a inner join near.core.fact_receipts as b on a.tx_hash = b.tx_hash and a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP where b.logs[0] != '' ), multisend as( SELECT block_timestamp, try_parse_json(f.value):account_id as user_address, try_parse_json(f.value):amount / pow(10, 24) as amount FROM near.core.fact_actions_events_function_call, table(flatten(try_parse_json(near.core.fact_actions_events_function_call.ARGS):accounts)) f where method_name ='multisend_from_balance' or method_name = 'multisend_from_balance_unsafe' ), users_transactions_all as( select date_trunc('day', block_timestamp) as mydate,user_address, amount from Transfers union ALL select date_trunc('day', block_timestamp) as mydate,user_address, amount from SWAPS union ALL select date_trunc('day', block_timestamp) as mydate,user_address, amount from stake_unstake union ALL select date_trunc('day', block_timestamp) as mydate,user_address, amount from joined_unstake_withdraw_receipt union ALL select date_trunc('day', block_timestamp) as mydate,user_address, amount from multisend ), transactions_daily_change as( select mydate, user_address, sum(amount) as daily_change from users_transactions_all group by mydate, user_address ), transactions_daily_change_index as( SELECT mydate, user_address, daily_change, ROW_NUMBER() OVER(PARTITION BY user_address ORDER BY mydate) AS rownum FROM transactions_daily_change ORDER BY mydate ), positive_change AS( select user_address, daily_change, ROW_NUMBER() OVER(PARTITION BY user_address ORDER BY mydate) AS rownum from transactions_daily_change_index where daily_change > 0 ), negative_change as( select user_address, daily_change, ROW_NUMBER() OVER(PARTITION BY user_address ORDER BY mydate) AS rownum from transactions_daily_change_index where daily_change < 0 ), max_deposit_change as( select user_address, max(rownum) as number_of_deposit from positive_change group by user_address ), max_withdraw_change as( select user_address, max(rownum) as number_of_withdraw from negative_change group by user_address ), user_deposit_withdraw_number as( select a.*, b.number_of_withdraw as number_of_withdraw FROM max_deposit_change a inner join max_withdraw_change b on a.user_address = b.user_address ), user_balance as( select user_address, sum(daily_change) as balance from transactions_daily_change_index group by user_address having balance > 0 ), balance_distribution as( select user_address, balance, CASE WHEN balance <= 0.05 THEN '<= 0.05' WHEN balance > 0.05 and balance <= 1 THEN '0.05-1' WHEN balance > 1 and balance <= 10 THEN '1-10' WHEN balance > 10 and balance <= 50 THEN '10-50' WHEN balance > 50 and balance <= 100 THEN '50-100' WHEN balance > 100 and balance <= 200 THEN '100-200' WHEN balance > 200 and balance <= 500 THEN '200-500' WHEN balance > 500 and balance <= 1000 THEN '500-1000' WHEN balance > 1000 and balance <= 2000 THEN '1000-2000' WHEN balance > 2000 and balance <= 5000 THEN '2000-5000' WHEN balance > 5000 and balance <= 10000 THEN '5000-10000' WHEN balance > 10000 and balance <= 20000 THEN '10000-20000' WHEN balance > 20000 and balance <= 50000 THEN '20000-50000' WHEN balance > 50000 and balance <= 100000 THEN '50000-100000' WHEN balance > 100000 THEN ' > 100000' END AS balance_group from user_balance ) select balance_group ,count(*) as number_of_users from balance_distribution group by balance_group

Near Distribution