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

  • Can you order by customer then date? I.e. two fields in the order by. – Phil W May 13 at 8:29
  • sorry, not sure if I understand what you mean. Can you please expand a bit – Duicug May 13 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 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 at 9:00
  • 1
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 or ask your own question.