winnie-fsDaily R4 Harvested by Claim Stake copy
    Updated 2023-04-20
    -- forked from StarDustEconomy / Daily R4 Harvested by Claim Stake @ https://flipsidecrypto.xyz/StarDustEconomy/q/atlas-harvested-zz9DOE

    WITH Stake_claims AS (
    SELECT
    date(block_timestamp) AS date,
    instruction:programId AS program,
    inner_instruction:instructions[0]:parsed:info:amount AS fuel,
    inner_instruction:instructions[1]:parsed:info:amount AS ammo,
    inner_instruction:instructions[2]:parsed:info:amount AS food,
    inner_instruction:instructions[3]:parsed:info:amount AS tool,
    tx_id,
    inner_instruction:instructions[0]:parsed:info:authority AS sender,
    instruction:accounts[0] AS receiver,
    CASE
    WHEN instruction:accounts[12] = 'CdQHUngrpj21e5Pi7WD21Uj5w6wDWX4AK1McuedHMDga' THEN instruction:accounts[17] -- Exception for When a player withdraws their claimstake and claims whats remaining in the same action
    ELSE instruction:accounts[12]
    END AS claimstake_id,
    inner_instruction:instructions[0]:parsed:info:amount / POW(1, 8) AS amount
    FROM
    solana.core.fact_events
    WHERE
    program_id = 'STAKEr4Bh8sbBMoAVmTDBRqouPzgdocVrvtjmhJhd65'
    AND sender = '6gxMWRY4DJnx8WfJi45KqYY1LaqMGEHfX9YdLeQ6Wi5'
    AND date BETWEEN CURRENT_DATE() - 14 AND CURRENT_DATE()
    )
    SELECT
    date,
    --tx_id,
    CASE claimstake_id
    WHEN 'HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33' THEN 'Tier 1'
    WHEN '2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq' THEN 'Tier 2'
    WHEN 'C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292' THEN 'Tier 3'
    WHEN 'EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz' THEN 'Tier 4'
    WHEN '3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d' THEN 'Tier 5'
    ELSE 'Unknown'
    END AS claimstake,
    Run a query to Download Data