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:

IF OBJECT_ID('tempdb..#StackTest') IS NOT NULL
    DROP TABLE #StackTest;

CREATE TABLE #StackTest
(Id int
,Month int
,Income int
);

INSERT INTO #StackTest
(Id
,Month
,Income
)
VALUES
(1, 1, 5000),
(1, 2, 0),
(1, 3, 0),
(1',4, 0),
(1,5, 0),
(1,6, 0),
(1, 7, 400),
(1, 8, 0),
(1, 9, 0),
(1, 10, 0),
(1, 11, 0),
(1, 12, 0),
(1, 13, 400),
(2, 1, 5000),
(2, 2, 0),
(2, 3, 100),
(2,4, 0),
(2,5, 0),
(2,6, 0),
(2, 7, 0),
(2, 8, 1500),
(2, 9, 0),
(2, 10, 0),
(2, 11, 0),
(2, 12, 100),
(2, 13, 750),
(3, 1, 0),
(3, 2, 0),
(3, 3, 0),
(3',4, 0),
(3,5, 700),
(3,6, 240),
(3, 7, 100),
(3, 8, 0),
(3, 9, 0),
(3, 10, 0),
(3, 11, 0),
(3, 12, 500),
(3, 13, 760);

 ID | Month | Incomes
  1 |    1   | 5000
  1 |    2   |    0
  1 |    3   |    0
  1 |    4   |    0
  1 |    5   |    0
  1 |    6   |    0
  1 |    7   |  400
  1 |    8   |  300
  1 |    9   |    0
  1 |   10   |    0
  1 |   11   |    0
  1 |   12   |    0
  1 |   13   |  400
  2 |    1   |    0
  2 |    2   |  100
  2 |    3   |    0
  2 |    4   |    0
  2 |    5   |    0
  2 |    6   |    0
  2 |    7   |    0
  2 |    8   | 1500
  2 |    9   |    0
  2 |   10   |    0
  2 |   11   |    0
  2 |   12   |  100
  2 |   13   |  750
  3 |    1   |    0
  3 |    2   |    0
  3 |    3   |    0
  3 |    4   |    0
  3 |    5   |  700
  3 |    6   |  240
  3 |    7   |  100
  3 |    8   |    0
  3 |    9   |    0
  3 |   10   |    0
  3 |   11   |    0
  3 |   12   |  500
  3 |   13   |  760

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:

 ID | Month | Incomes | Quarterly
  1 |    1   | 5000     |    0
  1 |    2   |    0     |    0
  1 |    3   |    0     |    0
  1 |    4   |    0     |    1
  1 |    5   |    0     |    1
  1 |    6   |    0     |    1
  1 |    7   |  400     |    1
  1 |    8   |  300     |    0
  1 |    9   |    0     |    0
  1 |   10   |    0     |    0
  1 |   11   |    0     |   NA
  1 |   12   |    0     |   NA
  1 |   13   |  400     |   NA
  2 |    1   |    0     |    1
  2 |    2   |  100     |    0
  2 |    3   |    0     |    0
  2 |    4   |    0     |    0
  2 |    5   |    0     |    1
  2 |    6   |    0     |    1
  2 |    7   |    0     |    1
  2 |    8   | 1500     |    0
  2 |    9   |    0     |    1
  2 |   10   |    0     |    1
  2 |   11   |    0     |   NA
  2 |   12   |  100     |   NA
  2 |   13   |  750     |   NA
  3 |    1   |    0     |    0
  3 |    2   |    0     |    1
  3 |    3   |    0     |    1
  3 |    4   |    0     |    1
  3 |    5   |  700     |    1
  3 |    6   |  240     |    1
  3 |    7   |  100     |    0
  3 |    8   |    0     |    0
  3 |    9   |    0     |    1
  3 |   10   |    0     |    1
  3 |   11   |    0     |   NA
  3 |   12   |  500     |   NA
  3 |   13   |  760     |   NA

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

select Id,Month,Incomes, SUM(Incomes) OVER (PARTITION BY Id ORDER BY Month ROWS 3 PRECEDING) AS Quarterly from rentability  order by Id, Month

*rentability is a table created.

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

Answer

Consider:

select 
    t.*,
    case when sum(income) over(
        partition by id 
        order by month 
        range between 1 following and 3 following
    ) > 0 
        then 1 
        else 0
    end quaterly
from StackTest t

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

Advertisement

Answer