Use this query to find out the most popular paths your users take in a single session. Results with the highest number of VISIT_COUNTS are the most popular, and so on.

Use path analysis to analyze consecutive events by a user, or to determine popular user paths in your website. For example, a user logs in and continues to navigate through a game, registering additional events ('up level', 'message', 'buy', etc.), during a single usage session.

Please see our Path Analysis documentation for more information on path-related queries.

Syntax:

SELECT count(session_internal_id) AS visit_count
    , path_to_string(path) AS path
from cooladata
WHERE date_range(BETWEEN 2014-02-01 AND 2014-02-28)
CLUSTER PATH BY session REMOVE DUPLICATES MATCH any
WHERE date_range(BETWEEN 2014-02-01 AND 2014-02-28)
cluster path by session REMOVE DUPLICATES MATCH any 
GROUP BY path
ORDER BY visit_count
DESC LIMIT 20

Tips/Best Practices:

• Use REMOVE DUPLICATES to remove duplicate actions, and treat repeated events as one event. For example, if a user watches a YouTube playlist and clicks 'next' several times to continue to the next song in the playlist, you can filter out and remove the duplicate actions.

• When using CLUSTER PATH BY, it is possible (although not mandatory) to use HAVING PATH MATCH (an inner clause of CLUSTER PATH BY). This clause allows you to apply filtering.

• Please see our Path Analysis documentation for additional path-related tips and best practices.