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 ), total_transactions_per_week AS ( -- Calculate total transactions per week SELECT week, SUM(total_transactions) AS total_transactions FROM weekly_transactions GROUP BY week ), average_transaction_size AS ( -- Calculate average transaction size for each week SELECT tt.week, ROUND(tt.total_transactions / NULLIF(wc.active_users, 0), 2) AS avg_transaction_size FROM total_transactions_per_week tt JOIN weekly_counts wc ON tt.week = wc.week ) -- Final result showing weekly averages of transaction size SELECT 'Week ' || ROW_NUMBER() OVER (ORDER BY week) AS week_number, avg_transaction_size FROM average_transaction_size ORDER BY week;

Query	Creator	Created
AVG TRASACTION SIZE ETH uniswap kyberswap hashflow maverick dodo	amarugo	Nov 20, 2024
ACTIVE USERS ETH uniswap kyberswap hashflow maverick dodo	amarugo	Nov 5, 2024

Explore trending onchain insights