crypto_edgarHarvester_Overview
    Updated 2023-08-20
    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