Fact Transactions

-- Daily transaction statistics by type
SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    tx_type,
    COUNT(*) AS tx_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    SUM(tx_fee) AS total_fees_native,
    AVG(gas_used) AS avg_gas_used,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gas_price) AS median_gas_price
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- High-value native token transfers
SELECT
    tx_hash,
    block_timestamp,
    from_address,
    to_address,
    value,
    tx_fee,
    gas_used * gas_price / 1e9 AS gas_cost_gwei
FROM <blockchain_name>.core.fact_transactions
WHERE value > 0
    AND tx_succeeded
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY value DESC
LIMIT 100;

-- Smart contract interaction patterns
SELECT
    to_address,
    origin_function_signature,
    COUNT(*) AS interaction_count,
    COUNT(DISTINCT from_address) AS unique_users,
    SUM(tx_fee) AS total_fees_paid
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IN (SELECT address FROM dim_contracts)
    AND block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 50;

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

-- Daily native token transfer volume
SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    SUM(value) AS total_transferred,
    COUNT(*) AS transfer_count
FROM <blockchain_name>.core.fact_transactions
WHERE value > 0 AND tx_succeeded
GROUP BY 1;
``` |
| VALUE_PRECISE_RAW | TEXT | String representation of numeric values preserving exact precision without any adjustments.

**Format**: VARCHAR containing numeric string
**Purpose**: Prevents floating-point precision loss due to snowflake limitations
**Contains**: Raw blockchain values (usually in smallest unit)

**Example Values**:
- "1000000000000000000" = 1 ETH in Wei
- "50000000" = 50 USDC (6 decimals)

**Usage**:
```sql
-- Exact value comparisons
WHERE value_precise_raw = '1000000000000000000'

-- Conversion with precision
CAST(value_precise_raw AS NUMERIC(38,0)) / POW(10, 18) AS value_decimal
``` |
| VALUE_PRECISE | TEXT | String representation of numeric values adjusted for human readability while maintaining precision.

**Format**: VARCHAR containing decimal string
**Adjustments**: Converted from smallest unit to standard unit
**Purpose**: Human-readable values without precision loss

**Example Values**:
- "1.0" = 1 ETH (converted from Wei)
- "50.0" = 50 USDC (converted from 6 decimal places)

**Best Practices**:
```sql
-- Safe numeric operations
CAST(value_precise AS NUMERIC(38,18))

-- Filtering large values
WHERE CAST(value_precise AS NUMERIC(38,18)) > 1000

-- Aggregations
SUM(CAST(value_precise AS NUMERIC(38,18))) AS total_value
``` |
| TX_FEE | FLOAT | Total fee paid for transaction execution in native token units.

Example: 0.002 |
| TX_FEE_PRECISE | TEXT | Exact transaction fee as string to prevent floating-point precision loss.

Example: '0.002345678901234567' |
| TX_SUCCEEDED | BOOLEAN | Boolean indicator of transaction success.

**Values**:
- TRUE: Transaction executed successfully
- FALSE: Transaction failed/reverted |
| TX_TYPE | NUMBER | Transaction envelope type (EIP-2718).

Example: 2 |
| NONCE | NUMBER | Sequential counter of transactions sent by the from_address.

Example: 42 |
| TX_POSITION | NUMBER | Zero-indexed position of transaction within its block.

**Insights**:
- Position 0: First transaction in block
- MEV bots often target early positions
- Bundle transactions appear consecutively
- Useful for analyzing transaction ordering |
| INPUT_DATA | TEXT | Encoded data sent with the transaction, containing function calls and parameters.

Example: '0xa9059cbb0000000000000000000000001234567890123456789012345678901234567890' |
| GAS_PRICE | FLOAT | Price per gas unit in Gwei (1 Gwei = 1e-9 native token).

Example: 25 |
| EFFECTIVE_GAS_PRICE | FLOAT | Actual price paid per gas unit for EIP-1559 transactions, in Gwei.

Example: 23.5 |
| GAS_LIMIT | NUMBER | Maximum gas units the sender is willing to consume for this transaction.

Example: 150000 |
| GAS_USED | NUMBER | Actual gas units consumed by transaction execution.

Example: 89234 |
| CUMULATIVE_GAS_USED | NUMBER | Running total of gas consumed by all transactions up to and including this transaction within the block.

Example: 1234567 |
| MAX_FEE_PER_GAS | FLOAT | Maximum total fee per gas unit sender is willing to pay (EIP-1559), in Gwei.

Example: 50 |
| MAX_PRIORITY_FEE_PER_GAS | FLOAT | Maximum tip per gas unit for validator (EIP-1559), in Gwei.

Example: 2 |
| R | TEXT | R component of ECDSA signature (32 bytes).

Example: '0x1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcdef' |
| S | TEXT | S component of ECDSA signature (32 bytes).

Example: '0xabcdef1234567890abcdef1234567890abcdef1234567890abcdef1234567890' |
| V | TEXT | Recovery identifier for ECDSA signature.

Example: 27 |
| FACT_TRANSACTIONS_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;
``` |