Using MAX function in DateADD SQL. Error - Invalid aggregate function in where clause [MAX(date)]

Question

I have a table 'CSALES' having columns such as customerid,transactiondate,quantity,price. I'm trying to find customers who have not been active in 1 month from a list of dates present in the transactiondate column. I've tried the following code but I'm unsure about the approach and the code is giving a compilation error

SELECT C.CUSTOMERID
FROM CSALES C

WHERE C.CUSTOMERID  NOT IN
(
    SELECT CS.CUSTOMERID FROM CSALES as CS
    WHERE CS.TRANSACTIONDATE > DATEADD(month, -1, MAX(CS.TRANSACTIONDATE )
);

I'm getting the following error

SQL compilation error: Invalid aggregate function in where clause [MAX(CS.TRANSACTIONDATE)]

What changes should I make in the code to reflect the requirement? Would MAX(date) be a right approach ?

Answer 1

SELECT CUSTOMERID
FROM
  CSSALES
GROUP BY CUSTOMERID
HAVING
  MAX(TRANSACTIONDATE) < ADD_MONTHS(CURRENT_DATE(),-1)

Shawnt00 is right the max date in the transaction table is irrelevant if you just want any customer that hasn't been active in 1 calendar month.

In snowflake use CURRENT_DATE() to get the date portion of Today then ADD_MONTHS(date,int) to get months. Other functions work two but these are pretty easy. If you only want customers to remove duplicate CUSTOMERIDS group by the column.

Answer 2

I think I am about to just repeat Matt's code, but...

With a CTE for some test data:

WITH CSALES(CUSTOMERID, TRANSACTIONDATE) as (
    SELECT * FROM VALUES
    (1, '2022-05-08'::date), -- to recent
    (1, '2021-05-08'::date),
    
    (2, '2021-05-08'::date), -- old enough
    (2, '2020-05-08'::date)
)

We can use HAVING for a post aggregation filter.

SELECT C.CUSTOMERID, MAX(C.TRANSACTIONDATE) as last_trans
FROM CSALES C
GROUP BY 1
HAVING last_trans < DATEADD(month,-1,current_date());

As Matt noted there are few ways to find the "one month ago today" he used ADD_MONTHS, I have used DATEADD

CUSTOMERID	LAST_TRANS
2	2021-05-08

Now this code works the same as:

SELECT CUSTOMERID
FROM (
    SELECT C.CUSTOMERID, MAX(C.TRANSACTIONDATE) as last_trans
    FROM CSALES C
    GROUP BY 1
)
WHERE last_trans < DATEADD(month,-1,current_date());

which gives:

CUSTOMERID
2

Albeit we now have hidden away the last transaction, if that was what was wanted, and added some extra select layers for no high level value.

And thus if we want to hide the last_tran in the HAVING version, we can because we have already working code, we can just push the MAX into the HAVING (and we have Matt's code)

SELECT C.CUSTOMERID
FROM CSALES C
GROUP BY 1
HAVING MAX(C.TRANSACTIONDATE) < DATEADD(month,-1,current_date());

which gives for the demo code:

CUSTOMERID
2

Date Options:

There are a couple ways to alter date/time, depending how you like to order you logic, I tend to prefer DATEADD:

SELECT 
    current_date() as cd_a,
    CURRENT_DATE as cd_b,
    DATEADD(month, -1, cd_a) as one_month_ago_a,
    ADD_MONTHS(cd_a, -1) as one_month_ago_b;

gives:

CD_A	CD_B	ONE_MONTH_AGO_A	ONE_MONTH_AGO_B
2022-05-07	2022-05-07	2022-04-07	2022-04-07

Answer 3

SELECT
  C.CUSTOMERID
FROM
  CSALES C
GROUP BY
  C.CUSTOMERID
HAVING
  MAX(C.TRANSACTIONDATE)
  <
  DATEADD(
    month,
    -1,
    (SELECT MAX(TRANSACTIONDATE) FROM CSALES)
  )

Or, assuming you have a customer table...

SELECT
  *
FROM
  CUSTOMER   C
WHERE
  NOT EXISTS (
    SELECT *
      FROM CSALES   CS
     WHERE CS.CUSTOMERID       = C.ID
       AND CS.TRANSACTIONDATE >= DATEADD(
                                   month,
                                   -1,
                                   (SELECT MAX(TRANSACTIONDATE) FROM CSALES)
                                 )
  )

Demo : dbfiddle

Answer 4

there are multiple possibilities, you must check which is faster

SELECT C.CUSTOMERID
FROM CSALES C

WHERE C.CUSTOMERID  NOT IN
(
    SELECT CS.CUSTOMERID FROM CSALES as CS CROSS JOIN (SELECT MAX(TRANSACTIONDATE) maxdate FROM CSALES) t1
    WHERE CS.TRANSACTIONDATE > DATEADD(month, -1, maxdate)
);

GO

| CUSTOMERID |
| ---------: |
|          4 |

SELECT DISTINCT C.CUSTOMERID
FROM CSALES C CROSS JOIN (SELECT MAX(TRANSACTIONDATE) maxdate FROM CSALES) t1
WHERE  NOT EXISTS (SELECT 1 FROM CSALES WHERE CUSTOMERID = c.CUSTOMERID AND TRANSACTIONDATE > DATEADD(month, -1, maxdate))
;
GO

| CUSTOMERID |
| ---------: |
|          4 |

db<>fiddle here