Trying to identify a list of customers who's quantity decreases from their previous purchase.

In this example we see that with each new purchase Mary's quantity decreases over time. However, while Bob shows a decline, he would not yield in the results because on 9/19 he purchased 8 quantities which is greater than his previous purchase of 5.

I'm trying to figure out a query for this for the life of me I can't seem to get it together

Customer    PurchaseDate    Quantity
Bob         9/1/2021        10
Bob         9/10/2021       6
Bob         9/18/2021       5
Bob         9/19/2021       8
Mary        9/1/2021        10
Mary        9/10/2021       6
Mary        9/18/2021       5
Mary        9/19/2021       3
Frank       9/1/2021        5
Lucus       9/1/2021        5
Lucus       9/10/2021       6
Lucus       9/18/2021       10

End results should be

Customer
Mary

CodePudding user response：

This is a bit tricky, and to find results that are steadily increasing or decreasing you would probably want to use the MATCH_RECOGNIZE clause, which MySQL doesn't (yet) support. This way you can define a pattern whereby each qty is less than than the previous value. Additionally, you could probably do this with a recursive cte, but that would be outside of my abilities.

Here is what I came up with, with the caveat that it only compares the first and last values:

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Bob',         '9/1/2021',        10),
    ('Bob',         '9/10/2021',       6),
    ('Bob',         '9/18/2021',       5),
    ('Bob',         '9/19/2021',       8),
    ('Mary',        '9/1/2021',        10),
    ('Mary',        '9/10/2021',       6),
    ('Mary',        '9/18/2021',       5),
    ('Mary',        '9/19/2021',       3),
    ('Frank',       '9/1/2021',        5),
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10)
)

SELECT
    DISTINCT customer
FROM
    tbl
QUALIFY
      FIRST_VALUE(quantity) OVER (partition BY customer ORDER BY purchasedate)
    > LAST_VALUE(quantity)  OVER (PARTITION BY customer ORDER BY purchasedate)

Which gives:

CUSTOMER
Bob
Mary

Or, to get strictly decreasing with a known max, you can chain them all together which gets pretty ugly:

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Bob',         '9/1/2021',        10),
    ('Bob',         '9/10/2021',       6),
    ('Bob',         '9/18/2021',       5),
    ('Bob',         '9/19/2021',       8),
    ('Mary',        '9/1/2021',        10),
    ('Mary',        '9/10/2021',       6),
    ('Mary',        '9/18/2021',       5),
    ('Mary',        '9/19/2021',       3),
    ('Frank',       '9/1/2021',        5),
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10)
)

SELECT
    DISTINCT customer
FROM
    tbl
    qualify 
        (NTH_VALUE(quantity, 1) OVER (partition BY customer ORDER BY purchasedate) >= NTH_VALUE(quantity, 2) OVER (partition BY customer ORDER BY purchasedate))
        and ((NTH_VALUE(quantity, 2) OVER (partition BY customer ORDER BY purchasedate) >= NTH_VALUE(quantity, 3) OVER (partition BY customer ORDER BY purchasedate)) or (NTH_VALUE(quantity, 3) OVER (partition BY customer ORDER BY purchasedate) is null))
        and ((NTH_VALUE(quantity,3) OVER (partition BY customer ORDER BY purchasedate) >= NTH_VALUE(quantity, 4) OVER (partition BY customer ORDER BY purchasedate)) or (NTH_VALUE(quantity, 4) OVER (partition BY customer ORDER BY purchasedate) is null))

Which gives:

CUSTOMER
Mary

Though for an unknown amount I would think match_recognize would be the best solution (or you could add in some recursion or a custom function).

CodePudding user response：

SELECT Customer
FROM ( SELECT CASE WHEN Customer = @customer AND Quantity > @quantity
                   THEN 1
                   ELSE 0 
                   END AS increase_detected,
              @customer := Customer Customer,
              PurchaseDate,
              @quantity := Quantity Quantity
       FROM test
       CROSS JOIN ( SELECT @customer := NULL, @quantity := NULL ) init_variables
       ORDER BY Customer, PurchaseDate
     ) subquery
GROUP BY Customer
HAVING NOT SUM(increase_detected);

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=68b75b0df7fe4b383896e78db0caa569