For sessions where the user checked between three and six products (exclusive), return the names of the most and least expensive products, the maximum price of the most expensive product, and the minimum price of the least expensive product.
Input
- aggregate_clicks, as in LAG and LEAD Expressions Example 2 (under nPath Symbols)
SQL Call
SELECT * FROM nPath@coprocessor ( 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;
Output
sessionid | most_expensive_product | max_price | least_expensive_product | min_price |
---|---|---|---|---|
5 | cellphones | 600 | bookcases | 150 |