Ez Lending Deposits

-- Daily lending deposits by protocol
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(DISTINCT tx_id) AS deposit_txns,
    COUNT(DISTINCT depositor) AS unique_depositors,
    SUM(amount) AS tokens_deposited,
    SUM(amount_usd) AS usd_deposited,
    AVG(amount_usd) AS avg_deposit_size
FROM solana.defi.ez_lending_deposits
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC;

-- Protocol market share analysis
WITH protocol_totals AS (
    SELECT 
        platform,
        SUM(amount_usd) AS total_usd_deposited,
        COUNT(DISTINCT depositor) AS unique_depositors,
        COUNT(*) AS total_deposits
    FROM solana.defi.ez_lending_deposits
    WHERE block_timestamp >= CURRENT_DATE - 90
        AND amount_usd IS NOT NULL
    GROUP BY platform
)
SELECT 
    platform,
    total_usd_deposited,
    total_usd_deposited * 100.0 / SUM(total_usd_deposited) OVER () AS market_share_pct,
    unique_depositors,
    total_deposits,
    total_usd_deposited / total_deposits AS avg_deposit_size
FROM protocol_totals
ORDER BY total_usd_deposited DESC;

-- Depositor behavior patterns
WITH depositor_activity AS (
    SELECT 
        depositor,
        COUNT(DISTINCT platform) AS protocols_used,
        COUNT(*) AS total_deposits,
        SUM(amount_usd) AS total_usd_deposited,
        MIN(block_timestamp) AS first_deposit,
        MAX(block_timestamp) AS last_deposit,
        COUNT(DISTINCT DATE_TRUNC('month', block_timestamp)) AS active_months
    FROM solana.defi.ez_lending_deposits
    WHERE amount_usd IS NOT NULL
    GROUP BY depositor
)
SELECT 
    CASE 
        WHEN total_usd_deposited < 1000 THEN '< $1K'
        WHEN total_usd_deposited < 10000 THEN '$1K-$10K'
        WHEN total_usd_deposited < 100000 THEN '$10K-$100K'
        ELSE '$100K+'
    END AS depositor_tier,
    COUNT(*) AS depositor_count,
    AVG(total_deposits) AS avg_deposits_per_user,
    AVG(protocols_used) AS avg_protocols_used,
    SUM(total_usd_deposited) AS tier_total_usd
FROM depositor_activity
GROUP BY depositor_tier
ORDER BY MIN(total_usd_deposited);

-- Large deposits monitoring (whale activity)
SELECT 
    block_timestamp,
    tx_id,
    platform,
    depositor,
    token_symbol,
    amount,
    amount_usd,
    protocol_market
FROM solana.defi.ez_lending_deposits
WHERE amount_usd >= 100000
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY amount_usd DESC;

-- Weekly deposit momentum
WITH weekly_deposits AS (
    SELECT 
        DATE_TRUNC('week', block_timestamp) AS week,
        platform,
        SUM(amount_usd) AS weekly_usd_deposited,
        COUNT(DISTINCT depositor) AS unique_depositors
    FROM solana.defi.ez_lending_deposits
    WHERE block_timestamp >= CURRENT_DATE - 84
        AND amount_usd IS NOT NULL
    GROUP BY 1, 2
)
SELECT 
    week,
    platform,
    weekly_usd_deposited,
    LAG(weekly_usd_deposited) OVER (PARTITION BY platform ORDER BY week) AS prev_week_usd,
    (weekly_usd_deposited / NULLIF(LAG(weekly_usd_deposited) OVER (PARTITION BY platform ORDER BY week), 0) - 1) * 100 AS week_over_week_pct,
    unique_depositors
FROM weekly_deposits
ORDER BY week DESC, weekly_usd_deposited DESC;