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

@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.