How to summarize count of rows by range of two dates in Metabase

user_3479489 0
Viewed 57

I have a Postgress database with the table MyClasses

Name
id uuid
startDate timestamptz
endDate timestamptz
price numeric

And given the next data:

id startDate endDate price
class1 Aug 1, 2022 Aug 3, 2022 100
class2 Aug 1, 2022 Aug 8, 2022 50
class3 Aug 3, 2022 Aug 16, 2022 50

I would like to generate a graph with the following results:

  • The first week it must count 3 classes because the range between startDate and endDate are inside the first week
  • The second week it must count only 2 classes
  • The third week is only one class.

enter image description here

So far I was able to summarize by the startDate (check the next screenshot) but I need to summarize by the dates that are between startDate and endDate

enter image description here

metabase postgresql sql
1 Answers

The following query gets the job done. (Had named the table classes when I was testing it)

with extremes as (
    select 
        date_trunc('week', min(start_date)) min_date, 
        date_trunc('week', max(end_date)) max_date 
    from classes
) select weeks, count(1) from generate_series(
    (select min_date from extremes), 
    (select max_date from extremes), 
    '1 week'::interval
  ) weeks 
  left join classes c on 
    weeks between date_trunc('week', c.start_date) and date_trunc('week', c.end_date)
  group by 1
  order by 1;

The idea is to generate a series of all the start of week dates ranging b/w the min and the max dates in the data. Once we have the series of weeks, we simply count the number of their occurrences in the data by joining on classes table.

The only problem is that it's very inefficient. The cost comes out to be pretty high on running explain. It's at around 130,000 with 10,000 rows. While the cost figure is arbitrary, in my experience that's very high for the number of rows. I'll check if we can improve something here or if there's a better approach. Let me know if you find something too!

user_69327460
Related