player_profile

,scanning_fleets as scanning_fleet

from SOLANA.SA.SCANNING_FLEETS

,fees AS (

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

,s.player_profile

---

,round(sum(ft.fee) / 1000000000,4) as sol_payed

from scanning_fleets s

,solana.core.fact_events fe

inner join solana.core.fact_transactions ft USING(block_timestamp, tx_id, succeeded)

WHERE program_id IN ('SAGE2HAwep459SNq61LHvjxPk4pLPEJLoMETef7f7EE')

AND succeeded = 'true'

AND instruction:accounts[1] IN (s.player_profile)

AND instruction:accounts[3] IN (s.scanning_fleet)

AND block_timestamp > current_date-30 AND block_timestamp < current_date

group by 1,2

SELECT * FROM fees