instruction:accounts[0] as wallet

,instruction:accounts[1] as player_profile

,instruction:accounts[3] as mining_fleets

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

WHERE program_id IN ('SAGE2HAwep459SNq61LHvjxPk4pLPEJLoMETef7f7EE')

AND instruction:accounts[18] = 'MineMBxARiRdMh7s1wdStSK4Ns3YfnLjBfvF5ZCnzuw'

AND succeeded = 'true'

AND block_timestamp > '2024-04-01' AND block_timestamp < current_date

GROUP BY 1,2,3

,mining AS (

select

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

,case when signers[0] = 'FEEPAYye6DtaJhSXFR65ENXMroiseL1jqYT4QxHKRoZr'

then signers[1] else signers[0] end as wallet

,sum(case when value:parsed:info:authority IN ('qe84yNaK76yaZqGqyWJ9A1Npgnt4NSzicbcZvxEQuJy')

then value:parsed:info:amount

end) as ARCO_mined

,sum(case when value:parsed:info:authority IN ('CHxH1BkLp5A6VcQFCYqKGW88i5XFJV5KyyxewSiwcbkB')

then value:parsed:info:amount

end) as BIOMASS_mined

,sum(case when value:parsed:info:authority IN ('6ny545QXZbGDWXo6F7UEVC7MncAzNNxB62tbVoYWG5gs')

then value:parsed:info:amount

end) as CARBON_mined

,sum(case when value:parsed:info:authority IN ('4rZNPtbe9kD44RNdf5XxB6RBWsWaHTg76esbrkWGpkXU')

then value:parsed:info:amount

end) as CUORE_mined

,sum(case when value:parsed:info:authority IN ('9LPyad7NE1VDApsirv46gMrRhbmsbFGMMy5CUiAQXkfP')