Blockchain Transaction, DAI Stablecoin Activity Analysis
Analyzing DAI transfer volume, user adoption, whale concentration, and transaction behavior using on-chain Ethereum data.
Activity & Adoption
Is growth driven by more users or just more money?
Transfer volume fluctuates significantly during periods of market stress, while weekly active users show a steadier growth trend, indicating sustained participation rather than purely speculative usage.
Behavior & Usage Patterns
Most DAI transactions fall into mid-sized buckets, suggesting
that DAI is primarily used in DeFi protocols and structured
transfers rather than purely retail micro-payments.
Risk & Concentration
A small number of wallets account for a disproportionate
share of total DAI volume, highlighting concentration risk
and the importance of monitoring whale behavior.
Dynamics & Interpretation
Periods where transfer volume spikes without a corresponding increase in transaction count suggest whale-driven activity, while parallel increases indicate broader user participation.
IN CONCLUSION
This project demonstrates my ability to work with raw blockchain transaction data, design meaningful aggregations, and translate complex stablecoin activity into insights relevant to investors and analysts. Rather than focusing on price, the analysis examines actual usage behavior, adoption trends, and concentration risks within a decentralized stablecoin ecosystem.
Q1: Daily transfer volume of DAI over the last 6 months
-- Purpose: Show overall trend in activity for DAI
-- line chart
-- insight: highlight demand, liquidity movement, and market reactions
-- Pull raw DAI transfer events from Ethereum in last 6 months
WITH dai_transfers AS (
SELECT
evt_block_time,
value / 1e18 AS amount_dai
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0x6b175474e89094c44da98b954eedeac495271d0f
AND evt_block_time >= NOW() - INTERVAL '6' MONTH
),
-- Aggregate by day to compute total transferred volume
daily_volume AS (
SELECT
DATE_TRUNC('day', evt_block_time) AS day,
SUM(amount_dai) AS daily_volume_dai
FROM dai_transfers
GROUP BY 1
)
SELECT
day,
daily_volume_dai
FROM daily_volume
ORDER BY day;
Q2: Weekly active DAI users (unique senders + receivers)
-- Purpose: Understand user engagement and adoption over time
-- line chart
-- insight: whether DAI’s user base is expanding or contracting
WITH dai_transfers AS (
SELECT
evt_block_time,
"from" AS from_address,
"to" AS to_address
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0x6b175474e89094c44da98b954eedeac495271d0f
AND evt_block_time >= NOW() - INTERVAL '6' MONTH
),
all_wallets AS (
-- Flatten from/to into one column so we can count distinct users
SELECT evt_block_time, from_address AS wallet FROM dai_transfers
UNION ALL
SELECT evt_block_time, to_address AS wallet FROM dai_transfers
),
weekly_active AS (
SELECT
DATE_TRUNC('week', evt_block_time) AS week,
COUNT(DISTINCT wallet) AS weekly_active_wallets
FROM all_wallets
GROUP BY 1
)
SELECT *
FROM weekly_active
ORDER BY week;
Q3: Top 20 wallets by DAI transfer volume
-- Purpose: Identify whales and assess concentration risk
-- bar chart
-- insight: reveal activity dominated by a small number of users
-- Load DAI transfer events including sender and amount
WITH dai_transfers AS (
SELECT
"from" AS sender,
"to" AS receiver,
value / 1e18 AS amount_dai
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0x6b175474e89094c44da98b954eedeac495271d0f
AND evt_block_time >= NOW() - INTERVAL '6' MONTH
),
-- Aggregate total amount sent per wallet
wallet_volume AS (
SELECT
sender AS wallet,
SUM(amount_dai) AS total_sent_dai,
COUNT(*) AS num_txs
FROM dai_transfers
GROUP BY sender
)
-- Output top 20 wallets
SELECT
wallet,
total_sent_dai,
num_txs
FROM wallet_volume
ORDER BY total_sent_dai DESC
LIMIT 20;
Q4: Transfer size distribution for DAI
-- Purpose: Show what types of users dominate activity (small tx vs large)
-- pie chart, bar chart
-- insight: show which group drives the majority of DAI movement
-- Load standard-sized DAI transfer amounts
WITH dai_transfers AS (
SELECT
value / 1e18 AS amount_dai
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0x6b175474e89094c44da98b954eedeac495271d0f
AND evt_block_time >= NOW() - INTERVAL '6' MONTH
),
-- Assign transfer to a size bucket
bucketed AS (
-- Categorize the transfer size
SELECT
CASE
WHEN amount_dai < 100 THEN '0–100 DAI'
WHEN amount_dai < 1000 THEN '100–1,000 DAI'
WHEN amount_dai < 10000 THEN '1,000–10,000 DAI'
ELSE '10,000+ DAI'
END AS bucket,
amount_dai
FROM dai_transfers
),
-- Count transactions and aggregate volume per bucket
bucket_stats AS (
SELECT
bucket,
COUNT(*) AS num_txs,
SUM(amount_dai) AS total_volume_dai
FROM bucketed
GROUP BY 1
)
SELECT *
FROM bucket_stats
ORDER BY total_volume_dai DESC;
Q5: Daily DAI transfers and volume (great for area or line chart)
-- Purpose: Compare count of transactions vs amount moved
-- area chart
-- insight: to understand liquidity patterns and usage intensity
-- Retrieve DAI transfers with amount and timestamp
WITH dai_transfers AS (
SELECT
evt_block_time,
value / 1e18 AS amount_dai
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0x6b175474e89094c44da98b954EedeAC495271d0F
AND evt_block_time >= NOW() - INTERVAL '6' MONTH
),
-- Compute daily metrics: number of txs and total DAI moved
daily_stats AS (
SELECT
DATE_TRUNC('day', evt_block_time) AS day,
COUNT(*) AS num_transfers,
SUM(amount_dai) AS total_volume_dai
FROM dai_transfers
GROUP BY 1
)
SELECT
day,
num_transfers,
total_volume_dai
FROM daily_stats
ORDER BY day;
Q5: Daily DAI transfers and volume (great for area or line chart)
-- Purpose: Compare count of transactions vs amount moved
-- area chart
-- insight: to understand liquidity patterns and usage intensity
-- Retrieve DAI transfers with amount and timestamp
WITH dai_transfers AS (
SELECT
evt_block_time,
value / 1e18 AS amount_dai
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0x6b175474e89094c44da98b954EedeAC495271d0F
AND evt_block_time >= NOW() - INTERVAL '6' MONTH
),
-- Compute daily metrics: number of txs and total DAI moved
daily_stats AS (
SELECT
DATE_TRUNC('day', evt_block_time) AS day,
COUNT(*) AS num_transfers,
SUM(amount_dai) AS total_volume_dai
FROM dai_transfers
GROUP BY 1
)
SELECT
day,
num_transfers,
total_volume_dai
FROM daily_stats
ORDER BY day;