Ez Dex Liquidity Pool Actions

-- Top tokens by liquidity additions (last 7 days)
SELECT
    token_address,
    symbol,
    COUNT(DISTINCT tx_hash) AS add_count,
    SUM(amount_usd) AS total_usd_added
FROM <blockchain_name>.defi.ez_dex_liquidity_pool_actions
WHERE block_timestamp >= CURRENT_DATE - 7
    AND event_name IN ('Mint', 'AddLiquidity', 'Deposit')
GROUP BY 1, 2
ORDER BY total_usd_added DESC
LIMIT 50;

-- Largest single token liquidity actions
SELECT
    block_timestamp,
    tx_hash,
    platform,
    pool_name,
    liquidity_provider,
    symbol,
    amount,
    amount_usd
FROM <blockchain_name>.defi.ez_dex_liquidity_pool_actions
WHERE block_timestamp >= CURRENT_DATE - 7
    AND amount_usd > 0
ORDER BY amount_usd DESC
LIMIT 100;

-- Daily LP activity by platform
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(DISTINCT liquidity_provider) AS unique_lps,
    COUNT(DISTINCT pool_address) AS active_pools,
    SUM(amount_usd) AS total_volume_usd
FROM <blockchain_name>.defi.ez_dex_liquidity_pool_actions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1 DESC, 5 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

-- 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;
``` |
| LIQUIDITY_PROVIDER | TEXT | The address that is providing or removing liquidity from the pool.

Example: '0x1234567890123456789012345678901234567890' |
| SENDER | TEXT | The address that initiated the liquidity pool action function.

Example: '0x7a250d5630b4cf539739df2c5dacb4c659f2488d' |
| RECEIVER | TEXT | The recipient address of the LP tokens or withdrawn assets.

Example: '0x1234567890123456789012345678901234567890' |
| POOL_ADDRESS | TEXT | The liquidity pool contract address where the action occurred.

Example: '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' |
| POOL_NAME | TEXT | Human-readable name for the liquidity pool.

Example: 'WETH/USDC' |
| TOKEN_ADDRESS | TEXT | The contract address of the individual token in this liquidity pool action.

Example: '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' |
| SYMBOL | TEXT | The symbol of the individual token.

Example: 'WETH' |
| DECIMALS | NUMBER | The number of decimal places for the individual token.

Example: 18 |
| AMOUNT_UNADJ | FLOAT | Raw, non-decimal adjusted amount of the individual token in this action.

Example: 1000500000000000000000 |
| AMOUNT | FLOAT | Decimal-adjusted amount of the individual token in this action.

Example: 1000.5 |
| AMOUNT_USD | FLOAT | USD value of the individual token amount at the time of the transaction.

Example: 1500.75 |
| TOKEN_IS_VERIFIED | BOOLEAN | Whether the individual token is verified in the Flipside token metadata.

Example: true |
| PLATFORM | TEXT | The DEX protocol and version where the liquidity action occurred.

Example: 'uniswap-v3' |
| PROTOCOL | TEXT | The protocol used for the liquidity action. This is the clean name of the protocol without the version.

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

Example: 'v3' |
| EZ_DEX_LIQUIDITY_POOL_ACTIONS_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;
``` |