winnie-fsDaily R4 Harvested by Claim Stake copy
Updated 2023-04-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
›
⌄
-- 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