gigiokobawidespread-salmon
Updated 2025-04-01Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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