select

date_trunc('day', block_timestamp::date) as date,

-----------------------------------------------------------------------------------------------------

sum(case when instruction::string LIKE '%HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33%' then value:parsed:info:amount ELSE 0 end) AS CS1_in,

sum(case when instruction::string LIKE '%2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq%' then value:parsed:info:amount ELSE 0 end) as CS2_in,

sum(case when instruction::string LIKE '%C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292%' then value:parsed:info:amount ELSE 0 end) as CS3_in,

sum(case when instruction::string LIKE '%EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz%' then value:parsed:info:amount ELSE 0 end) as CS4_in,

sum(case when instruction::string LIKE '%3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d%' then value:parsed:info:amount ELSE 0 end) as CS5_in,

sum(case when instruction::string LIKE '%DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t%' then value:parsed:info:amount ELSE 0 end) as IMP1_in,

sum(case when instruction::string LIKE '%ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq%' then value:parsed:info:amount ELSE 0 end) as IMP2_in,

sum(case when instruction::string LIKE '%GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW%' then value:parsed:info:amount ELSE 0 end) as IMP3_in

from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions)

WHERE program_id = 'STAKEr4Bh8sbBMoAVmTDBRqouPzgdocVrvtjmhJhd65'

AND succeeded = 'true'

AND value:parsed:info:authority = signers[0]::string

--AND signers[0] = '5MAwMwFWKHrtSWCBJRTpqD8uv5cEkioCBXtaFLU3jaot'

--AND value:parsed:info:mint IN ('9ifQ16N5DdUFoejCwsgR73ihUwadAe3srCo9HhQe2zL2')

--AND value:parsed:info:mint NOT IN ('ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK', 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG', 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim', 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL')

AND block_timestamp > '2023-04-12' --AND block_timestamp < '2023-05-01'

group by 1

),

claim_stakes_out as (

select

date_trunc('day', block_timestamp::date) as date,

-----------------------------------------------------------------------------------------------------

sum(case when instruction::string LIKE '%HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33%' then value:parsed:info:amount ELSE 0 end) AS CS1_out,

sum(case when instruction::string LIKE '%2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq%' then value:parsed:info:amount ELSE 0 end) as CS2_out,

sum(case when instruction::string LIKE '%C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292%' then value:parsed:info:amount ELSE 0 end) as CS3_out,

sum(case when instruction::string LIKE '%EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz%' then value:parsed:info:amount ELSE 0 end) as CS4_out,

sum(case when instruction::string LIKE '%3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d%' then value:parsed:info:amount ELSE 0 end) as CS5_out,

sum(case when instruction::string LIKE '%DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t%' then value:parsed:info:amount ELSE 0 end) as IMP1_out,

sum(case when instruction::string LIKE '%ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq%' then value:parsed:info:amount ELSE 0 end) as IMP2_out,