Ez Dex Swaps

-- Daily swap volume by DEX platform
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS swap_count,
    COUNT(DISTINCT sender) AS unique_traders,
    COUNT(DISTINCT pool_address) AS active_pools,
    SUM(amount_in_usd) AS total_volume_usd,
    AVG(amount_in_usd) AS avg_swap_size_usd,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_in_usd) AS median_swap_usd
FROM <blockchain_name>.defi.ez_dex_swaps
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_in_usd IS NOT NULL
    AND amount_in_usd > 0
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC;

-- Most active trading pairs
WITH pair_volume AS (
    SELECT
        LEAST(token_in, token_out) AS token_a,
        GREATEST(token_in, token_out) AS token_b,
        LEAST(symbol_in, symbol_out) AS symbol_a,
        GREATEST(symbol_in, symbol_out) AS symbol_b,
        COUNT(*) AS swap_count,
        SUM(amount_in_usd) AS volume_usd,
        COUNT(DISTINCT sender) AS unique_traders,
        COUNT(DISTINCT DATE(block_timestamp)) AS active_days
    FROM <blockchain_name>.defi.ez_dex_swaps
    WHERE block_timestamp >= CURRENT_DATE - 7
        AND amount_in_usd IS NOT NULL
    GROUP BY 1, 2, 3, 4
)
SELECT
    symbol_a || '/' || symbol_b AS pair,
    swap_count,
    volume_usd,
    unique_traders,
    active_days,
    volume_usd / swap_count AS avg_swap_size
FROM pair_volume
WHERE volume_usd > 100000
ORDER BY volume_usd DESC
LIMIT 50;

-- Price discrepancies across DEXs for same token pairs
WITH recent_swaps AS (
    SELECT
        block_timestamp,
        platform,
        token_in,
        token_out,
        symbol_in,
        symbol_out,
        amount_in,
        amount_out,
        amount_in_usd / NULLIF(amount_in, 0) AS price_in_usd,
        amount_out_usd / NULLIF(amount_out, 0) AS price_out_usd,
        -- Calculate implied exchange rate
        amount_out / NULLIF(amount_in, 0) AS exchange_rate
    FROM <blockchain_name>.defi.ez_dex_swaps
    WHERE block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '1 hour'
        AND amount_in > 0
        AND amount_out > 0
        AND amount_in_usd IS NOT NULL
),
price_comparison AS (
    SELECT
        DATE_TRUNC('minute', block_timestamp) AS minute,
        token_in,
        token_out,
        symbol_in || '->' || symbol_out AS pair,
        platform,
        AVG(exchange_rate) AS avg_rate,
        COUNT(*) AS swap_count
    FROM recent_swaps
    GROUP BY 1, 2, 3, 4, 5
)
SELECT
    p1.minute,
    p1.pair,
    p1.platform AS platform_1,
    p2.platform AS platform_2,
    p1.avg_rate AS rate_1,
    p2.avg_rate AS rate_2,
    ABS(p1.avg_rate - p2.avg_rate) / LEAST(p1.avg_rate, p2.avg_rate) * 100 AS price_diff_pct
FROM price_comparison p1
JOIN price_comparison p2
    ON p1.minute = p2.minute
    AND p1.token_in = p2.token_in
    AND p1.token_out = p2.token_out
    AND p1.platform < p2.platform
WHERE price_diff_pct > 1  -- More than 1% difference
ORDER BY p1.minute DESC, price_diff_pct DESC;

-- Large swaps by size and impact
SELECT
    block_timestamp,
    tx_hash,
    platform,
    sender,
    symbol_in || ' -> ' || symbol_out AS swap_pair,
    amount_in,
    amount_in_usd,
    amount_out,
    amount_out_usd,
    ABS(amount_in_usd - amount_out_usd) / NULLIF(amount_in_usd, 0) * 100 AS slippage_pct
FROM <blockchain_name>.defi.ez_dex_swaps
WHERE block_timestamp >= CURRENT_DATE - 1
    AND amount_in_usd > 100000  -- Swaps over $100k
ORDER BY amount_in_usd DESC
LIMIT 100;

-- Platform market share by volume
WITH platform_stats AS (
    SELECT
        platform,
        SUM(amount_in_usd) AS total_volume,
        COUNT(*) AS total_swaps,
        COUNT(DISTINCT sender) AS unique_users,
        COUNT(DISTINCT pool_address) AS unique_pools
    FROM <blockchain_name>.defi.ez_dex_swaps
    WHERE block_timestamp >= CURRENT_DATE - 7
        AND amount_in_usd IS NOT NULL
    GROUP BY 1
)
SELECT
    platform,
    total_volume,
    ROUND(100.0 * total_volume / SUM(total_volume) OVER (), 2) AS market_share_pct,
    total_swaps,
    unique_users,
    unique_pools,
    total_volume / NULLIF(total_swaps, 0) AS avg_swap_size
FROM platform_stats
ORDER BY total_volume 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)

-- 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;
``` |
| AMOUNT_IN_UNADJ | FLOAT | The raw, non-decimal adjusted amount of tokens provided in the swap.

Example: 1000500000 |
| AMOUNT_IN | FLOAT | The decimal-adjusted quantity of tokens provided by the trader in the swap.

Example: 1000.5 |
| AMOUNT_IN_USD | FLOAT | USD value of tokens provided in the swap at time of transaction.

Example: 1500.75 |
| AMOUNT_OUT_UNADJ | FLOAT | The raw, non-decimal adjusted amount of tokens received from the swap.

Example: 650000000000000000 |
| AMOUNT_OUT | FLOAT | The decimal-adjusted quantity of tokens received by the trader from the swap.

Example: 0.65 |
| AMOUNT_OUT_USD | FLOAT | USD value of tokens received from the swap at time of transaction.

Example: 1498.25 |
| SENDER | TEXT | The address that initiated the swap transaction.

Example: '0x7a250d5630b4cf539739df2c5dacb4c659f2488d' |
| TX_TO | TEXT | The recipient address of the swapped tokens.

Example: '0x1234567890123456789012345678901234567890' |
| 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;
``` |
| PLATFORM | TEXT | The DEX protocol where the swap occurred.

Example: 'uniswap_v3' |
| PROTOCOL | TEXT | The protocol used for the swap. This is the clean name of the protocol, not the platform, without the version.

Example: 'uniswap' |
| PROTOCOL_VERSION | TEXT | The version of the protocol used for the swap.

Example: 'v3' |
| TOKEN_IN | TEXT | The contract address of the token being sold in the swap.

Example: '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' |
| TOKEN_IN_IS_VERIFIED | BOOLEAN | Whether the token in the swap is verified.

Example: true |
| TOKEN_OUT | TEXT | The contract address of the token being received from the swap.

Example: '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' |
| TOKEN_OUT_IS_VERIFIED | BOOLEAN | Whether the token out of the swap is verified.

Example: true |
| SYMBOL_IN | TEXT | The ticker symbol of the token being sold/swapped from.

Example: 'USDC' |
| SYMBOL_OUT | TEXT | The ticker symbol of the token being bought/received.

Example: 'WETH' |
| _LOG_ID | TEXT |  |
| EZ_DEX_SWAPS_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;
``` |