-- forked from fairlabs / ARB Claimers Categorized @ https://flipsidecrypto.xyz/fairlabs/q/arb-claim-categories-hFtgXB -- forked from ARB_CLAIM_CATEGORIES @ https://flipsidecrypto.xyz/edit/queries/86fafe5e-c14a-484c-a3bc-7b7d3d01e20f -- forked from failed_claims @ https://flipsidecrypto.xyz/edit/queries/851dec24-3155-485b-971b-9bdca321019b -- Users that successfully claimed ARB from the main contract with successful_claims AS ( SELECT TO_ADDRESS as recipient, SYMBOL, SUM(AMOUNT) as amount_claimed FROM arbitrum.core.ez_token_transfers WHERE CONTRACT_ADDRESS = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548') AND FROM_ADDRESS = '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9' GROUP BY recipient, symbol ), -- getting all these claimers ARB transfers claimer_transfers AS ( SELECT att.BLOCK_NUMBER, att.BLOCK_TIMESTAMP, att.TX_HASH, FROM_ADDRESS as claimer, amount_claimed, att.TO_ADDRESS as drop_receiver, COALESCE(AMOUNT,0) as amount_sent FROM arbitrum.core.ez_token_transfers att RIGHT JOIN successful_claims sc ON att.FROM_ADDRESS = sc.recipient WHERE contract_address = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548') ), claimer_receives AS ( SELECT att.BLOCK_NUMBER, att.BLOCK_TIMESTAMP, att.TX_HASH, TO_ADDRESS as claimer, amount_claimed, att.FROM_ADDRESS as sender_to_claimer, COALESCE(AMOUNT, 0) as amount_received FROM arbitrum.core.ez_token_transfers att RIGHT JOIN successful_claims sc ON att.TO_ADDRESS = sc.recipient WHERE contract_address = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548') -- ignore token distributor contract AND sender_to_claimer != '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9' ), --SELECT * FROM claimer_receives WHERE claimer = '0x38f40ec60909b3238837797cd005962fc1fd11a6' -- getting the recipient of their transfers labeled claimer_sends_labeled AS ( SELECT claimer, AVG(amount_claimed) as amount_claimed, SUM(amount_sent) as send_volume, drop_receiver, ADDRESS_NAME, LABEL_TYPE, LABEL_SUBTYPE, PROJECT_NAME FROM claimer_transfers ct LEFT JOIN arbitrum.core.dim_labels adl ON ct.DROP_RECEIVER = adl.ADDRESS GROUP BY claimer, drop_receiver, address_name, label_type, label_subtype, project_name ), claimer_receives_labeled AS ( SELECT claimer, AVG(amount_claimed) as amount_claimed, SUM(amount_received) as receive_volume, sender_to_claimer, ADDRESS_NAME, LABEL_TYPE, LABEL_SUBTYPE, PROJECT_NAME FROM claimer_receives cr LEFT JOIN arbitrum.core.dim_labels adl ON cr.SENDER_TO_CLAIMER = adl.ADDRESS GROUP BY claimer, sender_to_claimer, address_name, label_type, label_subtype, project_name ), -- Looking at sends to known decentralized exchange contracts (e.g., arb/weth pools) dex_sends AS ( SELECT claimer, AVG(AMOUNT_CLAIMED) as amount_claimed, SUM(SEND_VOLUME) as total_send_dex FROM claimer_sends_labeled WHERE label_type = 'dex' GROUP BY claimer ), -- Looking at sends to known decentralized exchange contracts (e.g., arb/weth pools) dex_receives AS ( SELECT claimer, AVG(AMOUNT_CLAIMED) as amount_claimed, SUM(RECEIVE_VOLUME) as total_receive_dex FROM claimer_receives_labeled WHERE label_type = 'dex' GROUP BY claimer ), all_dex AS ( SELECT claimer, amount_claimed, total_send_dex, total_receive_dex FROM dex_sends NATURAL FULL JOIN dex_receives ), -- Looking at sends to known decentralized exchange contracts (e.g., arb/weth pools) cex_sends AS ( SELECT claimer, AVG(AMOUNT_CLAIMED) as amount_claimed, SUM(SEND_VOLUME) as total_send_cex FROM claimer_sends_labeled WHERE label_type = 'cex' GROUP BY claimer ), -- Looking at sends to known decentralized exchange contracts (e.g., arb/weth pools) cex_receives AS ( SELECT claimer, AVG(AMOUNT_CLAIMED) as amount_claimed, SUM(RECEIVE_VOLUME) as total_receive_cex FROM claimer_receives_labeled WHERE label_type = 'cex' GROUP BY claimer ), all_cex AS ( SELECT claimer, amount_claimed, total_send_cex, total_receive_cex FROM cex_sends NATURAL FULL JOIN cex_receives ), other_sends AS ( SELECT claimer, AVG(AMOUNT_CLAIMED) as amount_claimed, SUM(SEND_VOLUME) as total_send_other FROM claimer_sends_labeled WHERE label_type NOT IN ('dex','cex') OR label_type IS NULL GROUP BY claimer ), other_receives AS ( SELECT claimer, AVG(AMOUNT_CLAIMED) as amount_claimed, SUM(RECEIVE_VOLUME) as total_receive_other FROM claimer_receives_labeled WHERE label_type NOT IN ('dex','cex') OR label_type IS NULL GROUP BY claimer ), all_other AS ( SELECT * FROM other_sends NATURAL FULL JOIN other_receives ), claim_all_data AS ( SELECT *, ( COALESCE(total_send_dex,0) + COALESCE(total_receive_dex,0) + COALESCE(total_send_cex ,0) + COALESCE(total_receive_cex,0) + COALESCE(total_send_other,0) + COALESCE(total_receive_other,0) ) as total_action FROM all_dex NATURAL FULL JOIN all_cex NATURAL FULL JOIN all_other ), claimers_categorized AS ( SELECT *, CASE -- Under 1 Arb of total action = Hodler -- note some weird dust accounts doing 0.00000001 Uni v3 actions WHEN COALESCE(total_action,0) < 1 THEN 'HODLer' -- Sending entire claim to dex without receiving from dex or cex or other = Dumper -- using 3 in case dust from dex, cex, other add up. WHEN ABS(COALESCE(total_send_dex,0) - amount_claimed) < 3 AND COALESCE(total_receive_dex, 0) < 1 AND COALESCE(total_receive_cex, 0) < 1 AND COALESCE(total_receive_other, 0) < 1 THEN 'Dumper' -- Sending entire claim to cex without receiving from dex or cex or other = Exchanger WHEN ABS(COALESCE(total_send_cex,0) - amount_claimed) < 3 AND COALESCE(total_receive_dex, 0) < 1 AND COALESCE(total_receive_cex, 0) < 1 AND COALESCE(total_receive_other, 0) < 1 THEN 'Exchanger' -- Sending only part of claim to dex or cex without receiving (or splitting between dex/cex) = Hedger WHEN COALESCE(COALESCE(total_send_dex,0) + COALESCE(total_send_cex, 0), 0) BETWEEN 1 AND (amount_claimed+1) AND COALESCE(total_receive_dex, 0) < 1 AND COALESCE(total_receive_cex, 0) < 1 AND COALESCE(total_receive_other, 0) < 1 THEN 'Hedger' -- Sending more than claim to dex or cex and receiving from dex or cex = Trader WHEN COALESCE(COALESCE(total_send_dex,0) + COALESCE(total_send_cex, 0), 0) > (amount_claimed - 1) -- 1 token buffer AND (COALESCE(total_receive_dex, 0) + COALESCE(total_receive_cex, 0)) > 1 THEN 'Trader' -- Sending claim or more to other w/o receiving from dex or cex = Intermediary (Farmer?) WHEN COALESCE(total_send_other,0) > (amount_claimed - 1) -- 1 token buffer AND COALESCE(total_receive_dex, 0) < 1 AND COALESCE(total_receive_cex, 0) < 1 THEN 'Intermediary' -- Sending claim or more to dex or cex while receiving from only other (possibly Intermediary) = Farmer WHEN (COALESCE(total_send_dex,0) + COALESCE(total_send_cex, 0)) > (amount_claimed - 1) -- 1 token buffer AND COALESCE(total_receive_dex, 0) < 1 AND COALESCE(total_receive_cex, 0) < 1 AND COALESCE(total_receive_other, 0) > 1 THEN 'Farmer' ELSE 'other/mixed actions' END as category FROM claim_all_data ), category_tbl AS ( SELECT category as "Category", count(claimer) as "# Addresses", sum(amount_claimed) as "Total Arb Claimed", FLOOR(avg(amount_claimed)) as "Avg Claim", FLOOR(sum( COALESCE(total_send_dex,0) + COALESCE(total_send_cex,0) + COALESCE(total_send_other,0)) ) as "Send Volume", FLOOR(sum( COALESCE(total_receive_dex,0) + COALESCE(total_receive_cex,0) + COALESCE(total_receive_other,0)) ) as "Receive Volume" FROM claimers_categorized GROUP BY category ) SELECT "Category", "# Addresses", ROUND("# Addresses" / (SELECT SUM("# Addresses") from category_tbl)*100, 2) as "% of Addresses", "Avg Claim", "Total Arb Claimed", ROUND("Total Arb Claimed" / (SELECT SUM("Total Arb Claimed") from category_tbl)*100,2) as "% ARB Claimed", "Send Volume", "Receive Volume" FROM category_tbl ORDER BY "% ARB Claimed" DESC /* -- Fork and use separately to check things SELECT * FROM category_tbl SELECT * FROM claimers_categorized WHERE category = 'undefined' SELECT * FROM claimers_categorized WHERE claimer = '0x38f40ec60909b3238837797cd005962fc1fd11a6' */

Query	Creator	Created
ARB Claimers Categorized copy arbitrum l2 evm	msilb7	Apr 21, 2023
Arbi Tx Comp arbitrum optimism avalanche l2 evm	msilb7	Sep 7, 2022
Transaction Comparisons arbitrum optimism avalanche l2 evm	msilb7	Sep 5, 2022

msilb7