Count events between dates, but dates are rolling based on a variable "first event"

Question

I have been lurking around and learning a bit of SQL recently so apologies if this is something easy I am entirely misunderstanding.

I am attempting to find out how many "things" different users did in their first 90 days since their first ever event, think like taxi trips or card transactions in the first 90 days. I'm working in BigQuery.

So, I have something that looks like this:

transaction_id	customer_id	transaction_date
67495549	49543345	03/07/2014
71849572	6321113	08/12/2016
75847266	94429299	01/03/2017
75903310	49543345	01/03/2017
82990678	10843	06/09/2017
85785566	384900	10/10/2017
88019966	7444632	21/11/2017

And the way I'm currently thinking about is is the following:

SELECT
  DISTINCT customer_id,
  MIN(transaction_date) AS First_transaction,
  MIN(transaction_date)+89 AS Last_useful_transaction_date,
  COUNT(transaction_id)

FROM
  Transactions

/* WHERE
  transaction_date BETWEEN MIN(transaction_date) AND MIN(transaction_date)+89 */

GROUP BY
  customer_id

Now, without the commented-out part, the output looks like I want it to: one customer ID per row, and the count of how many transactions there were. I have added the first and "last useful" dates for reference. Of course, the count of events is an overall count, and not within the first 90 days, but it looks like it should.

Where I'm stuck is that part (here commented out) where I only count the rows where a date is between the first transaction date and the first transaction + 89 days date. I get errors with BETWEEN and MIN not working nicely together ("Aggregate function MIN not allowed in WHERE clause at [11:28]").

So this is where I'm currently stuck at. I also have the feeling that when I fix this part then maybe I could have an issue with the MIN date value being considered absolute rather than "MIN for that user"? I'm still struggling with nested queries but I believe a solution might be somewhere down that hole, right?

Does it even make sense?

user_13473525 · Answer

Try this one:

select
  customer_id,
  MIN(transaction_date) AS First_transaction,
  COUNT(transaction_id)
from (
  select *
  from transactions
  where true
  qualify transaction_date <= min(transaction_date) over (partition by customer_id) + 89
)
group by customer_id

Count events between dates, but dates are rolling based on a variable "first event"

1 Answers