nPath Example 8: Multiple Partitioned Input Tables and Dimension Input Table - Teradata Vantage

An e-commerce store wants to count the advertising impressions that lead to a user clicking an online advertisement. The example counts the online advertisements that the user viewed and the television advertisements that the user might have viewed.

Input

SQL Call

The tables impressions and clicks have a user_id column, but the table tv_spots is only a record of television advertisements shown, which any user might have seen. Therefore, tv_spots must be a dimension table.

SELECT * FROM nPath@coprocessor (
  ON impressions PARTITION BY userid ORDER BY ts
  ON clicks2 PARTITION BY userid ORDER BY ts
  ON tv_spots DIMENSION ORDER BY ts
  USING
  Mode ('nonoverlapping')
  Symbols (true as imp, true as click, true as tv_imp)
  Pattern ('(imp|tv_imp)*.click')
  Result (COUNT(* of imp) as imp_cnt,
          COUNT (* of tv_imp) as tv_imp_cnt)
) AS dt;

Output