Is Today the Customer's First Order Anniversary?-CodePudding

I'm trying to get a MySQL query working to see if today is a customer's first order date. I'm looking at a single database table - tblOrders

My attempt so far, leads to orders returned that happened to fall on today's date at some point, but is not their 'first' order.

SELECT FirstName, LastName, Email, COUNT(OrderID) AS NumberOfOrders, CreateDate
FROM tblOrders
WHERE (MONTH(CreateDate) = MONTH(NOW()) 
    AND DAY(CreateDate) = DAY(NOW()))
GROUP BY Email
ORDER BY CreateDate

I feel like I'm needing a subquery, but I'm spinning my wheels.

CodePudding user response：

If you want the customer's first order, you'd need to use MIN(CreateDate), but aggregates cannot appear in the WHERE they must appear in the HAVING, so your query would be:

SELECT  o.FirstName, 
        o.LastName, 
        o.Email, 
        COUNT(o.OrderID) AS NumberOfOrders, 
        MIN(o.CreateDate) AS FirstOrder
FROM    tblOrders AS o
GROUP BY o.FirstName, o.LastName, o.Email
HAVING  MONTH(MIN(o.CreateDate)) = MONTH(NOW()) 
AND     DAY(MIN(o.CreateDate)) = DAY(NOW());

Your issue here though is that your count will be only counting orders on the same month/day as today, and not all the orders ever for that customer, so if you only want to know the customer details this is fine, but if you need the total number of orders for this customer you'll need a subquery (as you thought):

SELECT o.FirstName, o.LastName, o.Email, o.NumberOfOrders, o.FirstOrder
FROM  ( 
        SELECT o.FirstName, 
               o.LastName, 
               o.Email,
               MIN(o.CreateDate) AS FirstOrder,
               COUNT(*) AS NumberOfOrders
        FROM   tblOrders AS o
        GROUP BY o.FirstName, o.LastName, o.Email
      ) AS o
WHERE MONTH(o.FirstOrder) = MONTH(NOW())
AND   DAY(o.FirstOrder) = DAY(NOW());

CodePudding user response：

WITH cte AS (
    SELECT *, MIN(CreateDate) OVER (PARTITION BY Email) CreateDate 
    FROM tblOrders
)
SELECT FirstName, LastName, Email, COUNT(DISTINCT OrderID) AS NumberOfOrders, CreateDate 
FROM cte
WHERE DATE_FORMAT(CreateDate, '%m%d') = DATE_FORMAT(CURRENT_DATE, '%m%d')
GROUP BY FirstName, LastName, Email, CreateDate