-- forked from theericstone-pine / Optimism Airdrop Tracking @ https://flipsidecrypto.xyz/theericstone-pine/q/2023-02-08-06-13-pm-785Pf4 -- 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" OP from the main contract -- Forked from Arbitrum; OP just sent it direct with successful_claims AS ( SELECT TO_ADDRESS as recipient, SUM(AMOUNT) as amount_claimed FROM optimism.core.ez_token_transfers where block_timestamp > '2023-02-08' and from_address = lower('0x2501c477D0A35545a387Aa4A3EEe4292A9a8B3F0') and contract_address = lower('0x4200000000000000000000000000000000000042') GROUP BY recipient ), -- getting all these claimers OP transfers claimer_transfers AS ( SELECT ott.BLOCK_NUMBER, ott.BLOCK_TIMESTAMP, ott.TX_HASH, FROM_ADDRESS as claimer, amount_claimed, ott.TO_ADDRESS as drop_receiver, COALESCE(AMOUNT,0) as amount_sent FROM optimism.core.ez_token_transfers ott RIGHT JOIN successful_claims sc ON ott.FROM_ADDRESS = sc.recipient WHERE contract_address = lower('0x4200000000000000000000000000000000000042') ), claimer_receives AS ( SELECT ott.BLOCK_NUMBER, ott.BLOCK_TIMESTAMP, ott.TX_HASH, TO_ADDRESS as claimer, amount_claimed, ott.FROM_ADDRESS as sender_to_claimer, COALESCE(AMOUNT, 0) as amount_received FROM optimism.core.ez_token_transfers ott RIGHT JOIN successful_claims sc ON ott.TO_ADDRESS = sc.recipient WHERE contract_address = lower('0x4200000000000000000000000000000000000042') -- ignore token distributor contract AND sender_to_claimer != lower('0x2501c477D0A35545a387Aa4A3EEe4292A9a8B3F0') ), -- 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 optimism.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 optimism.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 OP 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 OP Claimed", ROUND("Total OP Claimed" / (SELECT SUM("Total OP Claimed") from category_tbl)*100,2) as "% OP Claimed", "Send Volume", "Receive Volume" FROM category_tbl ORDER BY "% OP 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' */

OP #2 Drop Review