Question 339874 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Count active customers dependent on start and end date

Count active customers dependent on start and end date

Customer Start Date End Date
1 2020-01 2020-04
2 2020-01 2020-03
3 2020-02

Hi, I have a table like the above. I would like to calculate the number of "Active Customers" for each month. Sometimes we have an "End Date" and sometimes we don’t (then they are still active).

So for the Example above, I would like an output like this:

on 2020-01: Number of Active Customers = 2 (Customer 1 + 2)

on 2020-02: Number of Active Customers = 3(Customer 1 + 2 + 3)

on 2020-03: Number of Active Customers = 3 (Customer 1 + 2 + 3)

on 2020-04: Number of Active Customers = 2 (Customer 1 + 3)

on 2020-05: Number of Active Customers = 1 (Customer 3)

2020-06: Number of Active Customers = 1 (Customer 3)

Thanks

Tags:

2

Answers


  1. 0

    Generate a calendar and then OUTER JOIN to the table with customer data and aggregate for each month:

    SELECT TO_CHAR(cal.month, 'YYYY-MM') AS month,
       COUNT(t.customer)
FROM   GENERATE_SERIES(
         '2020-01-01'::DATE,
         '2020-06-01'::DATE,
         '1 MONTH'::INTERVAL
       ) AS cal (month)
       LEFT OUTER JOIN table_name t
       ON (   t.start_date <= cal.month
          AND (cal.month <= t.end_date OR t.end_date IS NULL)
          )
GROUP BY
       cal.month
ORDER BY
       cal.month

    Which, for the sample data:

    CREATE TABLE table_name (Customer, Start_Date, End_Date) AS
SELECT 1, DATE '2020-01-01', DATE '2020-04-01' UNION ALL
SELECT 2, DATE '2020-01-01', DATE '2020-03-01' UNION ALL
SELECT 3, DATE '2020-02-01', NULL;

    Outputs:

    month count
    2020-01 2
    2020-02 3
    2020-03 3
    2020-04 2
    2020-05 1
    2020-06 1

    fiddle

    Login or Signup to reply.
  2. 0

    Split the customer table in two sources, one representing the new customers (with Start_Date), the second the churned customers (with End_Date) and union all the two sources (first CTE in teh example).

    Group it on the transaction date and sum the count (2nd CTE).

    Finally calculate the cummulated count (main query using aggregated function)

    Example

    with trans as (
select +1 cnt, Start_Date trans_date  from table_name union all
select -1 cnt, End_Date  from table_name where End_Date is not null
),
trans2 as (
select trans_date, sum(cnt) cnt
from trans
group by trans_date)
select 
trans_date, cnt, sum(cnt) over(order by trans_date) cnt_cum
from trans2
order by 1

trans_date|cnt|cnt_cum|
----------+---+-------+
2020-01-01|  2|      2|
2020-02-01|  1|      3|
2020-03-01| -1|      2|
2020-04-01| -1|      1|

    Of course if you want to see the figured for months without customer movement (here 5+6/2020) you must use the trick with calendar and outer join as proposed in alternative answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search