Ez Lending Deposits

-- Daily deposit volume and TVL calculation
WITH daily_metrics AS (
    SELECT 
        DATE_TRUNC('day', block_timestamp) AS date,
        platform,
        SUM(amount_usd) AS daily_deposits_usd,
        COUNT(DISTINCT depositor) AS unique_depositors
    FROM <blockchain_name>.defi.ez_lending_deposits
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1, 2
),
daily_withdrawals AS (
    SELECT 
        DATE_TRUNC('day', block_timestamp) AS date,
        platform,
        SUM(amount_usd) AS daily_withdrawals_usd
    FROM <blockchain_name>.defi.ez_lending_withdraws
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1, 2
)
SELECT 
    m.date,
    m.platform,
    m.daily_deposits_usd,
    COALESCE(w.daily_withdrawals_usd, 0) AS daily_withdrawals_usd,
    m.daily_deposits_usd - COALESCE(w.daily_withdrawals_usd, 0) AS net_flow_usd,
    SUM(m.daily_deposits_usd - COALESCE(w.daily_withdrawals_usd, 0)) 
        OVER (PARTITION BY m.platform ORDER BY m.date) AS cumulative_tvl_estimate
FROM daily_metrics m
LEFT JOIN daily_withdrawals w ON m.date = w.date AND m.platform = w.platform
ORDER BY m.date DESC, m.platform;

-- Wallet level deposit and withdraw analysis
SELECT 
    d.depositor,
    d.token_address AS collateral_token_address,
    d.token_symbol AS collateral_token_symbol,
    DATE_TRUNC('week', d.block_timestamp) AS weekly_block_timestamp,
    SUM(d.amount) AS total_deposit_amount,
    SUM(d.amount_usd) AS total_deposit_usd,
    SUM(w.amount) AS total_withdraw_amount,
    SUM(w.amount_usd) AS total_withdraw_usd,
    SUM(d.amount) - SUM(w.amount) AS net_collateral_amount,
    SUM(d.amount_usd) - SUM(w.amount_usd) AS net_collateral_usd
FROM 
    <blockchain_name>.defi.ez_lending_deposits d
LEFT JOIN <blockchain_name>.defi.ez_lending_withdraws w
    ON d.depositor = w.depositor
    AND d.token_address = w.token_address
WHERE 
    d.depositor = LOWER('<user_address>')
GROUP BY 1, 2, 3, 4;

-- Depositor behavior analysis
WITH depositor_activity AS (
    SELECT 
        depositor,
        COUNT(DISTINCT platform) AS platforms_used,
        COUNT(DISTINCT token_address) AS unique_assets,
        SUM(amount_usd) AS total_deposited_usd,
        MAX(block_timestamp) AS last_deposit,
        MIN(block_timestamp) AS first_deposit
    FROM <blockchain_name>.defi.ez_lending_deposits
    WHERE amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    CASE 
        WHEN platforms_used = 1 THEN 'Single Protocol'
        WHEN platforms_used = 2 THEN 'Two Protocols'
        ELSE 'Multi-Protocol'
    END AS user_type,
    COUNT(*) AS user_count,
    AVG(total_deposited_usd) AS avg_deposit_size,
    AVG(unique_assets) AS avg_assets_deposited,
    AVG(DATEDIFF('day', first_deposit, last_deposit)) AS avg_active_days
FROM depositor_activity
GROUP BY 1
ORDER BY 2 DESC;

-- Asset distribution by protocol
SELECT 
    platform,
    token_symbol,
    COUNT(*) AS deposit_transactions,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd,
    SUM(amount_usd) * 100.0 / SUM(SUM(amount_usd)) OVER (PARTITION BY platform) AS pct_of_protocol
FROM <blockchain_name>.defi.ez_lending_deposits
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
    AND token_symbol IS NOT NULL
GROUP BY 1, 2
ORDER BY 1, 6 DESC;

-- Large deposits monitoring (whale activity)
SELECT 
    block_timestamp,
    tx_hash,
    platform,
    depositor,
    token_symbol,
    amount,
    amount_usd
FROM <blockchain_name>.defi.ez_lending_deposits
WHERE amount_usd > 1000000
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY amount_usd DESC;