-- 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) < (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' */