I have a table of "customers", "count" and "datefield". I want to get the dates when a customer has a "count" of below 100. If the "count" remains below 100 then I am expecting only the first date when the "count" dropped below 100. If the "count" goes back up to 100 and drops again, I need that date when the "count" dropped below 100 again.

example data:

I am expecting a SQL statement which would return the times the customer had a count below 100.

Expected result:

Any help would be appreciated! Thanks

CodePudding user response：

Compare the previous count per customer.
Which you can get via the LAG function.

SELECT q.Customer, q.Count, q.datefield
FROM
(
  SELECT *
  , LAG(t.Count) OVER (PARTITION BY t.Customer ORDER BY t.datefield) AS prev_count
  FROM your_table t
) q
WHERE q.Count < 100 AND coalesce(q.prev_count,100) >= 100
ORDER BY q.Customer, q.datefield;

Test on db<>fiddle here