Using lag in the SQL query


I came across an article where it mentioned that omniscidb now supports window functions as lag, lead but couldn’t find an example of how to use lag. I am trying to write a query which will have lag and group by. Any clue as to how i can do that.


Hi @dipanwita2019,

a brief example while using LAG/LEAD and a CTE with a GROUP BY could be

with avg_delays_by_month as 
  (select extract(month from dep_timestamp) the_month, 
              extract(day from dep_timestamp) the_day ,avg(arrdelay) avg_arrdelay,
              avg(depdelay) avg_depdelay 
   from flights_sk group by 1,2) 
  select the_month,
            the_day,avg_arrdelay - lag(avg_arrdelay) over(partition by the_month order by the_day)  
  from avg_delays_by_month 
  order by 1,2;

Hopes this helps