I am facing the following problem. I searched for hours for a similar question, but can't find an answer.

Question:

* How to check if there is a range that contains a given date using SQL?

This is more of a general question as stated in the subject, but below you can find a little context.

I want to:

* calculate if there was an active subscription for a specific user at a given date.
Below I attach the sample tables. I want to use this later for calculations of retention/churn/reactivations etc.
* the tables are in BigQuery, so it is standard SQL question.

Given:

* Table 1: User\_id and a date I want to check if there was an active subscription at this date
* Table 2: Subscription transactions with date of transaction and expiry date

Desired output:

* Table 3: Table 1 with "check" column if there is any record in the second table that it's range contains this Table1.Date

```
Table 1: User_date
|---------------------|------------------|
|      User_id        |       Date       |
|---------------------|------------------|
|          1          |    2020-10-31    |
|---------------------|------------------|
|          1          |    2020-11-30    |
|---------------------|------------------|
|          2          |    2020-10-31    |
|---------------------|------------------|
|          2          |    2020-11-30    |
|---------------------|------------------|
|          3          |    2020-10-31    |
|---------------------|------------------|

Table 2: Subscription_transactions

|---------------------|------------------|------------------|
|  Transaction_date   |Transaction_expiry|       User_id    |
|---------------------|------------------|------------------|
|      2020-10-01     |    2020-10-28    |         1        |
|---------------------|------------------|------------------|
|      2020-10-29     |    2020-11-15    |         1        |
|---------------------|------------------|------------------|
|      2020-10-15     |    2020-11-15    |         2        |
|---------------------|------------------|------------------|
|      2020-09-29     |    2020-10-15    |         3        |
|---------------------|------------------|------------------|

Table 3: Desired Output 
|---------------------|------------------|------------------|
|      User_id        |       Date       |     Is_active    |
|---------------------|------------------|------------------|
|          1          |    2020-10-31    |       TRUE       |
|---------------------|------------------|------------------|
|          1          |    2020-11-30    |       FALSE      |
|---------------------|------------------|------------------|
|          2          |    2020-10-31    |       TRUE       |
|---------------------|------------------|------------------|
|          2          |    2020-11-30    |       FALSE      |
|---------------------|------------------|------------------|
|          3          |    2020-10-31    |       FALSE      |
|---------------------|------------------|------------------|

```