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;

Query	Creator	Created
ethereum daily user count uniswap kyberswap hashflow maverick dodo	ejirogabriel	Nov 7, 2024
ethereum average transactions per user uniswap kyberswap hashflow maverick dodo	ejirogabriel	Nov 7, 2024
Distribution of Defi Activities on Ethereum uniswap kyberswap hashflow maverick dodo	ejirogabriel	Nov 7, 2024
alive-amethyst uniswap kyberswap hashflow maverick dodo	ejirogabriel	Nov 11, 2024

ejirogabriel