Search code examples
sqlpostgresqlsubquerylag

SQL, Calculate Data Between Rows Using Lag

I have a Play table and it has columns possession and clock. I want to return all plays where the possession is 'away' and calculate the time elapsed between the previous play and the selected play. The problem is, using my query, the lag function is calculating elapsed time on a set of only plays where 'away' has possession and not a set of all plays.

select (lag(clock, 1) over () - clock) from plays where possession = 'away';

How do I make the lag(clock, 1) refer to the set of all plays, not just those where possession = 'away'?

Solution

  • I think you just want a subquery:

    select p.*
from (select p.*, (lag(clock, 1) over () - clock) as diff
      from plays p
     ) p
where possession = 'away';