Sql: Use Lead() And Partition By To Access To The Next Row Following The Current Row

I have a mobile app browsing history dataset as shown below. DeviceDateTime: Date and Time the User views the page in the mobile app. UserID: each UserID represents a visitor who

Solution 1:

Don't PARTITION BY the devicedatetime, ORDER BY it

select
    UserID,
    DeviceDateTime,
    PageName,
    lead(PageName) over (partitionby UserID orderby DeviceDateTime) as next_pagename
  from seq_fixed

You shouldn't partition by the time because the partition is "what makes all the rows be considered as belonging to the same set" i.e. the userid being the same is "a set of rows" that are ordered by the time (or sequence). If you divide the rows into sets of "per user per second" you aren't getting a "per user" journey of "one user visited ten pages" you're getting "ten usertimes visited one page"

Also, don't use GROUP BY to squish "navs to the same page", use ... from with_next WHERE next_pagename <> pagename

You calculate pageseq but never use it - either order by it in your LEAD (if you think two datetimes for different pages for the same user will be identical at a per-second precision) or dump it

And finally, if "count of user" is supposed to be "the number of distinct users who have navigated from this page to that page" it needs to be count(distinct userid) AS No_of_User, not count(*) - countstar is the "number of times this page navigation has occurred, including multiples by the same user". It could perhaps be handy to include both stats, to get an idea of how many users go round and round

Edit:

Here's the modified version of your query with commented out all the bits I recommend removing:

DROPTABLE IF EXISTS #App;
CREATETABLE #App (
    DeviceDateTime SMALLDATETIME,
    UserID VARCHAR(100),
    PageName VARCHAR(100),
    PageSequence VARCHAR(100))
INSERTINTO #App VALUES
    ('2021-01-19 16:40:00.000','UserA', 'Home', 'Seq_1'),
    ('2021-01-19 16:40:00.000','UserA', 'My Account', 'Seq_2'),
    ('2021-01-19 16:40:07.000','UserA', 'My Activity', 'Seq_3'),
    ('2021-01-19 16:40:07.000','UserA', 'Restaurant Listing', 'Seq_4'),
    ('2021-01-19 16:40:18.000','UserA', 'Restaurant Details Page', 'Seq_5'),
    ('2021-01-19 16:40:31.000','UserA', 'Restaurant Details Page', 'Seq_6'),
    ('2021-01-19 16:40:31.000','UserA', 'Restaurant Booking Confirmation', 'Seq_7'),
    ('2021-01-19 16:40:40.000','UserA', 'Home', 'Seq_8'),
    ('2021-01-19 16:40:45.000','UserA', 'Write To Use', 'Seq_9'), --TYPO ALERT!!!--TYPO ALERT!!!--TYPO ALERT!!!--TYPO ALERT!!!--TYPO ALERT!!!--TYPO ALERT!!!
    ('2021-01-19 16:40:46.000','UserA', 'Home', 'Seq_10'),
    ('2021-01-28 21:11:53.000','UserB', 'Home', 'Seq_1'),
    ('2021-01-28 21:12:01.000','UserB', 'Restaurant Listing', 'Seq_2'),
    ('2021-01-28 21:13:37.000','UserB', 'Restaurant Listing', 'Seq_3'),
    ('2021-02-16 09:43:27.000','UserA', 'Home', 'Seq_1'),
    ('2021-02-16 09:43:43.000','UserA', 'Write To Us', 'Seq_2'),
    ('2021-02-16 09:44:50.000','UserA', 'My Account', 'Seq_3'),
    ('2021-02-16 09:45:03.000','UserA', 'My Activity', 'Seq_4');

DROPTABLE IF EXISTS #SD;
with seq_fixed as
(
  select
    UserID,
    DeviceDateTime,
    PageName/*,
    cast(right(PageSequence, charindex('_', reverse(PageSequence)) - 1) as int) as pagesequencefinal*/from #App
)
, with_next as
(
  select
    UserID,
    DeviceDateTime,
    PageName,
    lead(PageName) over (partitionby UserID/*, DeviceDateTime*/orderby/*UserID,*/ DeviceDateTime ASC) as next_pagename
  from seq_fixed
  /*group by UserID, DeviceDateTime, PageName*/
)
select PageName, next_pagename, count(*) AS No_of_User
into #SD
from with_next
where next_pagename isnotnullgroupby PageName, next_pagename
orderby PageName, next_pagename;

SELECT*FROM #SD

Solution 2:

In your previous request you wanted to only acknowledge a page's first ocurrence per user, so Home->PageX->Home->PageY would be interpreted as Home->PageX->PageY. For this to happen you had to group by user and page to find this first occurrence.

This is not the case in this new request, so don't aggregate:

with seq_fixed as
(select
    userid,
    pagename,
    cast(right(pagesequence, charindex('_', reverse(pagesequence)) - 1) asint) as pagesequencefinal
  from app
)
, with_next as
(select
    userid,
    pagename,
    lead(pagename) over (partition by userid order by pagesequencefinal) as next_pagename
  from seq_fixed
)
select pagename, next_pagename, count(*)
from with_next
where next_pagename isnotnullgroupby pagename, next_pagename
order by pagename, next_pagename;

The only problem with this: When a user ended on PageX yesterday and starts with Home today, this will count as PageX->Home. If you want to prevent this from happening you need some detection for this situation, e.g. don't consider an entry a page change when it's pedecessor is at least 1 hour old or the like. For this you can use your timestamp column and LAG.

Solution 3:

As pointed out by Thorsten Kettner: When a user ended on PageX yesterday and starts with Home today, this will count as PageX->Home. I took his advice and try to prevent this from happening with the timestamp column and LAG function. However, it looks super long. Is there a way I can shorten it?

DROPTABLE IF EXISTS #App
CREATETABLE #App (
    DeviceDateTime SMALLDATETIME,
    UserID VARCHAR(100),
    PageName VARCHAR(100),
    PageSequence VARCHAR(100))
INSERTINTO #App VALUES
    ('2021-01-19 16:40:00.000','UserA', 'Home', 'Seq_1'),
    ('2021-01-19 16:40:00.000','UserA', 'My Account', 'Seq_2'),
    ('2021-01-19 16:40:07.000','UserA', 'My Activity', 'Seq_3'),
    ('2021-01-19 16:40:07.000','UserA', 'Restaurant Listing', 'Seq_4'),
    ('2021-01-19 16:40:18.000','UserA', 'Restaurant Details Page', 'Seq_5'),
    ('2021-01-19 16:40:31.000','UserA', 'Restaurant Details Page', 'Seq_6'),
    ('2021-01-19 16:40:31.000','UserA', 'Restaurant Booking Confirmation', 'Seq_7'),
    ('2021-01-19 16:40:40.000','UserA', 'Home', 'Seq_8'),
    ('2021-01-19 16:40:45.000','UserA', 'Write To Us', 'Seq_9'),
    ('2021-01-19 16:40:46.000','UserA', 'Home', 'Seq_10'),
    ('2021-01-28 21:11:53.000','UserB', 'Home', 'Seq_1'),
    ('2021-01-28 21:12:01.000','UserB', 'Restaurant Listing', 'Seq_2'),
    ('2021-01-28 21:13:37.000','UserB', 'Restaurant Listing', 'Seq_3'),
    ('2021-02-16 09:43:27.000','UserA', 'Home', 'Seq_1'),
    ('2021-02-16 09:43:43.000','UserA', 'Write To Us', 'Seq_2'),
    ('2021-02-16 09:44:50.000','UserA', 'My Account', 'Seq_3'),
    ('2021-02-16 09:45:03.000','UserA', 'My Activity', 'Seq_4');

    With #App2 as (
select
    DeviceDateTime,
    UserID,
    PageName,
    cast(right(PageSequence, charindex('_', reverse(PageSequence)) -1) asint) as pagesequencefinal,
    lead(DeviceDateTime) over (partitionby UserID orderby DeviceDateTime) as next_day,
    cast(lag(DeviceDateTime,1) over (partitionby UserID orderby DeviceDateTime) asdate) as Previous_day,
    CASEWHEN DATEDIFF(day, cast(lag(DeviceDateTime,1) over (partitionby UserID orderby DeviceDateTime) asdate), DeviceDateTime) <1THEN0ELSE1ENDAS Dayend_flag,
    DATEDIFF(day, cast(lag(DeviceDateTime,1) over (partitionby UserID orderby DeviceDateTime) asdate), DeviceDateTime) as test
  from #App
), #App3 as (
select
    DeviceDateTime,
    UserID,
    PageName,
    pagesequencefinal,
    next_day,
    Previous_day,
    Dayend_flag,
    test,
    SUM(Dayend_flag) OVER (ORDERBY UserID, DeviceDateTime, pagesequencefinal) AS Session_Num
  from #App2
), #App4 as (
select
    UserID,
    PageName,
    lead(PageName) over (partitionby Session_Num orderby pagesequencefinal) as next_pagename
  from #App3
)
select PageName, next_pagename, count(*) AS No_of_User
from #App4
where next_pagename isnotnullgroupby PageName, next_pagename
orderby PageName, next_pagename;