How to calculate ADA balance at a prior point in time?

Question

I'm trying to calculate the ADA balance at a prior point in time using DB-Sync.

What I did was first work out what the last tx.ID number of the 10th May 22 was, which in my version of the db is 40491540.

So then I wrote this query:

select sum (value) / 1000000 as current_supply from tx_out as tx_outer 
inner join stake_address sa on tx_outer.stake_address_id = sa.id
where 
    not exists 
      ( select tx_out.id from tx_out 
       inner join tx_in on tx_out.tx_id = tx_in.tx_out_id and tx_out.index = tx_in.tx_out_index
          where tx_outer.id = tx_out.id and tx_in.tx_in_id < 40491540
      ) 
and tx_outer.tx_id <=40491540 

However this gives me a total circulating ADA balance of 26.bn which I don't this is right - should be more like 33.7bn.

What am I doing wrong?

Answer 1

Ah ok I solved it. I was close. Just had to turn the inner join into a left join

select sum (value) / 1000000 as current_supply from tx_out as tx_outer 
left join stake_address sa on tx_outer.stake_address_id = sa.id
where 
    not exists 
      ( select tx_out.id from tx_out 
       inner join tx_in on tx_out.tx_id = tx_in.tx_out_id and tx_out.index = tx_in.tx_out_index
          where tx_outer.id = tx_out.id and tx_in.tx_in_id < 40491540
      ) 
and tx_outer.tx_id <=40491540 

Answer 2

Your query excludes account balances (not sure why you're doing the join to stake_address here).

But if looking for total/circulating supply, you can already get those from dbsync table ada_pots (see example here ).

The supply would only change per epoch - when reserved are unlocked.