Amount of clients that have subscription by years and month



  • I got 2 tables as follow

    CREATE TABLE subscriptions(
       client_id INT,
       open_date DATE,
       close_date DATE
    );
    

    CREATE TABLE calendar(date DATE);

    subscription table contains data about clients subscriptions and calendar contains all dates that start from particular year.

    This is what I have far

    SELECT to_char(open_date, 'Mon') as mon, 
        EXTRACT(year from open_date) as year, 
        COUNT(DISTINCT client_id) as count_num 
        FROM subscriptions WHERE close_date IS NULL OR close_date - open_date >= 1
        GROUP BY 1, 2
    

    this query return amount of clients that have subscription in particular period but I want to get an SQL query that, in each month since January of particular year, shows the number of customers who had at least one active subscription in that month at least for one day.

    https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=2b17d2e540dc69384b8f67c84d4049f2



  • Something like:

    select extract(year from c.dt) as yr
         , extract(month from c.dt) as mt
         , count(1) as cnt
    from subscriptions s
    join calendar c
        on c.dt between coalesce(s.open_date, '1900-01-01') 
                    and coalesce(s.close_date,'2099-01-01')
    group by extract(year from c.dt)
           , extract(month from c.dt)
    order by 1,2
    

    We are interested in calendar dates that lies between open and close for each subscription. Since the interval is open in both ends (can both be null) we add a sufficiently small/large date that acts as beginning/end of time as a replacement.

    From this new relation, we can apply an aggregate such as COUNT(1). In your example, you use COUNT(distinct client_id) so I assume that a client can have multiple subscriptions during a period of time.

    https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=b2717fe4e369bdfe7102accb0c001fb1




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2