Track customer loss in redshift

I have a data base that looks like this. It has:

acct_open_dt, or the day the account was opened.

acct_close_dt, or the day the account was closed.

dayssinceopendt, which is TODAY – acct_open_dt if acct_close_dt is null or acct_close_dt – acct_open_dt if acct_close_dt is not null.

What I want:

for a day sequence along 1990-01-01 to TODAY I want to find how many customers were active for each day.

My intial thought would be to do

case when
dateadd(day,dayssinceopenddt,acct_open_date) > '1990-01-01'
then 1
else 0 end as here_on_19900101

for ALL the days, then sum up each new column, but that would be super manual and super inefficient.

How could I do this better?

(of course I was planning on changing the class from numeric to date, you don’t have to include that in your answer.)

Answer

This may suffice:

with t as (
      select acct_open_dt, count(*) as ins, 0 as outs
      from t
      group by acct_open_dt
      union all
      select acct_close_dt, 0 as ins, count(*) as outs
      from t
      group by acct_close_dt
     )
select dt,
       (sum(sum(ins)) over (order by dt) -
        sum(sum(outs)) over (order by dt)
       ) as actives_on_day
from t
group by dt;

This will return only dates that are in your data, but that might be sufficient.

Note that the end date is not considered an active date in this logic.