FlorentGSharkfi Foreclosures
Updated 2023-11-30
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 defaults as (
SELECT TX_ID,
INSTRUCTION['accounts'][0] as loan_id,
INSTRUCTION['accounts'][3] as nft,
BLOCK_TIMESTAMP
FROM solana.core.fact_events
WHERE date(BLOCK_TIMESTAMP) > current_date() - INTERVAL '7 DAYS'
AND LEFT(INSTRUCTION['data'], 4) = 'PsML'
AND SUCCEEDED = 'true'
),
offers_principal_s as (
SELECT TX_ID,
o.PRE_BALANCES[0] - o.POST_BALANCES[0] as principal_s
FROM solana.core.fact_transactions o
WHERE date(o.BLOCK_TIMESTAMP) > current_date() - INTERVAL '21 DAYS'
AND LEFT(o.INSTRUCTIONS[0]['data'], 6) = '2pxy3Z'
AND o.SUCCEEDED = 'true'
),
offers as (
SELECT TX_ID,
o.INSTRUCTION['accounts'][6] as ob_pubkey,
o.INSTRUCTION['accounts'][3] as loan_id
FROM solana.core.fact_events o
WHERE date(o.BLOCK_TIMESTAMP) > current_date() - INTERVAL '21 DAYS'
AND LEFT(o.INSTRUCTION['data'], 6) = '2pxy3Z'
AND o.SUCCEEDED = 'true'
),
nb_offers_per_tx as (
SELECT TX_ID,
count(*) as nb_offers
FROM offers
GROUP BY 1
),
Run a query to Download Data