Query editor
WITH pumpswap_trades AS (SELECT *FROM query_4893450WHERE block_time >= TIMESTAMP '2025-03-15'),first_seen AS (SELECTuser,MIN(DATE_TRUNC('week', block_time)) AS first_swap_dayFROM pumpswap_tradesGROUP BY user),swaps_with_user_type AS (SELECTDATE_TRUNC('week', s.block_time) AS day,s.user,CASEWHEN DATE_TRUNC('week', s.block_time) = f.first_swap_day THEN 'new_user'ELSE 'recurring_user'END AS user_typeFROM pumpswap_trades sJOIN first_seen f ON s.user = f.user)SELECTday,COUNT(DISTINCT CASE WHEN user_type = 'new_user' THEN user END) AS new_users,COUNT(DISTINCT CASE WHEN user_type = 'recurring_user' THEN user END) AS recurring_usersFROM swaps_with_user_typeGROUP BY dayORDER BY day DESC;