16.10 - Example 6: Find Data for Sessions That Checked Out 3-6 Products - Teradata Database

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K

SQL-MapReduce Call

SELECT * FROM NPATH (
   ON aggregate_clicks
   PARTITION BY sessionid
   ORDER BY clicktime
   USING
   MODE (nonoverlapping)
   PATTERN ('H+.D*.C{3,6}.D')
   SYMBOLS (pagetype = 'home' AS H, pagetype='checkout' AS C,
            pagetype<>'home' AND pagetype<>'checkout' AS D)
   RESULT (FIRST(sessionid OF C) AS sessionid,
           max_choose(productprice, productname OF C) AS
           most_expensive_product,
           MAX(productprice OF C) AS max_price,
           min_choose(productprice, productname of C) AS
           least_expensive_product,
           MIN(productprice OF C) AS min_price)
) as dt ORDER BY dt.sessionid;

Output

nPath Range-Matching Example 6 Output Table
sessionid most_expensive_product max_price least_expensive_product min_price
5 cellphones 600 bookcases 150