WITH analysis_timeframe AS ( SELECT '2024-05-06' AS start_ana_date ,'2024-06-05' AS last_ana_date ) ,eligible_pudgy_holders AS ( SELECT value: "NFT_TO_ADDRESS"::string AS pudgy_holder_address ,value: "HOLD_PUDGY_AMOUNT"::string AS hold_pudgy_amount FROM ( SELECT live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/4a0e48f8-4e91-46c9-922d-95ae8b67f9bb/data/latest') AS response ) ,lateral FLATTEN(input => response: data) ) ,claim_filtered_table AS ( SELECT block_timestamp ,to_address AS claimer_address ,tx_hash ,contract_address AS spec_address ,'75.392038601' AS amount -- hardcoded: only 75.39203860072377 $SPEC per Pudgy Peguine holder's wallet FROM base.core.ez_token_transfers WHERE 0 = 0 AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND from_address = lower('0xadf7c35560035944e805d98ff17d58cde2449389') AND origin_function_signature = '0xbbfb1a0b' -- MerkleClaim AND contract_address = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') -- $SPEC AND to_address IN ( SELECT DISTINCT pudgy_holder_address FROM eligible_pudgy_holders ) ) ,spec_on_chain_sell_from_claimers as ( SELECT block_timestamp ,tx_hash ,origin_from_address as sender_address ,amount_in as spec_amount FROM base.defi.ez_dex_swaps WHERE 0=0 AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND token_in = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') AND origin_from_address in (select distinct claimer_address from claim_filtered_table) ) ,spec_on_chain_bridge_from_claimers as ( SELECT block_timestamp ,tx_hash ,origin_from_address as sender_address ,amount as spec_amount FROM base.defi.ez_bridge_activity WHERE 0=0 AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND token_address = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') AND origin_from_address in (select distinct claimer_address from claim_filtered_table) ) ,spec_cex_transfer_from_claimers as ( SELECT block_timestamp ,tx_hash ,origin_from_address as sender_address ,amount as spec_amount from base.core.ez_token_transfers where 0=0 and contract_address = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') and lower(to_address) in ( select distinct address from base.core.dim_labels where label_type = 'cex' ) AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND origin_from_address in (select distinct claimer_address from claim_filtered_table) ) ,spec_on_chain_lp_from_claimers as ( SELECT block_timestamp ,tx_hash ,origin_from_address as sender_address ,amount as spec_amount ,origin_function_signature from base.core.ez_token_transfers where 0=0 and contract_address = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') and lower(to_address) in ( select distinct pool_address from base.defi.dim_dex_liquidity_pools where 0=0 and tokens:token0 = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') or tokens:token1 = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') UNION select '0xfd7abc461df4e496a25898cab0c6ea88cdd94cf9' as pool_address -- aerodome spec lp UNION select '0x8055e6de251e414e8393b20adab096afb3cf8399' as pool_address -- uniswap spec lp ) AND origin_function_signature in ( '0xb5007d1f' -- mint lp token aerodrome ,'0x88316456' -- mint lp token uniswap ,'0xb7e0d4c0' ) AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND origin_from_address = from_address AND origin_from_address in (select distinct claimer_address from claim_filtered_table) ) ,spec_other_transfer_from_claimers as ( SELECT block_timestamp ,tx_hash ,origin_from_address as sender_address ,amount as spec_amount from base.core.ez_token_transfers where 0=0 and contract_address = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND origin_function_signature = '0xa9059cbb' -- Normal Transfer AND origin_from_address in (select distinct claimer_address from claim_filtered_table) AND tx_hash not in (select distinct tx_hash from spec_cex_transfer_from_claimers) ) ,spec_on_chain_sell_from_claimers_plus as ( /*------ Selling on Paraswap, 0x, 1-inch, Kyberswap ------*/ SELECT block_timestamp ,tx_hash ,origin_from_address as sender_address ,amount as spec_amount FROM base.core.ez_token_transfers WHERE 0 = 0 AND block_timestamp >= ( SELECT start_ana_date FROM analysis_timeframe ) AND block_timestamp <= ( SELECT last_ana_date FROM analysis_timeframe ) AND origin_function_signature NOT IN ( '0xbbfb1a0b' -- MerkleClaim ,'0xa9059cbb' -- Normal Transfer ) AND contract_address = lower('0x96419929d7949d6a801a6909c145c8eef6a40431') -- $SPEC AND origin_from_address = from_address AND origin_from_address in (select distinct claimer_address from claim_filtered_table) AND tx_hash not in (select distinct tx_hash from spec_on_chain_sell_from_claimers) AND tx_hash not in (select distinct tx_hash from spec_on_chain_bridge_from_claimers) AND tx_hash not in (select distinct tx_hash from spec_cex_transfer_from_claimers) AND tx_hash not in (select distinct tx_hash from spec_on_chain_lp_from_claimers) AND tx_hash not in (select distinct tx_hash from claim_filtered_table) UNION ALL SELECT * from spec_on_chain_sell_from_claimers ) ,claim_timeframe as ( select CASE WHEN date_trunc('day', block_timestamp) = '2024-05-06' THEN 'First Day' WHEN date_trunc('day', block_timestamp) >= '2024-05-07' AND date_trunc('day', block_timestamp) < '2024-05-13' THEN '7D' WHEN date_trunc('day', block_timestamp) >= '2024-05-13' AND date_trunc('day', block_timestamp) < '2024-05-20' THEN '14D' WHEN date_trunc('day', block_timestamp) >= '2024-05-20' AND date_trunc('day', block_timestamp) < '2024-06-06' THEN '30D' END AS date_frame ,claimer_address as claim_address ,sum(amount) as total_token_amount from claim_filtered_table group by date_frame, claim_address ) ,tx_timeframe as ( select 'cex-transfer' as action ,CASE WHEN date_trunc('day', block_timestamp) = '2024-05-06' THEN 'First Day' WHEN date_trunc('day', block_timestamp) >= '2024-05-07' AND date_trunc('day', block_timestamp) < '2024-05-13' THEN '7D' WHEN date_trunc('day', block_timestamp) >= '2024-05-13' AND date_trunc('day', block_timestamp) < '2024-05-20' THEN '14D' WHEN date_trunc('day', block_timestamp) >= '2024-05-20' AND date_trunc('day', block_timestamp) < '2024-06-06' THEN '30D' END AS tx_date_frame ,sender_address as tx_wallet_address ,sum(spec_amount) as tx_total_token_amount from spec_cex_transfer_from_claimers group by action, tx_date_frame, tx_wallet_address UNION ALL select 'L1-bridge' as action ,CASE WHEN date_trunc('day', block_timestamp) = '2024-05-06' THEN 'First Day' WHEN date_trunc('day', block_timestamp) >= '2024-05-07' AND date_trunc('day', block_timestamp) < '2024-05-13' THEN '7D' WHEN date_trunc('day', block_timestamp) >= '2024-05-13' AND date_trunc('day', block_timestamp) < '2024-05-20' THEN '14D' WHEN date_trunc('day', block_timestamp) >= '2024-05-20' AND date_trunc('day', block_timestamp) < '2024-06-06' THEN '30D' END AS tx_date_frame ,sender_address as tx_wallet_address ,sum(spec_amount) as tx_total_token_amount from spec_on_chain_bridge_from_claimers group by action, tx_date_frame, tx_wallet_address UNION ALL select 'Provide LP' as action ,CASE WHEN date_trunc('day', block_timestamp) = '2024-05-06' THEN 'First Day' WHEN date_trunc('day', block_timestamp) >= '2024-05-07' AND date_trunc('day', block_timestamp) < '2024-05-13' THEN '7D' WHEN date_trunc('day', block_timestamp) >= '2024-05-13' AND date_trunc('day', block_timestamp) < '2024-05-20' THEN '14D' WHEN date_trunc('day', block_timestamp) >= '2024-05-20' AND date_trunc('day', block_timestamp) < '2024-06-06' THEN '30D' END AS tx_date_frame ,sender_address as tx_wallet_address ,sum(spec_amount) as tx_total_token_amount from spec_on_chain_lp_from_claimers group by action, tx_date_frame, tx_wallet_address UNION ALL select 'Sell' as action ,CASE WHEN date_trunc('day', block_timestamp) = '2024-05-06' THEN 'First Day' WHEN date_trunc('day', block_timestamp) >= '2024-05-07' AND date_trunc('day', block_timestamp) < '2024-05-13' THEN '7D' WHEN date_trunc('day', block_timestamp) >= '2024-05-13' AND date_trunc('day', block_timestamp) < '2024-05-20' THEN '14D' WHEN date_trunc('day', block_timestamp) >= '2024-05-20' AND date_trunc('day', block_timestamp) < '2024-06-06' THEN '30D' END AS tx_date_frame ,sender_address as tx_wallet_address ,sum(spec_amount) as tx_total_token_amount from spec_on_chain_sell_from_claimers_plus group by action, tx_date_frame, tx_wallet_address UNION ALL select 'wallet-transfer' as action ,CASE WHEN date_trunc('day', block_timestamp) = '2024-05-06' THEN 'First Day' WHEN date_trunc('day', block_timestamp) >= '2024-05-07' AND date_trunc('day', block_timestamp) < '2024-05-13' THEN '7D' WHEN date_trunc('day', block_timestamp) >= '2024-05-13' AND date_trunc('day', block_timestamp) < '2024-05-20' THEN '14D' WHEN date_trunc('day', block_timestamp) >= '2024-05-20' AND date_trunc('day', block_timestamp) < '2024-06-06' THEN '30D' END AS tx_date_frame ,sender_address as tx_wallet_address ,sum(spec_amount) as tx_total_token_amount from spec_other_transfer_from_claimers group by action, tx_date_frame, tx_wallet_address ) ,final_aggr_table AS ( SELECT wallet_address ,sum(claim_token_first_date) as total_claim_token_first_date ,sum(claim_token_first_week) as total_claim_token_first_week ,sum(claim_token_second_week) as total_claim_token_second_week ,sum(claim_token_first_month) as total_claim_token_first_month ,sum(tx_token_first_date) as total_tx_token_first_date ,sum(tx_token_first_week) as total_tx_token_first_week ,sum(tx_token_second_week) as total_tx_token_second_week ,sum(tx_token_first_month) as total_tx_token_first_month FROM ( SELECT claim_address as wallet_address ,total_token_amount as claim_token_first_date ,0 as claim_token_first_week ,0 as claim_token_second_week ,0 as claim_token_first_month ,0 as tx_token_first_date ,0 as tx_token_first_week ,0 as tx_token_second_week ,0 as tx_token_first_month FROM claim_timeframe WHERE date_frame = 'First Day' UNION SELECT claim_address as wallet_address ,0 as claim_token_first_date ,total_token_amount as claim_token_first_week ,0 as claim_token_second_week ,0 as claim_token_first_month ,0 as tx_token_first_date ,0 as tx_token_first_week ,0 as tx_token_second_week ,0 as tx_token_first_month FROM claim_timeframe WHERE date_frame = '7D' UNION SELECT claim_address as wallet_address ,0 as claim_token_first_date ,0 as claim_token_first_week ,total_token_amount as claim_token_second_week ,0 as claim_token_first_month ,0 as tx_token_first_date ,0 as tx_token_first_week ,0 as tx_token_second_week ,0 as tx_token_first_month FROM claim_timeframe WHERE date_frame = '14D' UNION SELECT claim_address as wallet_address ,0 as claim_token_first_date ,0 as claim_token_first_week ,0 as claim_token_second_week ,total_token_amount as claim_token_first_month ,0 as tx_token_first_date ,0 as tx_token_first_week ,0 as tx_token_second_week ,0 as tx_token_first_month FROM claim_timeframe WHERE date_frame = '30D' UNION SELECT tx_wallet_address as wallet_address ,0 as claim_token_first_date ,0 as claim_token_first_week ,0 as claim_token_second_week ,0 as claim_token_first_month ,tx_total_token_amount as tx_token_first_date ,0 as tx_token_first_week ,0 as tx_token_second_week ,0 as tx_token_first_month FROM tx_timeframe WHERE tx_date_frame = 'First Day' UNION SELECT tx_wallet_address as wallet_address ,0 as claim_token_first_date ,0 as claim_token_first_week ,0 as claim_token_second_week ,0 as claim_token_first_month ,0 as tx_token_first_date ,tx_total_token_amount as tx_token_first_week ,0 as tx_token_second_week ,0 as tx_token_first_month FROM tx_timeframe WHERE tx_date_frame = '7D' UNION SELECT tx_wallet_address as wallet_address ,0 as claim_token_first_date ,0 as claim_token_first_week ,0 as claim_token_second_week ,0 as claim_token_first_month ,0 as tx_token_first_date ,0 as tx_token_first_week ,tx_total_token_amount as tx_token_second_week ,0 as tx_token_first_month FROM tx_timeframe WHERE tx_date_frame = '14D' UNION SELECT tx_wallet_address as wallet_address ,0 as claim_token_first_date ,0 as claim_token_first_week ,0 as claim_token_second_week ,0 as claim_token_first_month ,0 as tx_token_first_date ,0 as tx_token_first_week ,0 as tx_token_second_week ,tx_total_token_amount as tx_token_first_month FROM tx_timeframe WHERE tx_date_frame = '30D' ) GROUP BY 1 ) ,final_table AS ( SELECT wallet_address as claim_address ,total_claim_token_first_date AS claimed_token_first_date ,total_tx_token_first_date ,total_claim_token_first_date - total_tx_token_first_date AS net_first_date ,total_claim_token_first_date + total_claim_token_first_week AS claimed_token_first_week ,total_tx_token_first_week ,total_claim_token_first_date + total_claim_token_first_week - total_tx_token_first_date - total_tx_token_first_week AS net_first_week ,total_claim_token_first_date + total_claim_token_first_week + total_claim_token_second_week AS claimed_token_second_week ,total_tx_token_second_week ,total_claim_token_first_date + total_claim_token_first_week - total_tx_token_first_date - total_tx_token_first_week + total_claim_token_second_week - total_tx_token_second_week AS net_second_week ,total_claim_token_first_date + total_claim_token_first_week + total_claim_token_second_week + total_claim_token_first_month AS claimed_token_first_month ,total_tx_token_first_month ,total_claim_token_first_date + total_claim_token_first_week - total_tx_token_first_date - total_tx_token_first_week + total_claim_token_second_week - total_tx_token_second_week + total_claim_token_first_month - total_tx_token_first_month AS net_first_month FROM final_aggr_table ) SELECT 'a. D-1' AS timeframe ,CASE WHEN claimed_token_first_date > 0 AND claimed_token_first_date = net_first_date THEN 'HODLers' WHEN claimed_token_first_date > 0 AND net_first_date > 0 AND claimed_token_first_date > net_first_date THEN 'Partially Moved/Sold' ELSE 'Fully Moved/Sold' END AS action_types ,count(DISTINCT claim_address) as total_number_wallet FROM final_table WHERE claimed_token_first_date > 0 GROUP BY 2 UNION ALL SELECT 'b. 7days' AS timeframe ,CASE WHEN claimed_token_first_week > 0 AND claimed_token_first_week = net_first_week THEN 'HODLers' WHEN claimed_token_first_week > 0 AND net_first_week > 0 AND claimed_token_first_week > net_first_week THEN 'Partially Moved/Sold' ELSE 'Fully Moved/Sold' END AS action_types ,count(DISTINCT claim_address) as total_number_wallet FROM final_table WHERE claimed_token_first_week > 0 GROUP BY 2 UNION ALL SELECT 'c. 14days' AS timeframe ,CASE WHEN claimed_token_second_week > 0 AND claimed_token_second_week = net_second_week THEN 'HODLers' WHEN claimed_token_second_week > 0 AND net_second_week > 0 AND claimed_token_second_week > net_second_week THEN 'Partially Moved/Sold' ELSE 'Fully Moved/Sold' END AS action_types ,count(DISTINCT claim_address) as total_number_wallet FROM final_table WHERE claimed_token_second_week > 0 GROUP BY 2 UNION ALL SELECT 'd. 30days' AS timeframe ,CASE WHEN claimed_token_first_month > 0 AND claimed_token_first_month = net_first_month THEN 'HODLers' WHEN claimed_token_first_month > 0 AND net_first_month > 0 AND claimed_token_first_month > net_first_month THEN 'Partially Moved/Sold' ELSE 'Fully Moved/Sold' END AS action_types ,count(DISTINCT claim_address) as total_number_wallet FROM final_table WHERE claimed_token_first_month > 0 GROUP BY 2

Insights into Pudgy Penguins Community $SPEC airdrop