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) );