For each row, this SQL-MapReduce call counts the number of preceding rows in a given sequence (including the current row). The ORDER BY clause specifies DESC because the pattern must be matched over the rows preceding the start row, while the semantics dictate that the pattern be matched over the rows following the start row.
SELECT sessionid, pageid, rank FROM nPath (
ON clicks1
PARTITION BY sessionid
ORDER BY ts DESC
MODE (OVERLAPPING)
PATTERN ('A*')
SYMBOLS (true AS A)
RESULT (FIRST(sessionid OF A) AS sessionid,
FIRST (pageid OF A) AS pageid,
COUNT (* OF A) AS rank)
);