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

Answer 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