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,