WITH txns as ( -- +INFLOW SELECT TX_RECEIVER as wallet, SUM(deposit / pow(10, 24)) as val FROM near.core.fact_transfers WHERE STATUS = 'TRUE' GROUP BY 1 -- -OUTFLOW UNION ALL SELECT TX_SIGNER as wallet, -1 * SUM(deposit / pow(10, 24)) as val FROM near.core.fact_transfers WHERE STATUS = 'TRUE' GROUP BY 1 -- -TX Cost UNION ALL SELECT TX_SIGNER as wallet, -1 * SUM(transaction_fee / pow(10,24)) as val FROM near.core.fact_transfers WHERE STATUS = 'TRUE' GROUP BY 1 -- +Swap To NEAR (Increases NEAR balance) UNION ALL SELECT TRADER as wallet, SUM(AMOUNT_OUT) as val FROM near.core.ez_dex_swaps WHERE TOKEN_OUT = 'NEAR' GROUP BY 1 -- -Swap From NEAR (Reduces NEAR balance) UNION ALL SELECT TRADER as wallet, -1 * SUM(AMOUNT_IN) as val FROM near.core.ez_dex_swaps WHERE TOKEN_IN = 'NEAR' GROUP BY 1 -- +UnStake UNION ALL SELECT tx.TX_signer as wallet, SUM(TRY_PARSE_JSON(fc.ARGS):MIN_EXPECTED_NEAR::INTEGER / POW(10, 24)) as val FROM near.core.fact_transactions tx JOIN near.core.fact_actions_events_function_call fc ON fc.tx_hash = tx.tx_hash WHERE (METHOD_NAME ilike 'liquid_unstake' or METHOD_NAME ilike 'instant_unstake') GROUP BY 1 HAVING val > 0 -- -Stake UNION ALL SELECT tx.TX_signer as wallet, -1 * SUM(fc.deposit / POW(10, 24)) as val FROM near.core.fact_transactions tx JOIN near.core.fact_actions_events_function_call fc ON fc.tx_hash = tx.tx_hash WHERE METHOD_NAME IN ('deposit_and_stake', 'stake') GROUP BY 1 HAVING val > 0 -- +Multicall (NEAR Added to the wallet) UNION ALL SELECT TRY_PARSE_JSON(ac.VALUE):ACCOUNT_ID::STRING as wallet, SUM(TRY_PARSE_JSON(ac.VALUE):AMOUNT::INTEGER / POW(10, 24)) AS val FROM NEAR.CORE.FACT_ACTIONS_EVENTS_FUNCTION_CALL, table(flatten(TRY_PARSE_JSON(NEAR.CORE.FACT_ACTIONS_EVENTS_FUNCTION_CALL.ARGS):ACCOUNTS)) ac WHERE METHOD_NAME IN ('multisend_from_balance', 'multisend_from_balance_unsafe') GROUP BY 1 ), balances as ( SELECT wallet, SUM(val) as balance FROM txns group by wallet having balance > 0 ) select count(distinct wallet) as n_wallets, CASE WHEN balance > 0.1 and balance <= 0.5 THEN 'b.0.1-0.5' WHEN balance > 0.5 and balance <= 1 THEN 'c.0.5-1' WHEN balance > 1 and balance <= 5 THEN 'd.1-5' WHEN balance > 5 and balance <= 10 THEN 'e.5-10' WHEN balance > 10 and balance <= 50 THEN 'f.10-50' WHEN balance > 50 and balance <= 100 THEN 'g.50-100' WHEN balance > 100 and balance <= 500 THEN 'h.100-500' WHEN balance > 500 and balance <= 1000 THEN 'i.500-1K' WHEN balance > 1000 and balance <= 5000 THEN 'j.1K-5K' WHEN balance > 5000 and balance <= 10000 THEN 'k.5K-10K' WHEN balance > 10000 and balance <= 100000 THEN 'l.10K-100K' WHEN balance > 100000 THEN 'm.> 100K' END as category from balances where category is not null group by category order by category /* with tab1 as ( select tx_signer, sum(deposit/power(10,24)) as Amount_sent from near.core.fact_transfers group by 1 ), tab2 as ( select TX_RECEIVER, sum(deposit/power(10,24)) as Amount_recieved from near.core.fact_transfers group by 1 ) , tab3 as ( select tx_signer, amount_recieved - amount_sent as Net_NEAR from tab1 left outer join tab2 on tx_signer = TX_RECEIVER having net_NEAR is not null and tx_signer not like '%lockup%' and tx_signer not like 'nfeco%' and tx_signer not like 'nfendowment%' and tx_signer not like 'aurora' and tx_signer not like 'kucoinc.near' and tx_signer not like 'binance1.near' and tx_signer not like 'marketplace.paras.near' and tx_signer not like 'bitkubhwallet.near' and tx_signer not like 'app.nearcrowd.near' and net_near < 45641694.4836753 order by 2 desc limit 20 ) select address_name as "Address Name", count(*) as "Transfers Sent", sum(deposit/power(10,24)) as "Volume Sent" from near.core.fact_transfers left outer join near.core.dim_address_labels on TX_RECEIVER = address where tx_signer in (select tx_signer from tab3) -- They are the tx_signer, thus sending a transfer and address_name is not null group by 1 order by 2 desc limit 100 */ /* with tab1 as ( select tx_signer, sum(deposit/power(10,24)) as Amount_sent from near.core.fact_transfers group by 1 ), tab2 as ( select TX_RECEIVER, sum(deposit/power(10,24)) as Amount_recieved from near.core.fact_transfers group by 1 ) , tab3 as ( select tx_signer, amount_recieved - amount_sent as Net_NEAR from tab1 left outer join tab2 on tx_signer = TX_RECEIVER having net_NEAR is not null and tx_signer not like '%lockup%' and tx_signer not like 'nfeco%' and tx_signer not like 'nfendowment%' and tx_signer not like 'aurora' and tx_signer not like 'kucoinc.near' and tx_signer not like 'binance1.near' and tx_signer not like 'marketplace.paras.near' and tx_signer not like 'bitkubhwallet.near' and tx_signer not like 'app.nearcrowd.near' and net_near < 45641694.4836753 order by 2 desc limit 20 ) select case when TX_RECEIVER in (select tx_signer from tab3) then 'Recieved' when tx_signer in (select tx_signer from tab3) then 'Sent' END as users, count(*), avg(deposit/power(10, 24)) from near.core.fact_transfers group by 1 having users is not null */ /* with tab1 as ( SELECT tx_signer, sum(case when deposit / power(10, 18) is NULL then 0 else deposit / power(10, 18) end) as tx_out FROM near.core.fact_transfers GROUP BY 1 ), tab2 as ( SELECT TX_RECEIVER, sum(case when deposit / power(10, 18) is NULL then 0 else deposit / power(10, 18) end) as tx_in FROM near.core.fact_transfers GROUP BY 1 ) SELECT *, tx_in - tx_out as bal FROM tab2 LEFT OUTER JOIN tab1 on tx_signer = TX_RECEIVER HAVING not bal is NULL ORDER BY bal DESC LIMIT 100 */ /* -- forked from 60eda717-12c8-42f9-a1cd-a61aab79623c with tb AS ( select TX_SIGNER AS user, - sum(DEPOSIT / 1e24) AS amount from near.core.fact_transfers group by 1 union ALL select TX_SIGNER AS user, - sum(tx:actions[0]:FunctionCall:deposit / pow(10, 24)) AS amount from near.core.fact_actions_events_function_call A inner join near.core.fact_transactions B on A.TX_HASH = B.TX_HASH where METHOD_NAME in ('stake', 'deposit_and_stake') group by 1 union ALL select TRADER AS user, - sum(AMOUNT_OUT) AS amount from near.core.ez_dex_swaps where TOKEN_OUT in ('stNEAR', 'NEAR', 'wNEAR') group by 1 union ALL select TX_SIGNER AS user, - sum(TRANSACTION_FEE / 1e24) AS amount from near.core.fact_transactions group by 1 union ALL select TX_RECEIVER User, sum(DEPOSIT / 1e24) AS amount from near.core.fact_transfers group by 1 union ALL select TRADER AS user, sum(AMOUNT_IN) AS amount from near.core.ez_dex_swaps where TOKEN_IN in ('stNEAR', 'NEAR', 'wNEAR') group by 1 union ALL select TX_SIGNER AS user, sum(try_parse_json(args):balance / 1e24) AS Amount from near.core.fact_actions_events_function_call A inner join near.core.fact_transactions B on A.TX_HASH = B.TX_HASH where METHOD_NAME in ('unstake') and try_parse_json(args):balance LIKE '%[^0-9]%' group by 1 ), abc as (select user AS address, dal.address as dal_address, sum(amount) AS Balance from tb left join near.core.dim_address_labels dal on tb.user = dal.address where dal.address is null group by 1,2 order by 3 desc) select address, balance from abc where address not like '%pool%' AND address not like '%staking%' AND address not like '%deposit%' and address not like '%withdraw%' AND address not like '%lockup%' and address not like '%safe%' AND address not like '%binance%' and address not like '%ftx%' AND address not like '%kucoin%' and address not like '%coinbase%' AND address not like '%huobi%' and address not like '%kraken%' AND address not like '%dao%' and address not like '%reserve%' AND address not like '%farm%' and address not like '%app%' AND address not like '%finance%' and address not like '%defi%' order by 2 desc limit 20 */ /* select t1.users, sum(t1.Aamount_in - t2.Amount_out) as User_Hold from ( select tx_receiver as users, sum(deposit / pow(10, 24)) as Aamount_in from near.core.fact_transfers group by users ) as t1 inner join ( select tx_signer as users, sum(deposit / pow(10, 24)) as Amount_out from near.core.fact_transfers group by users ) as t2 on t1.users = t2.users group by t1.users having User_Hold > 0 order by User_Hold desc limit 50 */ /* select tx_receiver as users, sum(deposit / pow(10, 24)) as Aamount_in from near.core.fact_transfers group by users having Aamount_in > 0 order by Aamount_in desc limit 50 */ /* select t1.users, sum(Amount_In - Amount_Out) as User_Balance from ( select trader as users, sum(amount_in) as Amount_In from near.core.ez_dex_swaps where token_in in ('stNEAR', 'NEAR', 'wNEAR') group by users ) as t1 inner join ( select trader as users, sum(amount_out) as Amount_Out from near.core.ez_dex_swaps where token_out in ('stNEAR', 'NEAR', 'wNEAR') group by users ) as t2 on t1.users = t2.users group by t1.users having User_Balance > 0 limit 20 */ /* select trader as users, sum(amount_out) as Swap_Out from near.core.ez_dex_swaps where token_out in ('stNEAR', 'NEAR', 'wNEAR') group by users having Swap_Out > 0 ORDER by Swap_Out DESC limit 20 */ /* select date_trunc('day', block_timestamp) as Day, date_trunc('month', block_timestamp) as Month, tx_receiver, count(*) as Transfer_Count, sum(deposit / pow(10, 24)) as Amount, sum(TRANSACTION_FEE / pow(10, 24)) as transaction_fee from near.core.fact_transfers where tx_receiver in ( select users from ( select users, sum(User_Hold) as User_Hold from ( select t1.users, sum(t1.Aamount_in - t2.Amount_out) as User_Hold from ( select tx_receiver as users, sum(deposit / pow(10, 24)) as Aamount_in from near.core.fact_transfers group by users ) as t1 inner join ( select tx_signer as users, sum(deposit / pow(10, 24)) as Amount_out from near.core.fact_transfers group by users ) as t2 on t1.users = t2.users group by t1.users HAVING User_Hold > 0 UNION select t1.users, sum(Amount_In - Amount_Out) as User_Hold from ( select trader as users, sum(amount_in) as Amount_In from near.core.ez_dex_swaps where token_in in ('stNEAR', 'NEAR', 'wNEAR') group by users ) as t1 inner join ( select trader as users, sum(amount_out) as Amount_Out from near.core.ez_dex_swaps where token_out in ('stNEAR', 'NEAR', 'wNEAR') group by users ) as t2 on t1.users = t2.users group by t1.users HAVING User_Hold > 0 ) group by users having sum(User_Hold) > 0 order by User_Hold DESC limit 20 ) ) group by Day, Month, tx_receiver */ /* select tx_receiver, count(*) as Transfer_Count, sum(deposit / pow(10, 24)) as Amount, sum(TRANSACTION_FEE / pow(10, 24)) as transaction_fee from near.core.fact_transfers where tx_receiver in ( select users from ( select users, sum(User_Hold) as User_Hold from ( select t1.users, sum(t1.Aamount_in - t2.Amount_out) as User_Hold from ( select tx_receiver as users, sum(deposit / pow(10, 24)) as Aamount_in from near.core.fact_transfers group by users ) as t1 inner join ( select tx_signer as users, sum(deposit / pow(10, 24)) as Amount_out from near.core.fact_transfers group by users ) as t2 on t1.users = t2.users group by t1.users HAVING User_Hold > 0 UNION select t1.users, sum(Amount_In - Amount_Out) as User_Hold from ( select trader as users, sum(amount_in) as Amount_In from near.core.ez_dex_swaps where token_in in ('stNEAR', 'NEAR', 'wNEAR') group by users ) as t1 inner join ( select trader as users, sum(amount_out) as Amount_Out from near.core.ez_dex_swaps where token_out in ('stNEAR', 'NEAR', 'wNEAR') group by users ) as t2 on t1.users = t2.users group by t1.users HAVING User_Hold > 0 ) group by users having sum(User_Hold) > 0 order by User_Hold DESC limit 20 ) ) group by tx_receiver */ /* with tab1 as ( SELECT tx_signer, sum(case when deposit / power(10, 18) is NULL then 0 else deposit / power(10, 18) end) as tx_out FROM near.core.fact_transfers GROUP BY 1 ), tab2 as ( SELECT TX_RECEIVER, sum(case when deposit / power(10, 18) is NULL then 0 else deposit / power(10, 18) end) as tx_in FROM near.core.fact_transfers GROUP BY 1 ) SELECT *, tx_in - tx_out as bal FROM tab2 LEFT OUTER JOIN tab1 on tx_signer = TX_RECEIVER HAVING not bal is NULL ORDER BY bal DESC LIMIT 100 */ /* with final_data as ( select to_date(block_timestamp) as date, TX_SIGNER, count( distinct TX_HASH) as tx_hash_count, sum(deposit/pow(10,24)) as deposit_amount from near.core.fact_transfers where TX_SIGNER='aurora' and STATUS=1 group by tx_signer, date ) select * from final_data */ /* with final_data as ( select to_date(block_timestamp) as date, TX_RECEIVER, count( distinct TX_HASH) as transfer_count, sum(deposit/pow(10,24)) as deposit_amount from near.core.fact_transfers where TX_RECEIVER='aurora.pool.near' and status = 1 group by date, tx_receiver ) select * from final_data */ /* with tab1 as ( select tx_signer, sum(deposit/power(10,24)) as Amount_sent from near.core.fact_transfers group by 1 ), tab2 as ( select TX_RECEIVER, sum(deposit/power(10,24)) as Amount_recieved from near.core.fact_transfers group by 1 ) select tx_signer, amount_recieved - amount_sent as Net_NEAR from tab1 left outer join tab2 on tx_signer = TX_RECEIVER having net_NEAR is not null and tx_signer not like '%lockup%' and tx_signer not like 'nfeco%' and tx_signer not like 'nfendowment%' and tx_signer not like 'aurora' and tx_signer not like 'kucoinc.near' and tx_signer not like 'binance1.near' and tx_signer not like 'marketplace.paras.near' and tx_signer not like 'bitkubhwallet.near' and tx_signer not like 'app.nearcrowd.near' and net_near < 45641694.4836753 order by 2 desc limit 20 with final_data as ( select to_date(block_timestamp) as date, TX_RECEIVER, count( distinct TX_HASH) as transfer_count, sum(deposit/pow(10,24)) as deposit_amount from near.core.fact_transfers where TX_RECEIVER='aurora.pool.near' and status = 1 group by date, tx_receiver ) select * from final_data ith amount as ( SELECT TX_RECEIVER, count( distinct TX_HASH) as transfer_count, sum(deposit/pow(10,24)) as amount_out FROM near.core.fact_transfers GROUP BY 1 ), tab2 as ( SELECT TX_RECEIVER, sum(case when deposit / power(10, 18) is NULL then 0 else deposit / power(10, 18) end) as tx_in FROM near.core.fact_transfers GROUP BY 1 ) SELECT *, tx_in - tx_out as bal FROM tab2 LEFT OUTER JOIN tab1 on tx_signer = TX_RECEIVER HAVING not bal is NULL ORDER BY bal DESC LIMIT 100 with tab1 as ( select tx_signer, sum(deposit/power(10,24)) as Amount_sent from near.core.fact_transfers group by 1 ), tab2 as ( select TX_RECEIVER, sum(deposit/power(10,24)) as Amount_recieved from near.core.fact_transfers group by 1 ) select tx_signer, amount_recieved - amount_sent as Net_NEAR from tab1 left outer join tab2 on tx_signer = TX_RECEIVER having net_NEAR is not null -- and tx_signer not like '%lockup%' and tx_signer not like 'nfeco%' and tx_signer not like 'nfendowment%' and tx_signer not like 'aurora' and tx_signer not like 'kucoinc.near' -- and tx_signer not like 'binance1.near' and tx_signer not like 'marketplace.paras.near' and tx_signer not like 'bitkubhwallet.near' and tx_signer not like 'app.nearcrowd.near' -- and net_near < 45641694.4836753 order by 2 desc limit 20 */

sima_farnoud