Ez Bridge Activity

-- Daily bridge volume by protocol
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(DISTINCT tx_hash) AS bridge_txns,
    SUM(amount_usd) AS volume_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- Top bridge routes (source to destination chains)
SELECT
    blockchain AS source_chain,
    destination_chain,
    platform,
    COUNT(*) AS transfer_count,
    SUM(amount_usd) AS total_volume_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 7
    AND destination_chain IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 5 DESC
LIMIT 20;

-- User bridge activity analysis
SELECT
    sender,
    COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS active_days,
    COUNT(DISTINCT platform) AS protocols_used,
    COUNT(DISTINCT destination_chain) AS chains_bridged_to,
    SUM(amount_usd) AS total_bridged_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd > 100  -- Filter small transfers
GROUP BY 1
HAVING COUNT(*) > 5  -- Active bridgers
ORDER BY 5 DESC
LIMIT 100;

-- Token flow analysis
SELECT
    token_symbol,
    token_address,
    blockchain AS source_chain,
    destination_chain,
    COUNT(*) AS bridge_count,
    SUM(amount) AS total_amount,
    AVG(amount_usd) AS avg_transfer_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 7
    AND token_symbol IS NOT NULL
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) > 10
ORDER BY 5 DESC;

-- Bridge protocol comparison
WITH protocol_stats AS (
    SELECT
        platform,
        COUNT(DISTINCT sender) AS unique_users,
        COUNT(*) AS total_transfers,
        AVG(amount_usd) AS avg_transfer_size,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_usd) AS median_transfer_size,
        SUM(amount_usd) AS total_volume
    FROM <blockchain_name>.defi.ez_bridge_activity
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT *
FROM protocol_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)

-- 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;
``` |
| BRIDGE_ADDRESS | TEXT | The smart contract address handling the bridge operation.

Example: '0x296f55f8fb28e498b858d0bcda06d955b2cb3f97' |
| 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;
``` |
| PLATFORM | TEXT | The protocol or application facilitating the cross-chain bridge transfer.

Example: 'stargate' |
| PROTOCOL | TEXT |  |
| PROTOCOL_VERSION | TEXT |  |
| SENDER | TEXT | The address that directly sent tokens to the bridge contract.

Example: '0xabcdefabcdefabcdefabcdefabcdefabcdefabcd' |
| RECEIVER | TEXT | The address designated to receive tokens on the destination chain (or on the source chain, for intermediate steps).

Example: '0x9876543210987654321098765432109876543210' |
| DESTINATION_CHAIN_RECEIVER | TEXT | The final recipient address on the destination blockchain.

Example: '0xfedcbafedcbafedcbafedcbafedcbafedcbafed' |
| DESTINATION_CHAIN | TEXT | The target blockchain network for the bridged assets.

Example: 'arbitrum' |
| DESTINATION_CHAIN_ID | TEXT | The numeric identifier for the destination blockchain.

Example: 42161 |
| TOKEN_ADDRESS | TEXT | The contract address of the token being bridged.

Example: '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' |
| TOKEN_SYMBOL | TEXT | The symbol identifier for the bridged token.

Example: 'USDC' |
| AMOUNT_UNADJ | NUMBER | The raw token amount without decimal adjustment.

Example: 1000000 |
| AMOUNT | FLOAT | The decimal-adjusted amount of tokens bridged.

Example: 1.0 |
| AMOUNT_USD | FLOAT | The hourly close USD value of bridged tokens at the time of the transaction.

Example: 1000.50 |
| TOKEN_IS_VERIFIED | BOOLEAN | Whether the token is verified by the Flipside team.

Example: true |
| EZ_BRIDGE_ACTIVITY_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;
``` |