WITH monthly_user_data AS ( SELECT recipient AS wallet_address, date_trunc('month', block_timestamp::date)::date AS month, COUNT(*) AS transaction_count, AVG(ABS(amount0_usd)) AS avg_volume_usd FROM ethereum.uniswapv3.ez_swaps WHERE month >= '2023-01-01' AND month <= '2023-04-01' GROUP BY month, wallet_address ), user_personas AS ( SELECT month, wallet_address, CASE WHEN transaction_count < 5 AND avg_volume_usd < 500 THEN 'Hobbyist Trader' WHEN transaction_count > 20 AND avg_volume_usd > 500 THEN 'Professional Trader' WHEN transaction_count < 5 AND avg_volume_usd > 5000 THEN 'Investor' WHEN transaction_count <= 3 THEN 'Newbie' ELSE 'Other' END AS user_persona FROM monthly_user_data ), --------------------------------------------------------------------------------------------------------- userActiveMonths_HOBBYIST AS ( SELECT recipient as wallet_address, date_trunc('month', min(block_timestamp)::date)::date AS start_month, date_trunc('month', max(block_timestamp)::date)::date AS end_month, datediff(month, start_month, end_month) AS active_months, LISTAGG(DISTINCT date_trunc('month', block_timestamp)::date, ',') AS active_months_list FROM ethereum.uniswapv3.ez_swaps WHERE wallet_address IN (SELECT wallet_address FROM user_personas WHERE user_persona = 'Hobbyist Trader') -- Filter only new users belonging to 'Hobbyist Trader' persona GROUP BY wallet_address ), splitActiveMonths_HOBBYIST AS ( SELECT wallet_address, value::date AS active_month FROM userActiveMonths_HOBBYIST, TABLE(SPLIT_TO_TABLE(active_months_list, ',')) -- Split the active_months_list into rows ), monthlyRetention_HOBBYIST AS ( SELECT MONTHNAME(start_month) AS month, COUNT(start_month) AS new_users, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_HOBBYIST WHERE splitActiveMonths_HOBBYIST.wallet_address = userActiveMonths_HOBBYIST.wallet_address AND splitActiveMonths_HOBBYIST.active_month = userActiveMonths_HOBBYIST.start_month) THEN 1 END) AS M0, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_HOBBYIST WHERE splitActiveMonths_HOBBYIST.wallet_address = userActiveMonths_HOBBYIST.wallet_address AND splitActiveMonths_HOBBYIST.active_month = ADD_MONTHS(userActiveMonths_HOBBYIST.start_month, 1)) THEN 1 END) AS M1, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_HOBBYIST WHERE splitActiveMonths_HOBBYIST.wallet_address = userActiveMonths_HOBBYIST.wallet_address AND splitActiveMonths_HOBBYIST.active_month = ADD_MONTHS(userActiveMonths_HOBBYIST.start_month, 2)) THEN 1 END) AS M2, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_HOBBYIST WHERE splitActiveMonths_HOBBYIST.wallet_address = userActiveMonths_HOBBYIST.wallet_address AND splitActiveMonths_HOBBYIST.active_month = ADD_MONTHS(userActiveMonths_HOBBYIST.start_month, 3)) THEN 1 END) AS M3, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_HOBBYIST WHERE splitActiveMonths_HOBBYIST.wallet_address = userActiveMonths_HOBBYIST.wallet_address AND splitActiveMonths_HOBBYIST.active_month = ADD_MONTHS(userActiveMonths_HOBBYIST.start_month, 4)) THEN 1 END) AS M4, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_HOBBYIST WHERE splitActiveMonths_HOBBYIST.wallet_address = userActiveMonths_HOBBYIST.wallet_address AND splitActiveMonths_HOBBYIST.active_month = ADD_MONTHS(userActiveMonths_HOBBYIST.start_month, 5)) THEN 1 END) AS M5 FROM userActiveMonths_HOBBYIST WHERE start_month >= '2023-01-01' AND start_month <= '2023-04-01' GROUP BY start_month ORDER BY start_month ), retentionRate_HOBBYIST AS ( SELECT month, new_users, M0 / CAST(new_users AS FLOAT) * 100 AS M0, M1 / CAST(new_users AS FLOAT) * 100 AS M1, M2 / CAST(new_users AS FLOAT) * 100 AS M2, M3 / CAST(new_users AS FLOAT) * 100 AS M3, M4 / CAST(new_users AS FLOAT) * 100 AS M4, M5 / CAST(new_users AS FLOAT) * 100 AS M5 FROM monthlyRetention_HOBBYIST ), avgRetention_HOBBYIST AS ( SELECT 'Hobbyist Trader' as user_type, AVG(M0) as m0, AVG(M1) as m1, AVG(M2) as m2, AVG(M3) as m3, AVG(M4) as m4, AVG(M5) as m5 FROM retentionRate_HOBBYIST ), --------------------------------------------------------------------------------------------------------- userActiveMonths_Professional AS ( SELECT recipient as wallet_address, date_trunc('month', min(block_timestamp)::date)::date AS start_month, date_trunc('month', max(block_timestamp)::date)::date AS end_month, datediff(month, start_month, end_month) AS active_months, LISTAGG(DISTINCT date_trunc('month', block_timestamp)::date, ',') AS active_months_list FROM ethereum.uniswapv3.ez_swaps WHERE wallet_address IN (SELECT wallet_address FROM user_personas WHERE user_persona = 'Professional Trader') -- Filter only new users belonging to 'Hobbyist Trader' persona GROUP BY wallet_address ), splitActiveMonths_Professional AS ( SELECT wallet_address, value::date AS active_month FROM userActiveMonths_Professional, TABLE(SPLIT_TO_TABLE(active_months_list, ',')) -- Split the active_months_list into rows ), monthlyRetention_Professional AS ( SELECT MONTHNAME(start_month) AS month, COUNT(start_month) AS new_users, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Professional WHERE splitActiveMonths_Professional.wallet_address = userActiveMonths_Professional.wallet_address AND splitActiveMonths_Professional.active_month = userActiveMonths_Professional.start_month) THEN 1 END) AS M0, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Professional WHERE splitActiveMonths_Professional.wallet_address = userActiveMonths_Professional.wallet_address AND splitActiveMonths_Professional.active_month = ADD_MONTHS(userActiveMonths_Professional.start_month, 1)) THEN 1 END) AS M1, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Professional WHERE splitActiveMonths_Professional.wallet_address = userActiveMonths_Professional.wallet_address AND splitActiveMonths_Professional.active_month = ADD_MONTHS(userActiveMonths_Professional.start_month, 2)) THEN 1 END) AS M2, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Professional WHERE splitActiveMonths_Professional.wallet_address = userActiveMonths_Professional.wallet_address AND splitActiveMonths_Professional.active_month = ADD_MONTHS(userActiveMonths_Professional.start_month, 3)) THEN 1 END) AS M3, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Professional WHERE splitActiveMonths_Professional.wallet_address = userActiveMonths_Professional.wallet_address AND splitActiveMonths_Professional.active_month = ADD_MONTHS(userActiveMonths_Professional.start_month, 4)) THEN 1 END) AS M4, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Professional WHERE splitActiveMonths_Professional.wallet_address = userActiveMonths_Professional.wallet_address AND splitActiveMonths_Professional.active_month = ADD_MONTHS(userActiveMonths_Professional.start_month, 5)) THEN 1 END) AS M5 FROM userActiveMonths_Professional WHERE start_month >= '2023-01-01' AND start_month <= '2023-04-01' GROUP BY start_month ORDER BY start_month ), retentionRate_Professional AS ( SELECT month, new_users, M0 / CAST(new_users AS FLOAT) * 100 AS M0, M1 / CAST(new_users AS FLOAT) * 100 AS M1, M2 / CAST(new_users AS FLOAT) * 100 AS M2, M3 / CAST(new_users AS FLOAT) * 100 AS M3, M4 / CAST(new_users AS FLOAT) * 100 AS M4, M5 / CAST(new_users AS FLOAT) * 100 AS M5 FROM monthlyRetention_Professional ), avgRetention_Professional AS ( SELECT 'Professional Trader' as user_type, AVG(M0) as m0, AVG(M1) as m1, AVG(M2) as m2, AVG(M3) as m3, AVG(M4) as m4, AVG(M5) as m5 FROM retentionRate_Professional ), ---------------------------------------------------------------------------------------------------- userActiveMonths_Investor AS ( SELECT recipient as wallet_address, date_trunc('month', min(block_timestamp)::date)::date AS start_month, date_trunc('month', max(block_timestamp)::date)::date AS end_month, datediff(month, start_month, end_month) AS active_months, LISTAGG(DISTINCT date_trunc('month', block_timestamp)::date, ',') AS active_months_list FROM ethereum.uniswapv3.ez_swaps WHERE wallet_address IN (SELECT wallet_address FROM user_personas WHERE user_persona = 'Investor') -- Filter only new users belonging to 'Hobbyist Trader' persona GROUP BY wallet_address ), splitActiveMonths_Investor AS ( SELECT wallet_address, value::date AS active_month FROM userActiveMonths_Investor, TABLE(SPLIT_TO_TABLE(active_months_list, ',')) -- Split the active_months_list into rows ), monthlyRetention_Investor AS ( SELECT MONTHNAME(start_month) AS month, COUNT(start_month) AS new_users, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Investor WHERE splitActiveMonths_Investor.wallet_address = userActiveMonths_Investor.wallet_address AND splitActiveMonths_Investor.active_month = userActiveMonths_Investor.start_month) THEN 1 END) AS M0, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Investor WHERE splitActiveMonths_Investor.wallet_address = userActiveMonths_Investor.wallet_address AND splitActiveMonths_Investor.active_month = ADD_MONTHS(userActiveMonths_Investor.start_month, 1)) THEN 1 END) AS M1, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Investor WHERE splitActiveMonths_Investor.wallet_address = userActiveMonths_Investor.wallet_address AND splitActiveMonths_Investor.active_month = ADD_MONTHS(userActiveMonths_Investor.start_month, 2)) THEN 1 END) AS M2, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Investor WHERE splitActiveMonths_Investor.wallet_address = userActiveMonths_Investor.wallet_address AND splitActiveMonths_Investor.active_month = ADD_MONTHS(userActiveMonths_Investor.start_month, 3)) THEN 1 END) AS M3, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Investor WHERE splitActiveMonths_Investor.wallet_address = userActiveMonths_Investor.wallet_address AND splitActiveMonths_Investor.active_month = ADD_MONTHS(userActiveMonths_Investor.start_month, 4)) THEN 1 END) AS M4, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Investor WHERE splitActiveMonths_Investor.wallet_address = userActiveMonths_Investor.wallet_address AND splitActiveMonths_Investor.active_month = ADD_MONTHS(userActiveMonths_Investor.start_month, 5)) THEN 1 END) AS M5 FROM userActiveMonths_Investor WHERE start_month >= '2023-01-01' AND start_month <= '2023-04-01' GROUP BY start_month ORDER BY start_month ), retentionRate_Investor AS ( SELECT month, new_users, M0 / CAST(new_users AS FLOAT) * 100 AS M0, M1 / CAST(new_users AS FLOAT) * 100 AS M1, M2 / CAST(new_users AS FLOAT) * 100 AS M2, M3 / CAST(new_users AS FLOAT) * 100 AS M3, M4 / CAST(new_users AS FLOAT) * 100 AS M4, M5 / CAST(new_users AS FLOAT) * 100 AS M5 FROM monthlyRetention_Investor ), avgRetention_Investor AS ( SELECT 'Investor' as user_type, AVG(M0) as m0, AVG(M1) as m1, AVG(M2) as m2, AVG(M3) as m3, AVG(M4) as m4, AVG(M5) as m5 FROM retentionRate_Investor ), ------------------------------------------------------------------------------------------------------- userActiveMonths_Newbie AS ( SELECT recipient as wallet_address, date_trunc('month', min(block_timestamp)::date)::date AS start_month, date_trunc('month', max(block_timestamp)::date)::date AS end_month, datediff(month, start_month, end_month) AS active_months, LISTAGG(DISTINCT date_trunc('month', block_timestamp)::date, ',') AS active_months_list FROM ethereum.uniswapv3.ez_swaps WHERE wallet_address IN (SELECT wallet_address FROM user_personas WHERE user_persona = 'Newbie') -- Filter only new users belonging to 'Newbie Trader' persona GROUP BY wallet_address ), splitActiveMonths_Newbie AS ( SELECT wallet_address, value::date AS active_month FROM userActiveMonths_Newbie, TABLE(SPLIT_TO_TABLE(active_months_list, ',')) -- Split the active_months_list into rows ), monthlyRetention_Newbie AS ( SELECT MONTHNAME(start_month) AS month, COUNT(start_month) AS new_users, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Newbie WHERE splitActiveMonths_Newbie.wallet_address = userActiveMonths_Newbie.wallet_address AND splitActiveMonths_Newbie.active_month = userActiveMonths_Newbie.start_month) THEN 1 END) AS M0, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Newbie WHERE splitActiveMonths_Newbie.wallet_address = userActiveMonths_Newbie.wallet_address AND splitActiveMonths_Newbie.active_month = ADD_MONTHS(userActiveMonths_Newbie.start_month, 1)) THEN 1 END) AS M1, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Newbie WHERE splitActiveMonths_Newbie.wallet_address = userActiveMonths_Newbie.wallet_address AND splitActiveMonths_Newbie.active_month = ADD_MONTHS(userActiveMonths_Newbie.start_month, 2)) THEN 1 END) AS M2, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Newbie WHERE splitActiveMonths_Newbie.wallet_address = userActiveMonths_Newbie.wallet_address AND splitActiveMonths_Newbie.active_month = ADD_MONTHS(userActiveMonths_Newbie.start_month, 3)) THEN 1 END) AS M3, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Newbie WHERE splitActiveMonths_Newbie.wallet_address = userActiveMonths_Newbie.wallet_address AND splitActiveMonths_Newbie.active_month = ADD_MONTHS(userActiveMonths_Newbie.start_month, 4)) THEN 1 END) AS M4, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Newbie WHERE splitActiveMonths_Newbie.wallet_address = userActiveMonths_Newbie.wallet_address AND splitActiveMonths_Newbie.active_month = ADD_MONTHS(userActiveMonths_Newbie.start_month, 5)) THEN 1 END) AS M5 FROM userActiveMonths_Newbie WHERE start_month >= '2023-01-01' AND start_month <= '2023-04-01' GROUP BY start_month ORDER BY start_month ), retentionRate_Newbie AS ( SELECT month, new_users, M0 / CAST(new_users AS FLOAT) * 100 AS M0, M1 / CAST(new_users AS FLOAT) * 100 AS M1, M2 / CAST(new_users AS FLOAT) * 100 AS M2, M3 / CAST(new_users AS FLOAT) * 100 AS M3, M4 / CAST(new_users AS FLOAT) * 100 AS M4, M5 / CAST(new_users AS FLOAT) * 100 AS M5 FROM monthlyRetention_Newbie ), avgRetention_Newbie AS ( SELECT 'Newbie' as user_type, AVG(M0) as m0, AVG(M1) as m1, AVG(M2) as m2, AVG(M3) as m3, AVG(M4) as m4, AVG(M5) as m5 FROM retentionRate_Newbie ), -------------------------------------------------------------------------------------------------------- userActiveMonths_Other AS ( SELECT recipient as wallet_address, date_trunc('month', min(block_timestamp)::date)::date AS start_month, date_trunc('month', max(block_timestamp)::date)::date AS end_month, datediff(month, start_month, end_month) AS active_months, LISTAGG(DISTINCT date_trunc('month', block_timestamp)::date, ',') AS active_months_list FROM ethereum.uniswapv3.ez_swaps WHERE wallet_address IN (SELECT wallet_address FROM user_personas WHERE user_persona = 'Other') -- Filter only new users belonging to 'Other Trader' persona GROUP BY wallet_address ), splitActiveMonths_Other AS ( SELECT wallet_address, value::date AS active_month FROM userActiveMonths_Other, TABLE(SPLIT_TO_TABLE(active_months_list, ',')) -- Split the active_months_list into rows ), monthlyRetention_Other AS ( SELECT MONTHNAME(start_month) AS month, COUNT(start_month) AS new_users, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Other WHERE splitActiveMonths_Other.wallet_address = userActiveMonths_Other.wallet_address AND splitActiveMonths_Other.active_month = userActiveMonths_Other.start_month) THEN 1 END) AS M0, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Other WHERE splitActiveMonths_Other.wallet_address = userActiveMonths_Other.wallet_address AND splitActiveMonths_Other.active_month = ADD_MONTHS(userActiveMonths_Other.start_month, 1)) THEN 1 END) AS M1, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Other WHERE splitActiveMonths_Other.wallet_address = userActiveMonths_Other.wallet_address AND splitActiveMonths_Other.active_month = ADD_MONTHS(userActiveMonths_Other.start_month, 2)) THEN 1 END) AS M2, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Other WHERE splitActiveMonths_Other.wallet_address = userActiveMonths_Other.wallet_address AND splitActiveMonths_Other.active_month = ADD_MONTHS(userActiveMonths_Other.start_month, 3)) THEN 1 END) AS M3, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Other WHERE splitActiveMonths_Other.wallet_address = userActiveMonths_Other.wallet_address AND splitActiveMonths_Other.active_month = ADD_MONTHS(userActiveMonths_Other.start_month, 4)) THEN 1 END) AS M4, COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths_Other WHERE splitActiveMonths_Other.wallet_address = userActiveMonths_Other.wallet_address AND splitActiveMonths_Other.active_month = ADD_MONTHS(userActiveMonths_Other.start_month, 5)) THEN 1 END) AS M5 FROM userActiveMonths_Other WHERE start_month >= '2023-01-01' AND start_month <= '2023-04-01' GROUP BY start_month ORDER BY start_month ), retentionRate_Other AS ( SELECT month, new_users, M0 / CAST(new_users AS FLOAT) * 100 AS M0, M1 / CAST(new_users AS FLOAT) * 100 AS M1, M2 / CAST(new_users AS FLOAT) * 100 AS M2, M3 / CAST(new_users AS FLOAT) * 100 AS M3, M4 / CAST(new_users AS FLOAT) * 100 AS M4, M5 / CAST(new_users AS FLOAT) * 100 AS M5 FROM monthlyRetention_Other ), avgRetention_Other AS ( SELECT 'Other' as user_type, AVG(M0) as m0, AVG(M1) as m1, AVG(M2) as m2, AVG(M3) as m3, AVG(M4) as m4, AVG(M5) as m5 FROM retentionRate_Other ), -- Combine all average retentions into one table combined_avgRetention AS ( SELECT * FROM avgRetention_HOBBYIST UNION ALL SELECT * FROM avgRetention_Professional UNION ALL SELECT * FROM avgRetention_Investor UNION ALL SELECT * FROM avgRetention_Newbie UNION ALL SELECT * FROM avgRetention_Other ) -- Final query SELECT * FROM ( SELECT months_retained, user_type, VALUE FROM combined_avgRetention UNPIVOT (VALUE FOR months_retained IN (M0, M1, M2, M3, M4, M5)) ) PIVOT ( MAX(VALUE) FOR user_type IN ('Hobbyist Trader', 'Professional Trader', 'Investor', 'Newbie', 'Other') );

Uniswapv3 monthly retention