Running Total/Record ID using In-DB tools?

[zachwalz]

Hi all--

I have some ETL processes that use In-DB tools. I use Redshift, so taking the data out and putting it back in can be a process (you have to send to S3, then staging, then production tables).

In an orders table, I'm trying to create a field that identifies the customer's order #. The value would be 1 for their first order, 2 for their second, etc. To do this using standard tools, I sort by date, create a dummy field that only has "1" in it, and do a running total grouped on customer ID.

Does anyone have any ideas about how to recreate this using In-DB tools?

Thanks

-Zach

[JoeM · Accepted solution]

@zachwalz Look at bringing the following SQL expression into the Formula in-db tool.

rank() over (partition by CustomerID order by Date asc)

This will do a new transaction count for each customer - with 1 starting at the earliest transaction for each CustID.

[zachwalz]

Ahhhh you're the best... I knew it was easier than I was making it out to be. Thanks, Joe!

[aman_goyal492]

Thanks for the great post. I am also trying to union some fake records in the In DB workflow. How to do that can some one please tell me?