-- Daily withdrawal patterns
SELECT
DATE_TRUNC('day', block_timestamp) AS date,
platform,
COUNT(*) AS withdrawal_count,
COUNT(DISTINCT depositor) AS unique_withdrawers,
SUM(amount_usd) AS total_withdrawn_usd,
AVG(amount_usd) AS avg_withdrawal_size_usd
FROM <blockchain_name>.defi.ez_lending_withdraws
WHERE block_timestamp >= CURRENT_DATE - 30
AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 5 DESC;
-- Platform liquidity analysis (deposits vs withdrawals)
WITH platform_deposits AS (
SELECT
platform,
token_address,
token_symbol,
COUNT(DISTINCT depositor) AS unique_depositors,
SUM(amount) AS total_deposited_tokens,
SUM(amount_usd) AS total_deposited_usd,
COUNT(*) AS deposit_transactions,
AVG(amount_usd) AS avg_deposit_size_usd
FROM <blockchain_name>.defi.ez_lending_deposits
WHERE block_timestamp >= CURRENT_DATE - 90
AND amount IS NOT NULL
GROUP BY 1, 2, 3
),
platform_withdrawals AS (
SELECT
platform,
token_address,
token_symbol,
COUNT(DISTINCT depositor) AS unique_withdrawers,
SUM(amount) AS total_withdrawn_tokens,
SUM(amount_usd) AS total_withdrawn_usd,
COUNT(*) AS withdrawal_transactions,
AVG(amount_usd) AS avg_withdrawal_size_usd
FROM <blockchain_name>.defi.ez_lending_withdraws
WHERE block_timestamp >= CURRENT_DATE - 90
AND amount IS NOT NULL
GROUP BY 1, 2, 3
)
SELECT
COALESCE(d.platform, w.platform) AS platform,
COALESCE(d.token_address, w.token_address) AS token_address,
COALESCE(d.token_symbol, w.token_symbol) AS token_symbol,
d.unique_depositors,
w.unique_withdrawers,
d.total_deposited_usd,
w.total_withdrawn_usd,
(d.total_deposited_usd - COALESCE(w.total_withdrawn_usd, 0)) AS net_deposits_usd,
d.deposit_transactions,
w.withdrawal_transactions,
d.avg_deposit_size_usd,
w.avg_withdrawal_size_usd
FROM platform_deposits d
FULL OUTER JOIN platform_withdrawals w
ON d.platform = w.platform
AND d.token_address = w.token_address
WHERE COALESCE(d.total_deposited_usd, 0) > 100000
OR COALESCE(w.total_withdrawn_usd, 0) > 100000
ORDER BY net_deposits_usd DESC;
-- Liquidity stress analysis
WITH hourly_flows AS (
SELECT
DATE_TRUNC('hour', block_timestamp) AS hour,
platform,
token_symbol,
0 AS deposits_usd,
SUM(amount_usd) AS withdrawals_usd
FROM <blockchain_name>.defi.ez_lending_withdraws
WHERE block_timestamp >= CURRENT_DATE - 7
AND amount_usd IS NOT NULL
GROUP BY 1, 2, 3
UNION ALL
SELECT
DATE_TRUNC('hour', block_timestamp) AS hour,
platform,
token_symbol,
SUM(amount_usd) AS deposits_usd,
0 AS withdrawals_usd
FROM <blockchain_name>.defi.ez_lending_deposits
WHERE block_timestamp >= CURRENT_DATE - 7
AND amount_usd IS NOT NULL
GROUP BY 1, 2, 3
)
SELECT
hour,
platform,
token_symbol,
SUM(deposits_usd) AS hourly_deposits,
SUM(withdrawals_usd) AS hourly_withdrawals,
SUM(deposits_usd - withdrawals_usd) AS net_flow,
SUM(SUM(deposits_usd - withdrawals_usd)) OVER (
PARTITION BY platform, token_symbol
ORDER BY hour
) AS cumulative_flow
FROM hourly_flows
GROUP BY 1, 2, 3
HAVING SUM(withdrawals_usd) > 10000
ORDER BY 1 DESC, 6;
-- Large withdrawals monitoring (potential bank run indicators)
SELECT
block_timestamp,
tx_hash,
platform,
depositor,
token_symbol,
amount AS withdrawn_tokens,
amount_usd AS withdrawn_usd,
LAG(amount_usd) OVER (PARTITION BY platform, token_symbol ORDER BY block_timestamp) AS prev_withdrawal_usd,
amount_usd / NULLIF(LAG(amount_usd) OVER (PARTITION BY platform, token_symbol ORDER BY block_timestamp), 0) AS size_multiplier
FROM <blockchain_name>.defi.ez_lending_withdraws
WHERE amount_usd > 1000000
AND block_timestamp >= CURRENT_DATE - 3
ORDER BY withdrawn_usd DESC;
-- Withdrawal timing patterns
SELECT
EXTRACT(HOUR FROM block_timestamp) AS hour_of_day,
COUNT(*) AS withdrawal_count,
SUM(amount_usd) AS total_withdrawn_usd,
AVG(amount_usd) AS avg_withdrawal_size
FROM <blockchain_name>.defi.ez_lending_withdraws
WHERE block_timestamp >= CURRENT_DATE - 30
AND amount_usd IS NOT NULL
GROUP BY 1
ORDER BY 1;
