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 |
-------------------
Last updated 22 May 2024
