user1369: 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?

user1369: 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 


1369: <p>I'm trying to calculate the ADA balance at a prior point in time using DB-Sync.</p>
<p>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.</p>
<p>So then I wrote this query:</p>
<pre><code>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 &lt; 40491540
      ) 
and tx_outer.tx_id &lt;=40491540 
</code></pre>
<p>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.</p>
<p>What am I doing wrong?</p>
 1369: <p>Ah ok I solved it. I was close. Just had to turn the inner join into a left join</p>
<pre><code>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 &lt; 40491540
      ) 
and tx_outer.tx_id &lt;=40491540 
</code></pre>
