Count distinct monthly running total

Question

I am trying to find a SQL query to the question of finding the running unique count of distinct customers each month.

This is not the number of distinct customers each month, but rather what is the new incremental total number of unique customers as each month rolls by.

For example, in January I had 10 unique customers and in February I had another 10 customers, but 5 of these who transacted in February were repeat customers and had also transacted in January; so between January and February I really only have a running total of 15 unique customers (10 in January and 5 in February).

What would be the simplest solution to achieve the running unique customer count for each month of the year?

Example Output where (compared to January) in February there were an additional 5 unique customers and in March there were an additional 10 unique customers

Answer 1

This might do:

Select 
   month, 
   count(*) as custs, 
   (select 
        count(distinct cust_id)
    from mytable b 
    where b.month<=a.month) as RunningUniqueCusts 
From mytable a
group by month

Or for month & region

Select 
   month, 
   region,
   count(*) as custs, 
   (select 
        count(distinct cust_id)
    from mytable b 
    where b.month<=a.month 
     and b.region=a.region) as RunningUniqueCustsForRegion
From mytable a
group by month, region

Update 3-Mar-2022 The following would return the unique customer ids for each month where they didn't appear previously:

SELECT TM.MONTH_ID, TM.CUST_ID 
FROM MYTABLE as TM 
WHERE NOT EXISTS 
     (SELECT 1
      FROM MYTABLE as PM 
      WHERE PM.CUST_ID = TM.CUST_ID
      and PM.MONTH < TM.MONTH)
GROUP BY TM.MONTH_ID, TM.CUST_ID
ORDER BY TM.MONTH_ID, TM.CUST_ID