with minted_gob as (SELECT nft_to_address as address, MIN(block_timestamp) as first_mint FROM ethereum.core.ez_nft_transfers WHERE nft_address = '0x60bb1e2aa1c9acafb4d34f71585d7e959f387769' --gobblers contract and nft_from_address = '0x0000000000000000000000000000000000000000' GROUP BY nft_to_address ), max_time_before as (SELECT user_address, symbol, MAX(block_timestamp) as time_before FROM ethereum.core.ez_balance_deltas INNER JOIN minted_gob ON address = user_address WHERE symbol in ('ETH','USDC') AND block_timestamp <= first_mint GROUP BY user_address,symbol ), max_time_after as (SELECT user_address, symbol, MAX(block_timestamp) as time_after FROM ethereum.core.ez_balance_deltas INNER JOIN minted_gob ON address = user_address WHERE symbol in ('ETH','USDC') AND block_timestamp > first_mint GROUP BY user_address,symbol ), before_stats as (SELECT m.user_address, d.symbol, current_bal as current_balance_before FROM ethereum.core.ez_balance_deltas d INNER JOIN max_time_before m ON m.user_address = d.user_address AND time_before = block_timestamp AND m.symbol = d.symbol ), after_stats as (SELECT m.user_address, d.symbol, current_bal as current_balance_after FROM ethereum.core.ez_balance_deltas d INNER JOIN max_time_after m ON m.user_address = d.user_address AND time_after = block_timestamp AND m.symbol = d.symbol ), net_total as (SELECT COALESCE(a.user_address,b.user_address) as address, COALESCE(a.symbol,b.symbol) as symbol, COALESCE(current_balance_after,current_balance_before) as current_balance_after, COALESCE(current_balance_before,.01) as current_balance_before, COALESCE(current_balance_after,current_balance_before) - COALESCE(current_balance_before,0) as net_change FROM before_stats b FULL OUTER JOIN after_stats a ON a.symbol = b.symbol AND a.user_address = b.user_address), checking as (SELECT m.address, symbol, (current_balance_before) as current_balance_before, (current_balance_after) as current_balance_after, net_change, round((current_balance_after - current_balance_before) / current_balance_before,1) as net_change_perc, 1 as counter FROM minted_gob m LEFT JOIN net_total n ON n.address = m.address WHERE symbol = 'ETH' ORDER BY net_change DESC) SELECT *, CASE WHEN net_change_perc < -.8 THEN 'Greater than -80% Net Change' WHEN net_change_perc >= -.8 AND net_change_perc < -.4 THEN '-80% to -40% Net Change' WHEN net_change_perc >= -.4 AND net_change_perc < -.2 THEN '-80% to -40% Net Change' WHEN net_change_perc >= -.2 AND net_change_perc < 0 THEN '-40% to 0 Net Change' WHEN net_change_perc >= 0 AND net_change_perc < .2 THEN '0 to 20% Net Change' WHEN net_change_perc >= .2 AND net_change_perc < .5 THEN '20% to 50% Net Change' WHEN net_change_perc >= .5 AND net_change_perc < .75 THEN '50% to 75% Net Change' WHEN net_change_perc >= .75 AND net_change_perc < 1THEN '75% to 100% Net Change' WHEN net_change_perc >= 1 AND net_change_perc < 1.5 THEN '100% to 150% Net Change' WHEN net_change_perc >= 1.5 AND net_change_perc < 2 THEN '150% to 200% Net Change' WHEN net_change_perc >= 2 AND net_change_perc < 2.5 THEN '200% to 250% Net Change' WHEN net_change_perc >= 2.5 AND net_change_perc < 3 THEN '250% to 300% Net Change' ELSE '300%+ Net Change' END as net_change_cat, CaSE WHEN net_change_perc > 0 THEN 'Positive Net Change' ELSE 'Negative Net Change' END AS positive_negative, CASE WHEN current_balance_before < .25 THEN 'Less Than .25 ETH' WHEN current_balance_before >= .25 AND current_balance_before < .5 THEN '.25 -.5 ETH' WHEN current_balance_before >= .5 AND current_balance_before < 1 THEN '.5-1 ETH' WHEN current_balance_before >= 1 AND current_balance_before < 3 THEN '1-3 ETH' WHEN current_balance_before >= 3 AND current_balance_before < 6 THEN '3-6 ETH' WHEN current_balance_before >= 6 AND current_balance_before < 10 THEN '6-10 ETH' WHEN current_balance_before >= 10 AND current_balance_before < 15 THEN '10-15 ETH' WHEN current_balance_before >= 15 AND current_balance_before < 25 THEN '15-25 ETH' WHEN current_balance_before >= 25 AND current_balance_before < 50 THEN '25-50 ETH' WHEN current_balance_before >= 50 AND current_balance_before < 100 THEN '50-100 ETH' ELSE '100+ ETH' END AS pre_balance_cat FROM checking

Art Gobblers Analysis