Ez Lending Liquidations

-- 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;
``` |
| PLATFORM_NAME | TEXT | The name of the platform |
| PLATFORM_ADDRESS | TEXT | The contract address of the platform's lending contract |
| PLATFORM_EXCHANGE_VERSION | TEXT | The version of the platform's lending contract |
| LOAN_ID | TEXT | The ID associated with the loan, often times tied to the borrower's address and collateral. For the case of Blur's blend, this ID can be used as a unique identifier when tracking active loans. However, note that refinances can happen while still maintaining the same loanId. For platforms like NFTFI, loanids of previously paid loans are reused hence they can't be reliably used as a unique identifier without appropriate timestamp filters. Instead, use the unique_loan_id column. |
| UNIQUE_LOAN_ID | TEXT | The unique ID that can be used to represent a loan within the same platform. For the same loan, the value of this ID will remain the same across the loans taken, liquidations and repayment tables. For some platforms like NFTFI where the lender is issued a promissory note, the lender can send this note to any address, making the receiver the new lender. However, the unique_loan_id still remains the same although the final lender is different. For Blend, unique_loan_id changes only if the loanId and lender changes which would also indicate the previous loan has been fully paid off. |
| LENDER_ADDRESS | TEXT | The address that gives out a loan to the borrower. In the case of NFTFI, when a loan is issued, a promissory note (an ERC721) is issued to the lender. The lender can transfer this note to any address which makes any receiving address the new "lender" |
| BORROWER_ADDRESS | TEXT | The address that receives the loan and has an NFT locked as collateral |
| NAME | TEXT | The name of the NFT collection or project. May be NULL for unverified collections.

Example: 'Bored Ape Yacht Club' |
| CONTRACT_ADDRESS | TEXT | The address of the NFT used as collateral |
| TOKEN_ID | TEXT | The tokenId of the NFT used as collateral |
| LOAN_TOKEN_ADDRESS | TEXT | The contract address of what the loan is denominated in. For Blend, this is Blur's ETH while for NFTFI this could be either WETH, DAI or USDC |
| LOAN_TOKEN_SYMBOL | TEXT | The symbol of the loan token address. For Blur Pool, the symbol is ETH |
| PRINCIPAL_UNADJ | NUMBER | The principal amount of the loan taken with unadjusted decimal places |
| PRINCIPAL | FLOAT | The principal amount of the loan taken with adjusted decimal places |
| PRINCIPAL_USD | FLOAT | The principal amount of the loan taken in USD terms |
| INTEREST_RATE | FLOAT | The non-annualized rate charged by the lender on the principal amount of the loan denominated in percentage (%). For fixed term loans, it is calculated by taking the difference between total debt and principal amount divided by principal amount. For perpetual term loans, interest rate is usually on an annual basis and total debt is calculated prorated |
| APR | FLOAT | The annualized interest rate denominated in percentage (%). |
| LOAN_TERM_TYPE | TEXT | The type of loan terms, could be either a fixed loan or a perpetual loan. A fixed term loan as a fixed repayment date while a perpetual loan does not. |
| LOAN_START_TIMESTAMP | TIMESTAMP_NTZ | The timestamp of when the loan offer is accepted |
| LOAN_DUE_TIMESTAMP | TIMESTAMP_NTZ | The timestamp of when the loan is due. For perpetual term loan, the value will be null |
| TX_FEE | FLOAT | Total fee paid for transaction execution in native token units.

Example: 0.002 |
| TX_FEE_USD | FLOAT | The transaction fee in USD terms |
| ORIGIN_FROM_ADDRESS | TEXT | The externally-owned account (EOA) or contract address that initiated the transaction.

**Key Points**:
- Always 42 characters (0x + 40 hex chars)
- Lowercase normalized in all tables
- Cannot be NULL for valid transactions
- For contract creation: sender of creation transaction

**Common Patterns**:
- EOA → EOA: Simple transfer
- EOA → Contract: User interaction
- Contract → Contract: Internal calls (see fact_traces)
- Known addresses: Exchange hot wallets, protocol deployers

**Query Examples**:
```sql
-- 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)

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