Filter out null values resulting from window function lag() in SQL query

Question

Example query:

SELECT *, 
       lag(sum(sales), 1) OVER(PARTITION BY department
       ORDER BY date ASC) AS end_date_sales
FROM revenue
GROUP BY department, date;

I want to show only the rows where end_date is not NULL.

Is there a clause used specifically for these cases? WHERE or HAVING does not allow aggregate or window function cases.

Answer 1

One method uses a subquery:

SELECT r.*
FROM (SELECT r. *, 
             LAG(sum(sales), 1) OVER (ORDER BY date ASC) AS end_date
      FROM revenue r
     ) r
WHERE end_date IS NOT NULL;

That said, I don't think the query is correct as you have written it. I would assume that you want something like this:

SELECT r.*
FROM (SELECT r. *, 
             LEAD(end_date, 1) OVER (PARTITION BY ? ORDER BY date ASC) AS end_date
      FROM revenue r
     ) r
WHERE end_date IS NOT NULL;

Where ? is a column such as the customer id.

Answer 2

Try this

select * from (select distinct *,SUM(sales) OVER (PARTITION BY dept) from test)t 
where t.date in(select max(date) from test group by dept)
order by date,dept;

And one more simpler way without sub query

SELECT distinct dept,MAX(date) OVER (PARTITION BY dept),
SUM(sales) OVER (PARTITION BY dept) 
FROM test;