This SQL-MapReduce call finds the user click-paths that start at pageid 50 and proceed either to pageid80 or to pages in category 9 or category 10, finds the pageid of the last page in the path, counts the visits to page 80, and returns the maximum count for each last page, by which it sorts the output. The query ignores paths of fewer than five pages and pages for which category is less than zero.
SELECT last_pageid, MAX(count_page80) FROM nPath (
ON (SELECT * FROM clicks1 WHERE category >= 0)
PARTITION BY sessionid ORDER BY ts
PATTERN ('A.(B|C)*')
MODE (OVERLAPPING)
SYMBOLS (pageid = 50 AS A,
pageid = 80 AS B,
pageid <> 80 AND category IN (9,10) AS C)
RESULT (LAST(pageid OF ANY (A,B,C)) AS last_pageid,
COUNT (* OF B) AS count_page80,
COUNT (* OF ANY (A,B,C)) AS count_any)
) WHERE count_any >= 5
GROUP BY last_pageid
ORDER BY MAX(count_page80);