17.10 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

This example finds sequences of increasing balances. This implies that we reset whenever the current balance is less than or equal to the preceding balance.

   SELECT account_key, month, balance,
   ROW_NUMBER() over
        (PARTITION BY account_key
         ORDER BY month
         RESET WHEN balance /* current row balance */ <=
         SUM(balance) over (PARTITION BY account_key ORDER BY month
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) /* prev row */
         ) - 1 /* to get the count started at 0 */ as balance_increase
   FROM accounts;

The possible results of the preceding SELECT appear in the table below:

account_key        month      balance  balance_increase
-----------        -----      -------  ----------------
          1            1           60                 0
          1            2           99                 1
          1            3           94                 0
          1            4           90                 0
          1            5           80                 0
          1            6           88                 1
          1            7           90                 2
          1            8           92                 3
          1            9           10                 0
          1           10           60                 1
          1           11           80                 2
          1           12           10                 0