I have a table which includes the following:

ID    evar     event_date   ranking  
1    landing   2019-01-01      1   
1    content   2019-01-02      2  
1    homepage  2019-01-03      3  
2    support   2019-02-01      1  
2    content   2019-02-02      2  
3    content   2019-01-23      1  
3    support   2019-01-24      2  


I am trying to map customer journeys, so I've ranked the pages that were visited by date.
I need to find the top journeys on the website - so it needs to be at this level, but aggregated by the number of users.

For example: ID 1 made the journey landing > content > homepage;
I would like to see who else made that same journey (without me manually checking)

How can I write such a query?


Your data is not enough to give the query a good test, but this FIDDLE seems to do it.

code of dbfille added:

SELECT y.journey, RANK() OVER (ORDER BY cnt DESC) as ranking
    SELECT x.journey, count(*) as cnt
        SELECT ID, STRING_AGG(RTRIM(evar),',') as journey
        FROM journey
        GROUP BY ID
    ) x
    GROUP BY journey
) y;


journey,     ranking
content,support,     1
landing,content,homepage,     1
support,content,     1

when I add an extra journey like this:

(4    ,'landing'   ,'2019-01-01',      1   ),
(4    ,'content'   ,'2019-01-02',      2   ),
(4    ,'homepage'  ,'2019-01-03',      3   );


**journey,     ranking**
landing,content,homepage,     1
support,content,     2
content,support,     2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.