I was given a SQL problem that has me stumped and was wondering if someone can shed some light on the best way to do this.

QUESTION: Given the table "product_price" represents average price for a product for a period of time. All days from the range occur in the "day" column, which is NOT sorted.

Find anomalies in the product prices during this time period, more specifically find days when the price spiked (days on which the price was strictly larger than on the day before and the day after).

The first and last days can never be spikes.

EXPECTED OUTCOME: Output should be ordered in increasing order by "day"

QUERY: I have tired this query

SELECT day FROM (
    SELECT
        day
        ,prev_price - price AS pre_diff
        ,price - next_price AS next_diff
    FROM (
        SELECT 
            day
            ,price
            ,LEAD(price) OVER(price) AS prev_price
            ,LAG(price) OVER(price) AS next_price
        FROM product_price
        ORDER BY day ASC
    ) a
) b
WHERE pre_diff > 1 and next_diff > 1
ORDER by day;

UPDATE: I think I solved it. Thanks @hsnsd for the tip. Would still appreciate any input if there is a better way to write this query.

SELECT day FROM (
    SELECT
        day
        ,price
        ,prev_price
        ,next_price
        ,price - prev_price AS pre_diff
        ,next_price - price AS next_diff
    FROM (
        SELECT 
            day
            ,price
            ,COALESCE(LAG(price) OVER(ORDER BY day ASC),0) AS prev_price
            ,COALESCE(LEAD(price) OVER(ORDER BY day ASC),0) AS next_price
        FROM prices
    ) a
) b
WHERE (day NOT IN (SELECT MIN(day) FROM  prices) AND
day NOT IN (SELECT MAX(day) FROM  prices))
AND (pre_diff > 0 AND  next_diff < 0)
ORDER BY "day";

CodePudding user response：

It looks like the following should give you the desired result:

with s as (
  select *, 
    Lag(Price, 1, Price) over(order by day) p,
    Lead(Price, 1, Price) over(order by day) n
  from prices
)
select day
from s
where p < Price and n < Price;