0

I'm trying to see the order date in an ascending order for each customer and also how many order dates each customer has so I know how many times a customer has ordered.

I've tried order by date but that is just ordering the whole dates(ModifiedTimestamp) and not per each customer

The data extension attached

Improve this question
5
  • Can you order by customer then date? I.e. two fields in the order by.
    – Phil W
    May 13, 2020 at 8:29
  • sorry, not sure if I understand what you mean. Can you please expand a bit
    – Duicug
    May 13, 2020 at 8:36
  • I'm not clear on whether you are writing the query directly (or through a UI), but SQL (and SOQL) both support something like "ORDER BY Field1 DESC, Field2 ASC, ..." allowing you to order first by Field1 and then, for those records where Field1 is the same, by Field2 etc.
    – Phil W
    May 13, 2020 at 8:49
  • I'm writing this in the SQL Query activity from Automation Studio. I couldn't make it work with the ORDER BY function, maybe I'm not using it correctly
    – Duicug
    May 13, 2020 at 9:00
  • 1
    Does this answer your question? How to use SQL ORDER BY statement in marketing cloud error says requires TOP
    – Johannes Schapdick
    May 13, 2020 at 9:23

1 Answer 1

Reset to default
0

Assuming you have below column names in your data extension and each date record represents an order date. Please use the below SQL code

SELECT SubscriberKey, 
ModifiedTimestamp,
COUNT(ModifiedTimestamp)as Total Orders, 
FROM <Data Extension Name>
GROUP BY SubscriberKey
ORDER BY MidifiedTimestamp ASC
Improve this answer

Not the answer you're looking for? Browse other questions tagged .