In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year.
Shouldn't the query optimizer be smart enough to only evaluate existence once for each customer?
--Count transactions on customers that are less than 1 year old SELECT t1.CUSTID,COUNT(*) FROM CUST_TRX t1 WHERE NOT EXISTS ( SELECT FIRST 1 1 FROM CUST_TRX t2 WHERE t2.CUSTID=t1.CUSTID AND t2.DATED<CURRENT_DATE-365 GROUP BY t2.CUSTID ) GROUP BY t1.CUSTID
There are no naturals in my query plan. This query is performing as if the database is running the existence clause for every transaction instead of running it for every customer. Performance is the same if I remove the GROUP BY in the sub-query.
Is there a better way to do this so that I may get better performance out of the database? Hopefully a simple select query will work avoiding a CTE if possible (that would introduce other challenges). Due to other group by criteria (not shown here) I'm not able to simply check MIN(DATED), I really need to perform another query.