transfer_txs as (

select

block_timestamp

, tx_id

, tx_from

, tx_to

, amount

from solana.core.fact_transfers

inner join solana.core.fact_events

using(block_timestamp, tx_id)

where succeeded

and program_id = '1azyuavdMyvsivtNxPoz6SucD18eDHeXzFCUPq5XU7w' -- Lazy Distributor

and mint = 'mb1eu7TzEc71KxDpsmsKoucSSuuoGLv1drys1oP2jh6' -- MOBILE

and tx_from = '4qGj88CX3McdTXEviEaqeP2pnZJxRTsZFWyU3Mrnbku4' -- MOBILE Rewards

and array_contains('9YgvHbCTrRCvBd6ZEMsMAFBmk2SkWkySdYPK98y34F9S' ::variant, instruction :accounts) -- Helium Mobile Subscriber cNFT

and block_timestamp > '2023-07-25'

),

aggregated as (

select

date_trunc('day', block_timestamp) as date

, sum(amount) as mobile

, sum(mobile) over (order by date) as cumul

from transfer_txs

group by 1