14  Querying Intersecting Dates

Tip

Hi, can you please find users who were on premium last year real quick?

When working with type 2 dimensions, you will usually be finding which rows were active for a given date. The query design is a bit different when you are interested in rows that fall within some interval of dates. The design is useful for finding relevant cases that happened within some period of time. For example, maybe we are interested in knowing the total number of users last year, even if they were not active at the end of the year. Or maybe we want to find users that were using the carwash at some period of time if we’d like to rebate them or something. Luckily, there is a recipe for that!

Let’s say we are not interested in knowing the number of users with an active subscription at the end of last year but instead want to know the total number of users that had a subscription within the last year. So if you had a subscription for just one month in the middle of the year, we want to add you to the count. In the first case, we would do something like this:


select distinct users.*
from subscriptions subs
inner join users
  on subs.user_id = users.user_id
where '2024-12-31' between date_from and date_to
Displaying records 1 - 10
user_id first_name last_name email signup_date confirmation_date activation_date
56 Karen King user56@example.com 2024-07-01 2024-07-04 NA
5 Rachel Baker user5@example.com 2023-02-01 NA NA
12 Bob Smith user12@example.com 2023-10-11 2023-10-17 NA
11 Eva Williams user11@example.com 2024-08-15 2024-08-18 NA
49 Alice Wright user49@example.com 2023-07-08 2023-07-09 NA
53 Alice Wright user53@example.com 2024-03-20 2024-03-26 2024-04-11
1 Hannah King user1@example.com 2023-01-26 NA NA
82 Gina Baker user82@example.com 2023-12-12 2023-12-19 2024-01-07
59 Nina Taylor user59@example.com 2023-04-28 NA NA
50 Chloe King user50@example.com 2023-09-16 2023-09-22 NA

To find where two date ranges intersect, we can use the following query:


select distinct users.*
from subscriptions subs
inner join users
  on subs.user_id = users.user_id
where date_from <= '2023-12-31'
and date_to >= '2023-01-01'
Displaying records 1 - 10
user_id first_name last_name email signup_date confirmation_date activation_date
1 Hannah King user1@example.com 2023-01-26 NA NA
21 Victor Nelson user21@example.com 2024-05-19 2024-05-23 2024-06-09
24 Brian Young user24@example.com 2024-10-08 NA NA
33 Oscar Walker user33@example.com 2023-06-02 2023-06-05 NA
54 Sam Martin user54@example.com 2023-11-30 2023-12-07 2023-12-10
60 George Jones user60@example.com 2024-02-06 2024-02-12 2024-03-11
82 Gina Baker user82@example.com 2023-12-12 2023-12-19 2024-01-07
83 George Miller user83@example.com 2023-02-03 2023-02-04 NA
84 Jasmine Garcia user84@example.com 2024-09-16 2024-09-19 2024-09-21
5 Rachel Baker user5@example.com 2023-02-01 NA NA

This query works when thinking in terms of sets. A subscription whose start date is later than our range’s end date is not in scope (i.e. date_from > ‘2023-12-31’). So we can write the inverse of this, i.e. date_from <= ‘2023-12-31’. The same goes for subscriptions that end before our range of interest.