Skip to content
Advertisement

put a 1 if the customer has bought in 3 following months sql assistant

I’m a beginner with teradata SQL assistant and I don’t know if it can do what I need.

I have a base with the variables ID, month (or period) and the incomes of that month. What I need is to put a 1 if the client buys in the next 3 months or a 0 if not, and do it for all ID. For example, if I am in month 1 and there’s a purchase in the next 3 months, then put a 1 in that row for that client. In the last periods as there will not be 3 months, an NA appears.

Here is code for the sample data:

I had to do it with R and here they could help me, but now I’ve to do it with teradata sql assistant.

This is what I want:

This was my attempt, but obviously it failed and I didn’t get what I expected.

*rentability is a table created.

Does anyone how to mark with a 1 or with the max of that period? Thanks!

Advertisement

Answer

Consider:

This works by performing a window sum over the 3 following months (we use a range definition instead of a row definition, so this should work even if you have missing records).

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement