Return the First, Second and Third Earliest Purchase Dates per Purchaser

Question

for a list of purchaser email addresses, I am trying to return one line per purchaser that has the columns '1stOrderDate', '2ndOrderDate', '3rdOrderDate' and 'TotalNumberofOrders'

I have tried using the ROW_Number function in the WHERE clause of subqueries but it reports that Windowed functions aren't allowed in the WHERE clause, so any help on how I fill in the ???s below would be gratefully received!

SELECT 
PT.email AS 'Email',
MIN(OT.orderdate) AS '1stOrderDate',
???               AS '2ndOrderDate',
???               AS '3rdOrderDate',
COUNT(DISTINCT OT.order_reference) AS 'TotalNumberOfOrders'    

FROM dbo.Orders AS OT
JOIN dbo.Purchaser AS PT ON OT.account_reference = PT.account_reference

GROUP BY PT.Email

Gordon Linoff · Answer 1 · 2014-12-12T12:25:50.697

1

You can do this with row_number() and conditional aggregation:

SELECT PT.email,
       MAX(CASE WHEN seqnum = 1 THEN OT.OrderDate END) as OrderDate_1,
       MAX(CASE WHEN seqnum = 2 THEN OT.OrderDate END) as OrderDate_2,
       MAX(CASE WHEN seqnum = 3 THEN OT.OrderDate END) as OrderDate_3,
       COUNT(DISTINCT OT.order_reference) AS TotalNumberOfOrders   
FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY account_reference ORDER BY o.orderdate) as seqnum
      FROM dbo.Orders o
     ) OT JOIN
     dbo.Purchaser PT
     ON OT.account_reference = PT.account_reference
GROUP BY PT.Email

A couple of notes:

Don't use single quotes for column aliases. Instead, choose column names that do not require escaping.
For the segnum = 1 logic, you can use MIN(), but I think consistency is a benefit here.

EDIT:

My guess is that the problem is the difference between a account_reference and email. Try this:

SELECT email,
       MAX(CASE WHEN seqnum = 1 THEN OT.OrderDate END) as OrderDate_1,
       MAX(CASE WHEN seqnum = 2 THEN OT.OrderDate END) as OrderDate_2,
       MAX(CASE WHEN seqnum = 3 THEN OT.OrderDate END) as OrderDate_3,
       COUNT(DISTINCT OT.order_reference) AS TotalNumberOfOrders   
FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY pt.email ORDER BY o.orderdate) as seqnum
      FROM dbo.Orders o JOIN
           dbo.Purchaser PT
           ON OT.account_reference = PT.account_reference
     ) OT 
GROUP BY PT.Email

edited Dec 12 '14 at 12:25

answered Dec 10 '14 at 12:57

Gordon Linoff

1,242,037
58
646
786

thanks Gordon, but just noticed some of the order 2 and order 3 dates aren't pulling through even when TotalNumberOfOrders >1, any ideas? – J-BAC Dec 10 '14 at 14:52
If you have multiple orders on the same date, they will be treated as different dates. The `row_number()` counts orders, not dates. You could try `dense_rank()` instead of `row_number()`. – Gordon Linoff Dec 11 '14 at 00:18
There can potentially be multiple orders on the same date and that is fine to report the same date for the second or third dates as applicable. Dense_Rank appears to have the same problem with some pulling through as NULLs – J-BAC Dec 11 '14 at 16:11
@J-BAC . . . The `row_number()` is what you need. Perhaps this happens when some customers have fewer than three orders. – Gordon Linoff Dec 11 '14 at 16:18
Unfortunately that's not it...I have sorted by descending TotalNumberOfOrders and the top customer with >60 orders only has a first date of purchase, the second and third dates are pulling through as NULL – J-BAC Dec 12 '14 at 08:58

Return the First, Second and Third Earliest Purchase Dates per Purchaser

1 Answers1