Aggregate monthly rows created date and ended date

Question

I need to adapt a graph from the current BI implementation to an SQL one. This graph reflects the amount of requests received and each one of these requests have 3 fields that are relevant for this query: the id, created date and the end date.

The graph looks like this https://i.sstatic.net/NRIjr.png:

+----+--------------+-------------+
| ID |  CREATE_DATE |   END_DATE  |
+----+--------------+-------------+
|    |              |             |
| 1  |  2022-01-01  |  2022-02-10 |
|    |              |             |
| 2  |  2022-01-03  |  2022-03-01 |
|    |              |             |
| 3  |  2022-02-01  |  2022-04-01 |
|    |              |             |
| 4  |  2022-03-01  |  null       |
+----+--------------+-------------+

So for this particular example we'd have something like this:

• January: active: 2 (requests 1 and 2), finished: 0;
• February: active 2 (requests 2, 3), finished 1 (request 1);
• March: active 2 (requests 3, 4) finished 1 (request 2)

So for each month I want the active requests for that particular month (those that their ended date goes after that particular month or is null) and the requests that finished during that month (this one might be split to another query, of course) I tried this query, but of course, it doesn't take into account the requests that ended in a particular month, and only gives me the cumulative sum

Edit: I forgot to mention that one of the requirements is that the beggining and end date of the graph might be set by the user. So maybe I want to see the months from April-2022 to April-2020 and see the 2 year behaviour!

 WITH cte AS ( SELECT
        date_trunc('month',
        r.date_init) AS mon,
        count(r.id) AS mon_sum
    FROM
        "FOLLOWUP"."CAT_REQUEST" r
    GROUP  BY
        1     )  SELECT
        to_char(mon,
        'YYYY-mm') AS mon_text,
        COALESCE(sum(c.mon_sum) 
                     OVER (ORDER BY mon),
                 0) AS running_sum
    FROM
        generate_series('2022-01-01', '2023-12-25',
        interval '1 month') mon
    LEFT   JOIN
        cte c USING (mon)
    ORDER  BY
        mon

Answer 1

I wrote query for you using some different business logic. But, result is will be same result which you needed. Sample query:

with month_list as (
    select 1 as id,  'Yanuary' as mname     union all
    select 2 as id,  'Febriary' as mname    union all
    select 3 as id,  'Marth' as mname       union all   
    select 4 as id,  'April' as mname       union all   
    select 5 as id,  'May' as mname         union all   
    select 6 as id,  'June' as mname        union all   
    select 7 as id,  'Jule' as mname        union all   
    select 8 as id,  'August' as mname      union all   
    select 9 as id,  'September' as mname   union all   
    select 10 as id, 'October' as mname     union all   
    select 11 as id, 'November' as mname    union all   
    select 12 as id, 'December' as mname
), 
test_table as (
    select 
        id, 
        create_date, 
        end_date, 
        extract(month from create_date) as month1, 
        extract(month from end_date) as month2 
    from 
        your_table
)
select 
    t1.mname, 
    count(*) as "actived"
from 
    month_list t1 
inner  join 
    test_table t2 on (t1.id >= t2.month1) and (t1.id < t2.month2)
group by 
    t1.id, t1.mname
order by 
    t1.id


/* --- Result: 

mname       actived
--------------------
 Yanuary     2
 Febriary    2
 Marth       1

*/