Find if a number is always increasing/decreasing-CodePudding

This is related to a question I tried answering here: Identifying users with a downward trend SQL.

Given the following starting place:

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10)
)
SELECT
    customer, array_agg(quantity) from tbl group by customer

Is there a way to aggregate on a customer and see if a value is always increasing or decreasing? For example, in the above, we'd get an array (if helpful?) that is always increasing:

CUSTOMER    ARRAY_AGG(QUANTITY)
Lucus   [   5,   6,   10 ]

Is there a function that could do something along the lines of:

>>> bool(reduce(lambda x,y: x if x>y else False, [3,2,4]))
False
>>> bool(reduce(lambda x,y: x if x>y else False, [3,2,1]))
True

CodePudding user response：

This looks at each pair of adjacent numbers in the series and the detects whether any of the differences were negative:

with data as (
    select customer,
        quantity
          - lag(quantity)
              over (partition by customer order by purchasedate) as diff
    from tbl
)
select customer,
    case when
      count(case when diff >= 0 then 1 end) = count(diff)
        then 'Y' else 'N' end as Increasing
from data
group by customer

If you're interested in detecting monotonically decreasing series as well then you could use similar logic.

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=4c7ec7f328091e0799fadb28f1bd4ce3