WITH active_users AS ( -- Track users performing DEX swaps on Ethereum using contract_address SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_dex_swaps WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' -- Filter for October GROUP BY user_address, week UNION ALL -- Track users interacting with lending protocols (deposits and borrows) SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_lending_borrows WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' GROUP BY user_address, week UNION ALL -- Track users interacting with lending deposits SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_lending_deposits WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' GROUP BY user_address, week UNION ALL -- Track users interacting with liquid staking (deposits and withdrawals) SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_liquid_staking_deposits WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' GROUP BY user_address, week UNION ALL -- Track users interacting with liquid staking withdrawals SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_liquid_staking_withdrawals WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' GROUP BY user_address, week ), weekly_transactions AS ( -- Calculate total transactions per user per week SELECT user_address, week, COUNT(*) AS total_transactions FROM ( -- Track DEX swaps transactions SELECT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_dex_swaps WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' UNION ALL -- Track lending transactions (borrows and deposits) SELECT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_lending_borrows WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' UNION ALL SELECT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_lending_deposits WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' UNION ALL -- Track liquid staking transactions (deposits and withdrawals) SELECT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_liquid_staking_deposits WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' UNION ALL SELECT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week FROM ethereum.defi.ez_liquid_staking_withdrawals WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' ) AS transactions GROUP BY user_address, week ), weekly_counts AS ( -- Count distinct users per week SELECT week, COUNT(DISTINCT user_address) AS active_users FROM active_users GROUP BY week ORDER BY week ), average_transactions_per_user AS ( -- Calculate the average transactions per user for each week, rounded to 2 decimal places SELECT wt.week, ROUND(AVG(wt.total_transactions), 2) AS avg_transactions_per_user FROM weekly_transactions wt JOIN weekly_counts wc ON wt.week = wc.week GROUP BY wt.week ORDER BY wt.week ) -- Final result showing weekly averages of transactions per user SELECT 'Week ' || ROW_NUMBER() OVER (ORDER BY week) AS week_number, avg_transactions_per_user FROM average_transactions_per_user ORDER BY week;

Ethereum vs Solana