SQL: Find AVG value of X samples before & after a found event

Question

How do I find the average value of X samples before and after a found event from a sorted table?

In the example below, I have sales_list table. I want to sort the table by date and find the events when sales 'count' goes from less than to greater than 100. Then find the average profit of 2 days before and 2 days after that event.

sales_list
date        count   profit
12/1/2023   20    100
12/2/2023   50    280
12/3/2023   125   660
12/4/2023   165   850
12/5/2023   85    150
12/6/2023   150   710
12/7/2023   180   740

Expected output:
date        count   profit  avg_prev2   avg_next2
12/3/2023   125     660     190     500
12/6/2023   150     710     500     740

Here, I'm struggling with the AVG(LAG) syntax that doesn't seem correct. I get the: "Windows function may not appear inside an aggregate function".

Any help will be appreciated.

WITH top_sales AS (
  SELECT
    date,
    count,
    profit
    ROW_NUMBER() OVER (ORDER BY date) AS row_num,
    
    CASE WHEN (
        LAG(count) OVER (ORDER BY date) < 100 AND count >= 100) 
    THEN 1 ELSE 0 END AS high_count
    
  FROM
    sales_data
)

SELECT
  date,
  count,
  profit,
  AVG(LAG(profit, 2) OVER (ORDER BY date)) AS avg_prev2,
  AVG(LEAD(profit, 2) OVER (ORDER BY date)) AS avg_next2
FROM
  top_sales
WHERE
  high_count = 1

Answer 1

The query below first uses a cte to associate an incremented row number with each entry. Then, the row numbers are used in a left join and two subqueries to search for the desired preceding and proceeding information:

with sales as (
   select row_number() over (order by s.date) r, s.* from sales_list s
)
select s1.*, (select avg(s3.profit) from sales s3 where s3.r >= s1.r - 2 and s3.r < s1.r) avg_prev2,
   (select avg(s3.profit) from sales s3 where s3.r <= s1.r + 2 and s3.r > s1.r) avg_next2
from sales s1 left join sales s2 on s1.r = s2.r + 1
where s2.count is not null and s2.count < 100 and s1.count > 100

See fiddle

Answer 2

Oracle:
One option is to use analytic functions LAG() Over() and AVG() Over() (AVG with windowing clause ROWS BETWEEN ...) to get the values of interest in the same row (Main SQL's inner query below):

WITH  --  S a m p l e    D a t a :
    sales_list ( A_DATE, A_COUNT, PROFIT ) AS
    ( Select  DATE '2023-12-01',  20, 100 From Dual Union All
      Select  DATE '2023-12-02',  50, 280 From Dual Union All
      Select  DATE '2023-12-03', 125, 660 From Dual Union All
      Select  DATE '2023-12-04', 165, 850 From Dual Union All
      Select  DATE '2023-12-05',  85, 150 From Dual Union All
      Select  DATE '2023-12-06', 150, 710 From Dual Union All
      Select  DATE '2023-12-07', 180, 740 From Dual 
    )

--    M a i n    S Q L :
Select A_DATE, A_COUNT, PROFIT, AVG_PREV_2, AVG_NEXT_2
From    ( Select    A_DATE, A_COUNT, PROFIT, 
                    LAG(A_COUNT) Over(Order By A_DATE) "PREV_COUNT",
                    AVG(PROFIT) Over(Order By A_DATE Rows Between 2 Preceding And 1 Preceding) "AVG_PREV_2",
                    AVG(PROFIT) Over(Order By A_DATE Rows Between 1 Following And 2 Following) "AVG_NEXT_2"
          From      sales_list
        )
Where     PREV_COUNT < 100 And A_COUNT >= 100

/*    R e s u l t :
A_DATE      A_COUNT     PROFIT AVG_PREV_2 AVG_NEXT_2
-------- ---------- ---------- ---------- ----------
03.12.23        125        660        190        500
06.12.23        150        710        500        740  */