Ez Lending Withdraws

-- 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;

-- Recent data
WHERE block_number >= (SELECT MAX(block_number) - 1000 FROM fact_blocks)

-- Historical analysis
WHERE block_number BETWEEN 15000000 AND 16000000

-- Join across tables
JOIN <blockchain_name>.core.fact_event_logs USING (block_number)

-- Time-based filtering (most efficient)
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP)

-- Hourly aggregations
DATE_TRUNC('hour', block_timestamp) AS hour

-- UTC date extraction
DATE(block_timestamp) AS block_date

-- Find all event types for a contract
SELECT DISTINCT event_name, COUNT(*) as occurrences
FROM ez_decoded_event_logs
WHERE contract_address = LOWER('0x...')
GROUP BY 1
ORDER BY 2 DESC;
``` |
| EVENT_INDEX | NUMBER | Zero-based sequential position of the event within a transaction's execution.

**Key Facts**:
- Starts at 0 for first event
- Increments across all contracts in transaction
- Preserves execution order
- Essential for deterministic event ordering

**Usage Example**:
```sql
-- Trace event execution flow
SELECT
    event_index,
    contract_address,
    topic_0,
    SUBSTRING(data, 1, 10) AS data_preview
FROM <blockchain_name>.core.fact_event_logs
WHERE tx_hash = '0xabc...'
ORDER BY event_index;
``` |
| ORIGIN_FUNCTION_SIGNATURE | TEXT | Function signature (first 4 bytes) of the called method.

**Format**: 0x + 8 hex characters

**Common Signatures**:
- 0xa9059cbb: transfer(address,uint256)
- 0x095ea7b3: approve(address,uint256)
- 0x23b872dd: transferFrom(address,address,uint256)

**Note**: NULL for simple transfers or invalid calls |
| ORIGIN_FROM_ADDRESS | TEXT | The externally-owned account (EOA) or contract address that initiated the transaction.

**Key Points**:
- Always 42 characters (0x + 40 hex chars)
- Lowercase normalized in all tables
- Cannot be NULL for valid transactions
- For contract creation: sender of creation transaction

**Common Patterns**:
- EOA → EOA: Simple transfer
- EOA → Contract: User interaction
- Contract → Contract: Internal calls (see fact_traces)
- Known addresses: Exchange hot wallets, protocol deployers

**Query Examples**:
```sql
-- User activity analysis
SELECT from_address, COUNT(*) as tx_count
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;

-- New user detection
SELECT DISTINCT from_address
FROM <blockchain_name>.core.fact_transactions t1
WHERE NOT EXISTS (
    SELECT 1 FROM <blockchain_name>.core.fact_transactions t2
    WHERE t2.from_address = t1.from_address
    AND t2.block_number < t1.block_number
);
``` |
| ORIGIN_TO_ADDRESS | TEXT | The destination address for the transaction - either an EOA or contract address.

**Special Cases**:
- NULL: Contract creation transaction
- Contract address: Interacting with smart contract
- EOA address: Simple transfer or receiving funds

**Important Patterns**:
```sql
-- Contract deployments
WHERE to_address IS NULL

-- Popular contracts
SELECT to_address, COUNT(*) as interactions
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

-- Direct transfers only
WHERE to_address NOT IN (SELECT address FROM dim_contracts)

-- Check data latency
SELECT
    DATE_TRUNC('hour', block_timestamp) as block_hour,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    AVG(TIMESTAMPDIFF('minute', block_timestamp, inserted_timestamp)) as avg_latency_minutes
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2;
``` |
| MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp of the most recent update to this record.

**Format**: TIMESTAMP_NTZ

**Triggers for Updates**:
- Data corrections
- Enrichment additions
- Reprocessing for accuracy
- Schema migrations

**Monitoring Usage**:
```sql
-- Recently modified records
SELECT *
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
AND modified_timestamp >= CURRENT_DATE - 1;

-- Data quality tracking
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_number) as blocks_affected
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
GROUP BY 1
ORDER BY 1 DESC;
``` |