Ez Liquid Staking Withdrawals

-- Daily withdrawal patterns by protocol
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS withdrawal_txns,
    COUNT(DISTINCT staker) AS unique_unstakers,
    SUM(eth_amount) AS eth_withdrawn,
    SUM(eth_amount_usd) AS usd_withdrawn,
    AVG(eth_amount) AS avg_withdrawal_size
FROM defi.ez_liquid_staking_withdrawals
WHERE block_timestamp >= CURRENT_DATE - 30
    AND eth_amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 5 DESC;

-- Net staking flows (deposits vs withdrawals)
WITH daily_deposits AS (
    SELECT
        DATE_TRUNC('day', block_timestamp) AS date,
        platform,
        SUM(eth_amount) AS eth_deposited,
        COUNT(DISTINCT staker) AS depositors
    FROM defi.ez_liquid_staking_deposits
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
),
daily_withdrawals AS (
    SELECT
        DATE_TRUNC('day', block_timestamp) AS date,
        platform,
        SUM(eth_amount) AS eth_withdrawn,
        COUNT(DISTINCT staker) AS withdrawers
    FROM defi.ez_liquid_staking_withdrawals
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
)
SELECT
    COALESCE(d.date, w.date) AS date,
    COALESCE(d.platform, w.platform) AS platform,
    COALESCE(d.eth_deposited, 0) AS eth_deposited,
    COALESCE(w.eth_withdrawn, 0) AS eth_withdrawn,
    COALESCE(d.eth_deposited, 0) - COALESCE(w.eth_withdrawn, 0) AS net_eth_flow,
    COALESCE(d.depositors, 0) AS depositors,
    COALESCE(w.withdrawers, 0) AS withdrawers
FROM daily_deposits d
FULL OUTER JOIN daily_withdrawals w
    ON d.date = w.date AND d.platform = w.platform
ORDER BY date DESC, ABS(net_eth_flow) DESC;

-- Staker holding period analysis
WITH staker_lifecycle AS (
    SELECT
        d.staker,
        d.platform,
        d.block_timestamp AS deposit_time,
        MIN(w.block_timestamp) AS withdrawal_time,
        d.eth_amount AS deposit_amount,
        d.token_amount AS tokens_received
    FROM defi.ez_liquid_staking_deposits d
    LEFT JOIN defi.ez_liquid_staking_withdrawals w
        ON d.staker = w.staker
        AND d.platform = w.platform
        AND d.token_address = w.token_address
        AND w.block_timestamp > d.block_timestamp
    WHERE d.eth_amount IS NOT NULL
    GROUP BY 1, 2, 3, 5, 6
)
SELECT
    platform,
    COUNT(CASE WHEN withdrawal_time IS NOT NULL THEN 1 END) AS completed_cycles,
    COUNT(CASE WHEN withdrawal_time IS NULL THEN 1 END) AS still_staking,
    AVG(CASE
        WHEN withdrawal_time IS NOT NULL
        THEN EXTRACT(EPOCH FROM (withdrawal_time - deposit_time)) / 86400
    END) AS avg_holding_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY CASE
            WHEN withdrawal_time IS NOT NULL
            THEN EXTRACT(EPOCH FROM (withdrawal_time - deposit_time)) / 86400
        END
    ) AS median_holding_days
FROM staker_lifecycle
WHERE deposit_time >= CURRENT_DATE - 365
GROUP BY platform
ORDER BY completed_cycles DESC;

-- Exchange rate at withdrawal (profit/loss analysis)
SELECT
    platform,
    token_symbol,
    DATE_TRUNC('week', block_timestamp) AS week,
    AVG(eth_amount / NULLIF(token_amount, 0)) AS avg_redemption_rate,
    MIN(eth_amount / NULLIF(token_amount, 0)) AS min_rate,
    MAX(eth_amount / NULLIF(token_amount, 0)) AS max_rate,
    COUNT(*) AS withdrawals
FROM defi.ez_liquid_staking_withdrawals
WHERE token_amount > 0
    AND eth_amount > 0
    AND block_timestamp >= CURRENT_DATE - 90
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC;

-- Large withdrawals monitoring (potential de-risking)
SELECT
    block_timestamp,
    tx_hash,
    platform,
    staker,
    eth_amount,
    eth_amount_usd,
    token_symbol,
    token_amount,
    eth_amount / NULLIF(token_amount, 0) AS redemption_rate
FROM defi.ez_liquid_staking_withdrawals
WHERE eth_amount >= 100
    AND block_timestamp >= CURRENT_DATE - 3
ORDER BY eth_amount DESC;

-- Withdrawal pressure indicators
WITH hourly_flows AS (
    SELECT
        DATE_TRUNC('hour', block_timestamp) AS hour,
        platform,
        SUM(eth_amount) AS hourly_withdrawals,
        COUNT(*) AS withdrawal_count,
        COUNT(DISTINCT staker) AS unique_withdrawers
    FROM defi.ez_liquid_staking_withdrawals
    WHERE block_timestamp >= CURRENT_DATE - 7
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
)
SELECT
    platform,
    MAX(hourly_withdrawals) AS peak_hourly_withdrawal,
    AVG(hourly_withdrawals) AS avg_hourly_withdrawal,
    MAX(withdrawal_count) AS peak_withdrawal_count,
    STDDEV(hourly_withdrawals) AS withdrawal_volatility
FROM hourly_flows
GROUP BY platform
HAVING MAX(hourly_withdrawals) > 100
ORDER BY peak_hourly_withdrawal DESC;

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

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

-- 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;
``` |
| EVENT_NAME | TEXT | The event name as defined in the contract's ABI.

**Format**: PascalCase event identifier
**Examples**:
- `Transfer` - Token transfers
- `Swap` - DEX trades
- `OwnershipTransferred` - Admin changes
- `Approval` - Token approvals

**Usage Pattern**:

```sql
-- 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;
``` |
| CONTRACT_ADDRESS | TEXT | Smart contract address that emitted this event or received the transaction.

**Key Points**:
- Always the immediate event emitter for logs
- May differ from transaction to_address
- Lowercase normalized format
- Never NULL for valid events |
| STAKER | TEXT | The address performing the staking or unstaking action.

Example: '0x1234567890123456789012345678901234567890' |
| PLATFORM | TEXT | The liquid staking protocol processing the transaction.

Example: 'lido' |
| TOKEN_SYMBOL | TEXT | The symbol of the liquid staking derivative token.

Example: 'stETH' |
| TOKEN_ADDRESS | TEXT | The contract address of the liquid staking token.

Example: '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' |
| TOKEN_AMOUNT_UNADJ | NUMBER | The raw amount of liquid staking tokens without decimal adjustment.

Example: 999500000000000000 |
| TOKEN_AMOUNT | FLOAT | The decimal-adjusted amount of liquid staking tokens minted or burned.

Example: 0.9995 |
| TOKEN_AMOUNT_USD | FLOAT | The USD value of liquid staking tokens minted or burned.

Example: 2498.75 |
| ETH_AMOUNT_UNADJ | FLOAT | The raw amount of ETH without decimal adjustment.

Example: 1000000000000000000 |
| ETH_AMOUNT | FLOAT | The decimal-adjusted amount of ETH staked or withdrawn.

Example: 1.0 |
| ETH_AMOUNT_USD | FLOAT | The USD value of ETH staked or withdrawn.

Example: 2500.50 |
| EZ_LIQUID_STAKING_WITHDRAWALS_ID | TEXT | Primary key - unique identifier for each row ensuring data integrity.

**Format**: Usually VARCHAR containing composite key generated using MD5 hash of the relevant columns.
**Example**: MD5(block_number, tx_hash, trace_index)

**Usage**:
- Deduplication in incremental loads
- Join operations for data quality checks
- Troubleshooting specific records

**Important**: Implementation varies by table - check table-specific documentation. |
| INSERTED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp when the record was first added to the Flipside database.

**Format**: TIMESTAMP_NTZ

**Use Cases**:
- Data freshness monitoring
- Incremental processing markers
- Debugging data pipeline issues
- SLA tracking

**Query Example**:
```sql
-- 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;
``` |