Finding time intervals between consecutive customer visits with SQL

Data and Databases
1

I need help writing a SQL query that computes the number of days between each consecutive visit for every customer in my database. Basically, I want to find the gap between visit 1 and visit 2, then between visit 2 and visit 3, and so on for each individual customer.

Let me give you an example to make it clearer. Say Customer X came on 2016-02-15, 2016-02-20, and 2016-07-10. I want to calculate that there were 5 days between the first and second visit, and then 141 days between the second and third visit. Customer Y might have 8 different visit dates and I need all the gaps calculated for them too.

Here’s what I tried so far:

With visit_cte as (Select client_id, visit_date,
                   ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY visit_date) as rank_num
                   FROM customer_visits CV)
Select client_id, visit_date, rank_num, DateDiff(DAY, T1.visit_date, T2.visit_date) as day_difference
FROM visit_cte T1
LEFT JOIN visit_cte T2 ON T1.client_id = T2.client_id AND T1.rank_num = 1 AND T2.rank_num = 2

Any suggestions on how to make this work properly?

2

yep, your join is only looking at 1 and 2 visits. the LAG function is a solid idea for this! try something like SELECT client_id, visit_date, DATEDIFF(DAY, LAG(visit_date) OVER(PARTITION BY client_id ORDER BY visit_date), visit_date) as days_between FROM customer_visits to catch all gaps.

3

hmm, looks like your join condition is only grabbing ranks 1 and 2? what if you try using LAG() function instead - it might be simpler for this kinda thing. something like LAG(visit_date) OVER(PARTITION BY client_id ORDER BY visit_date) could work better. have you experimented with window functions much before?

4

The issue with your current approach is that the join condition hardcodes ranks 1 and 2, which only captures the gap between the first two visits. To get all consecutive gaps, you need to modify the join to connect each visit with its immediate successor. Change your join condition to T1.rank_num + 1 = T2.rank_num instead of the fixed rank values. This will pair each visit with the next one in sequence. Your CTE structure is actually sound, but you could also consider using the LAG window function as others mentioned, which eliminates the need for a self-join entirely. Both approaches will work, but LAG tends to be more readable and efficient for this type of sequential analysis.