1

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  

etc.

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?

0

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
FROM 
(
    SELECT x.journey, count(*) as cnt
    from 
    (
        SELECT ID, STRING_AGG(RTRIM(evar),',') as journey
        FROM journey
        GROUP BY ID
    ) x
    GROUP BY journey
) y;

output:

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

when I add an extra journey like this:

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

output:

**journey,     ranking**
landing,content,homepage,     1
support,content,     2
content,support,     2
| improve this answer | |
  • You might want to include the query into the answer itself, for when the external link goes stale this answer will not have much meaning. – mustaccio Dec 31 '19 at 15:25
  • OK, code of dbfiddle added to answer. – Luuk Dec 31 '19 at 15:32

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.