Whenever a user visits the home page and then visits checkout pages and buys increasingly expensive products, the nPath query returns the first purchase and the most expensive purchase.
Input
The input table is a collection of clickstream data for different products with price information. Columns userid and sessionid identify the users.
userid | sessionid | productname | pagetype | clicktime | referrer | productprice |
---|---|---|---|---|---|---|
1039 | 1 | sneakers | home | 2009-07-29 20:17:59 | Company1 | 100 |
1039 | 2 | books | home | 2009-04-21 13:17:59 | Company2 | 300 |
1039 | 3 | television | home | 2009-05-23 13:17:59 | Company3 | 500 |
1039 | 4 | envelopes | home | 2009-07-16 11:17:59 | Company4 | 10 |
1039 | 4 | envelopes | home1 | 2009-07-16 11:18:16 | Company4 | 10 |
1039 | 4 | envelopes | page1 | 2009-07-16 11:18:18 | Company4 | 10 |
1039 | 5 | bookcases | home | 2009-08-19 22:17:59 | Company5 | 150 |
1039 | 5 | bookcases | home1 | 2009-08-19 22:18:02 | Company5 | 150 |
1039 | 5 | bookcases | page1 | 2009-08-19 22:18:05 | Company5 | 150 |
1039 | 5 | bookcases | page2 | 2009-08-22 04:20:05 | Company5 | 150 |
1039 | 5 | bookcases | checkout | 2009-08-24 14:30:05 | Company5 | 150 |
1039 | 5 | bookcases | page2 | 2009-08-27 23:03:05 | Company5 | 150 |
1040 | 1 | tables | home | 2009-07-29 20:17:59 | Company5 | 250 |
1040 | 2 | Appliances | home | 2009-04-21 13:17:59 | Company6 | 1500 |
1040 | 3 | laptops | home | 2009-05-23 13:17:59 | Company7 | 800 |
1040 | 4 | chairs | home | 2009-07-16 11:17:59 | Company4 | 400 |
1040 | 4 | chairs | home1 | 2009-07-16 11:18:16 | Company4 | 400 |
1040 | 4 | chairs | page1 | 2009-07-16 11:18:18 | Company4 | 400 |
1040 | 5 | cellphones | home | 2009-08-19 22:17:59 | Company8 | 600 |
1040 | 5 | cellphones | home1 | 2009-08-19 22:18:02 | Company8 | 600 |
1040 | 5 | cellphones | page1 | 2009-08-19 22:18:05 | Company8 | 600 |
1040 | 5 | cellphones | page2 | 2009-08-22 04:20:05 | Company8 | 600 |
1040 | 5 | cellphones | checkout | 2009-08-24 14:30:05 | Company8 | 600 |
1040 | 5 | cellphones | page2 | 2009-08-27 23:03:05 | Company8 | 600 |
... | ... | ... | ... | ... | ... | ... |
SQL Call
SELECT * FROM nPath@coprocessor ( ON aggregate_clicks PARTITION BY sessionid ORDER BY clicktime ASC, productname, pagetype, userid USING Mode (NONOVERLAPPING) Pattern ('H+.D*.X*.P1.P2+') Symbols ( 'true' AS X, pagetype = 'home' AS H, pagetype <> 'home' AND pagetype <> 'checkout' AS D, pagetype = 'checkout' AS P1, pagetype = 'checkout' AND productprice > 100 AND productprice > LAG (productprice, 1, 100::REAL) AS P2 ) Result ( FIRST (productname OF P1) AS first_product, MAX_CHOOSE (productprice, productname OF P2) AS max_product, FIRST (sessionid OF P2) AS sessionid ) ) AS dt;
Output
first_product | max_product | sessionid |
---|---|---|
bookcases | cellphones | 5 |
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.