Selecting rows with constrained time-stamp differences (in seconds) in tables in SQL

Question

I have a table called visit_times in SQL like this

    name   time_stamp
    Allen  2015-02-13 07:10:54
    Allen  2015-02-13 07:10:58
    Allen  2015-02-13 07:11:02
    Mary   2015-02-17 10:45:33
    Mary   2015-02-17 10:45:39
    Mary   2015-02-17 10:45:43
    ...    

I need to select names from "name" column for which all the row-consecutive differences (in second) in the "time_stamp" column equals a certain value. Using the LAG() command I have tried to code it up as follows

WITH cte AS
(
  SELECT  name, 
          DATEDIFF(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap 
  FROM customer_transactions
)
SELECT cte.name
FROM cte
GROUP BY cte.name
HAVING MIN(cte.visit_gap) = 10 AND MAX(cte.visit_gap) = 4;

I expect to get the result as follows:

---------
| name  |
---------
| Allen |
---------

But it outputs nothing! I get the error: in the pre-written template: Incorrect parameter count in the call to native function 'DATEDIFF'

I am not sure how to fix this. Any hints would be appreciated.

Answer 1

SQL queries are processed in a certain order (a quick search for "sql query order of operations" gave me this nice result). The column alias visit_gap can only be reused starting from the order by clause. This explains your syntax error.

The usual solution would be to duplicate the visit_gap expression in the where clause giving you this:

SELECT  name, 
        time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) AS visit_gap
FROM visit_times
WHERE time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) = 4;

However, this will give you a new error that states that the LAG() function cannot appear in the where clause...

Windowed functions can only appear in the SELECT or ORDER BY clauses.

In order to separate the LAG() or visit_gap calculation and the filtering (where clause) you could use a common table expression (CTE). Also, use the DATEDIFF() function (function documentation) to calculate the difference between dates.

with cte as
(
  SELECT  name, 
          datediff(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap
  FROM visit_times
)
select cte.name,
       cte.visit_gap --> column alias is available now!
from cte;

Adding a filter in the where clause gives you your final result:

with cte as
(
  SELECT  name, 
          datediff(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap
  FROM visit_times
)
select cte.name,
       cte.visit_gap --> column alias is available now!
from cte
where cte.visit_gap > 4;

Fiddle with all intermediate steps explained!