Arnab Arnab - 1 month ago 4
SQL Question

Finding entry page, exit page and bounces -sql

My table structure is as follows:

Sessionid Pageurl timestamp
abc1 /testpage1 1465374987308
abc1 /testpage2 1465375020477
abc2 /testpage2 1465374987308


I wish to create a report of entry page count, exit page count and bounces count per page.

For any session, the first page is entry page and last page an exit page.

A bounce occurs when user leaves after viewing the first page(session has a single entry)

Final report would be as below..

pageurl EntrypageCount ExitPagecount BounceCount
/testpage1 1 0 0
/testpage2 1 2 1


I have been able to get bounces but on per day basis.
For bounces, the base select is..

SELECT sessionid, min(timestamp),CASE WHEN count(*) = 1 THEN 1 ELSE 0 END AS bounces
FROM auditdata GROUP BY sessionid.


But can not figure out how to get them by pageurl.

All help is sincerely appreciated.

Thanks

Answer

The following is one way (demo).

SELECT Pageurl,
       COUNT(CASE WHEN timestamp = First THEN 1 END) AS EntrypageCount,
       COUNT(CASE WHEN timestamp = Last THEN 1 END) AS ExitPagecount,
       COUNT(CASE WHEN Count = 1 THEN 1 END) AS BounceCount
FROM   (SELECT Pageurl,
               timestamp,
               MIN(timestamp) OVER (PARTITION BY Sessionid) AS First,
               MAX(timestamp) OVER (PARTITION BY Sessionid) AS Last,
               COUNT(*) OVER (PARTITION BY Sessionid)       AS Count
        FROM   auditdata) T
GROUP  BY Pageurl; 

The above uses window functions, which most modern RDBMSs support, a version without would be.

SELECT Pageurl,
       COUNT(CASE WHEN timestamp = First THEN 1 END) AS EntrypageCount,
       COUNT(CASE WHEN timestamp = Last THEN 1 END) AS ExitPagecount,
       COUNT(CASE WHEN Count = 1 THEN 1 END) AS BounceCount
FROM   auditdata a
       JOIN (SELECT Sessionid,
                    MIN(timestamp) AS First,
                    MAX(timestamp) AS Last,
                    COUNT(*)       AS Count
             FROM   auditdata
             GROUP  BY Sessionid) g
         ON a.Sessionid = g.Sessionid
GROUP  BY Pageurl;
Comments