gigiokobawidespread-salmon
    Updated 2025-04-01
    WITH price_info AS (
    SELECT
    TO_DATE(hour) AS transaction_date,
    symbol AS token_symbol,
    MIN(price) AS average_price
    FROM near.price.ez_prices_hourly
    GROUP BY 1, 2
    ),

    transaction_info AS (
    SELECT
    TO_DATE(block_timestamp) AS txn_date,
    tx_hash AS transaction_hash,
    CASE
    WHEN symbol IN ('USDt', 'USDT.e', 'USDC.e') THEN 'USDC'
    WHEN symbol IN ('sFRAX') THEN 'FRAX'
    WHEN symbol IN ('wNEAR') THEN 'NEAR'
    ELSE symbol
    END AS normalized_token,
    sender_id AS user_id,
    actions AS transaction_action,
    amount AS transaction_amount
    FROM near.defi.ez_lending
    WHERE symbol IN ('sFRAX', 'WBTC', 'DAI', 'USDt', 'FRAX', 'ETH', 'USDC', 'BRRR', 'USN', 'USDT.e', 'AURORA', 'USDC.e', 'LINEAR', 'WOO', 'wNEAR', 'STNEAR')
    AND actions NOT IN ('increase_collateral', 'withdraw_failed', 'decrease_collateral')
    ),

    quarterly_info AS (
    SELECT
    txn_data.user_id AS borrower_id,
    CASE
    WHEN EXTRACT(MONTH FROM txn_data.txn_date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM txn_data.txn_date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM txn_data.txn_date) BETWEEN 7 AND 9 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM txn_data.txn_date) BETWEEN 10 AND 12 THEN 'Q4'
    END AS quarter,
    QueryRunArchived: QueryRun has been archived