crypto_edgarHarvester_Overview
Updated 2023-08-20
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
HARVESTS_DATASET AS (
SELECT
BLOCK_TIMESTAMP,
TX_ID,
-- SIGNERS[0] AS HARVESTER,
INSTRUCTIONS[1]:accounts[5] as HABITAT,
CASE
WHEN LOG_MESSAGES[6] LIKE '%Energy Amount:%' THEN LOG_MESSAGES[6]
WHEN LOG_MESSAGES[7] LIKE '%Energy Amount:%' THEN LOG_MESSAGES[7]
WHEN LOG_MESSAGES[8] LIKE '%Energy Amount:%' THEN LOG_MESSAGES[8]
ELSE 'None'
END AS DATA
FROM
solana.core.fact_transactions
WHERE
SUCCEEDED = 'true'
-- AND BLOCK_TIMESTAMP >= TIMESTAMP '2022-08-10'
AND BLOCK_TIMESTAMP >= TIMESTAMP '{{start_time}}'
AND SIGNERS[0] = '{{harvester}}'
-- AND BLOCK_ID >= 145322248
AND LOG_MESSAGES[3] = 'Program log: Instruction: HarvestKi'
),
HARVEST_DATASET_CLEANED AS (
SELECT
BLOCK_TIMESTAMP,
TX_ID,
-- HARVESTER,
HABITAT,
SPLIT_PART(SPLIT_PART(DATA, ': ', 3), ',', 0) AS ENERGY_AMOUNT,
SPLIT_PART(SPLIT_PART(DATA, ': ', 4), ',', 0) AS WAITING_PERIOD_DAYS,
SPLIT_PART(SPLIT_PART(DATA, ': ', 5), ',', 0) AS UNLOCK_TIME,
SPLIT_PART(SPLIT_PART(DATA, ': ', 6), ',', 0) AS KI_AMOUNT
FROM
HARVESTS_DATASET
)
Run a query to Download Data