0

We are collection every user click as follows in a log file

Current Url -------------- Refer Url  ------- User ---- Timestamp
/B                          /A                 usr1      1/1/2016 : ...

/C                          /B                 usr1       ....

/D                          /C                 Usr1       .....

/F                          NULL               usr2       .....

What is the best way to construct user click path?

How to do that in SQL or Do I have to write a custom program?

Fahad
  • 1,261
  • 9
  • 19
  • 1
    looks to be a recursive Common Table Expression (cte)... For XML path in MSFT SQL Server or connect by prior in oracle.... what RDBMS is this? oracle, MSFT SQL Sever, mySQL? bit baffled by the data too... how do the user ever get to A... and F seems to be a start and an end. – xQbert Mar 11 '16 at 23:31
  • What do you mean by user click path ? Is it : for a given user, get the ordered list of all the urls he visited ? Also, your log file only has current url, refer url or does it have also user_id in it ? Finally, can we assume your log file is actually a table and we can run our sql on it ? – Pholochtairze Mar 11 '16 at 23:31
  • 1
    It's easier if you keep a timestamp for each entry. – ZippyV Mar 11 '16 at 23:36
  • The term you're looking for is a hierarchical recursive query. perhaps: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql if mySQL or http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query?lq=1 – xQbert Mar 11 '16 at 23:38
  • SQL Server and yes you can assume there is a timestamp – Fahad Mar 12 '16 at 00:04
  • Then just order by the timestamp column. – ZippyV Mar 12 '16 at 00:07

0 Answers0