Correlated subquery in BigQuery not possible

Question

I have a table that records the daily increase (new subs) and decrease (expired subs) of subscriptions. I would like to have the number of active subscribers on a particular day for every date recorded in the table.

I tried to create a query that calculates the sum of new sub minus the sum of expired subs of all the days before the day I want to see but it is not working on Big Query. A JOIN is not possible since I dont want t1.dates = t2.dates ofc.

SELECT t1.dates, 
(select sum(t2.new_subscriptions) - sum(t2.expired_subscriptions) from `daily_subscriber_change` t2 where t1.dates >= t2.dates) as active_abo 
from `daily_subscriber_change` t1 

Any insight?

Answer 1

... to create a query that calculates the sum of new sub minus the sum of expired subs of all the days before the day I want to see

Consider below simple option (which is just exact "translation" of above)

select *, 
  sum(new_subscriptions - expired_subscriptions) over(order by dates)
from daily_subscriber_change