Trying find a better counting events at my SQL Query-CodePudding

I Have an events tables - all events that happened in my app. The table contains 3 columns, with event_id (string), created at (date) and ended at (date), looks like:

event_id | created_at | ended at

Some of the events started at some random month (for example January 2022) and ended at the same month (January 2022), but some of them started at previous month (December 2021) and ended at the next month (January 2022).

All I Want is to find a way to count, in a year and month level, all the events that happened in a month:

Started and ended at the same month
Started at the previous month but ended at the next month
Started at this month and ended at the next month

For Example:

event_id | created_at | ended at

123       2021-12-31  2022-01-01
456       2022-01-01  2022-01-01
789       2022-01-31  2022-02-01

final result will be:

year | month | num_of_events

2022    1         3
2022    2         1

January 2022 is 3 because 3 events occurred in this month.

I thought maybe union the 3 cases but I think that there is a better way to do that. Thanks for your help!

CodePudding user response：

extract year and month then use aggregation

select EXTRACT(YEAR FROM created_at) AS year,
EXTRACT(MONTH FROM DATE created_at) as month, count(1)
from table_name group by 
EXTRACT(YEAR FROM created_at) 
EXTRACT(MONTH FROM DATE created_at)

CodePudding user response：

Consider below approach

select 
  extract(year from year_month) year,
  extract(month from year_month) month, 
  count(*) num_of_events
from your_table, 
unnest(generate_date_array(
    date_trunc(created_at, month), 
    date_trunc(ended_at, month), 
    interval 1 month)
  ) year_month
group by year, month

if apply to sample data in your question -

with your_table as (
  select 123 event_id, date '2021-12-31' created_at, date '2022-01-01' ended_at union all
  select 456, '2022-01-01', '2022-01-01' union all
  select 789, '2022-01-31', '2022-02-01' 
)

output is