16.20 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
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