Find specific sequences

from the Artful Common Queries page


You have a table which tracks hits on named web pages...

CREATE TABLE hits (
  id INT NOT NULL DEFAULT 0,
  page CHAR(128) DEFAULT '',
  time TIMESTAMP NOT NULL DEFAULT 0,
  PRIMARY KEY(id, time)
)

where id is unique to a session. Here is a bit of sample data:

INSERT INTO hits VALUES
  (1, 'A', TIMESTAMPADD(SECOND,10,NOW())),
  (1, 'B', TIMESTAMPADD(SECOND,20,NOW())),
  (2, 'A', TIMESTAMPADD(SECOND,40,NOW())),
  (1, 'A', TIMESTAMPADD(SECOND,50,NOW())),
  (1, 'C', TIMESTAMPADD(SECOND,60,NOW())),
  (3, 'A', TIMESTAMPADD(SECOND,110,NOW())),
  (3, 'A', TIMESTAMPADD(SECOND,120,NOW())),
  (3, 'C', TIMESTAMPADD(SECOND,130,NOW())),
  (2, 'C', TIMESTAMPADD(SECOND,90,NOW())),
  (2, 'A', TIMESTAMPADD(SECOND,100,NOW()));


You desire a count of the number of sessions where a user moved from one particular page directly to another, for example from 'A' to 'C'.

To find the next hit in a given session, scope on id, order by time, and limit the output to one row. Then simply count the rows meeting the page criteria:

SELECT
  COUNT(DISTINCT h1.id) AS 'Moves from A to C'
FROM hits AS h1
WHERE
  h1.page = 'A'
  AND 'C' = (
    SELECT h2.page
    FROM hits AS h2
    WHERE h2.id = h1.id 
      AND h2.time > h1.time
    ORDER BY h2.time LIMIT 1
  );
 ------------------- 
| Moves from A to C |
 ------------------- 
|                 3 |
 ------------------- 



Return to the Artful Common Queries page