Mariadb: Use result of window function LAG in WHERE clause

September 2, 2022

I am using the following query to get the difference between two timestamps:

SELECT tracker_id,
       TIMESTAMP,
       LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC),
       TIMESTAMPDIFF(MINUTE,
                     TIMESTAMP,
                     LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC)) AS diff_in_minutes
  FROM comm_telemetry
 WHERE comm_telemetry.tracker_id = "123456789"
 ORDER BY comm_telemetry.timestamp DESC;

I want to filter the result to only show when diff_in_minutes > 0. The problem is, that windows functions are not allowed in WHERE clauses.

Any suggestion how so solve this?

>Solution :

You will need to first compute the lag in a subquery and then query that again to use it to filter.

WITH cte AS (
    SELECT tracker_id,
           TIMESTAMP,
           TIMESTAMPDIFF(MINUTE,
                         TIMESTAMP,
                         LAG(TIMESTAMP) OVER (ORDER BY TIMESTAMP DESC)) AS diff_in_minutes 
    FROM comm_telemetry 
    WHERE tracker_id = '123456789'
)

SELECT tracker_id, TIMESTAMP, diff_in_minutes
FROM cte
WHERE diff_in_minutes > 0
ORDER BY TIMESTAMP DESC;