Input
The input table has web clickstream data from a set of users with multiple sessions or sequences.
| session_id | page | datestamp |
|---|---|---|
| 0 | ACCOUNT SUMMARY | 2004-03-17 16:35:00 |
| 0 | FAQ | 2004-03-17 16:38:00 |
| 0 | ACCOUNT HISTORY | 2004-03-17 16:42:00 |
| 0 | FUNDS TRANSFER | 2004-03-17 16:45:00 |
| 0 | ONLINE STATEMENT ENROLLMENT | 2004-03-17 16:49:00 |
| 0 | PROFILE UPDATE | 2004-03-17 16:50:00 |
| 0 | ACCOUNT SUMMARY | 2004-03-17 16:51:00 |
| 0 | CUSTOMER SUPPORT | 2004-03-17 16:53:00 |
| 0 | VIEW DEPOSIT DETAILS | 2004-03-17 16:57:00 |
| 1 | ACCOUNT SUMMARY | 2004-03-18 01:16:00 |
| 1 | ACCOUNT SUMMARY | 2004-03-18 01:18:00 |
| 1 | FAQ | 2004-03-18 01:20:00 |
| 1 | ACCOUNT SUMMARY | 2004-03-18 01:21:00 |
| 1 | FUNDS TRANSFER | 2004-03-18 01:24:00 |
| 1 | ACCOUNT HISTORY | 2004-03-18 01:25:00 |
| 1 | VIEW DEPOSIT DETAILS | 2004-03-18 01:27:00 |
| 1 | ACCOUNT SUMMARY | 2004-03-18 01:27:00 |
| 1 | ACCOUNT HISTORY | 2004-03-18 01:28:00 |
| 2 | ACCOUNT SUMMARY | 2004-03-18 09:22:00 |
| 2 | ACCOUNT SUMMARY | 2004-03-18 09:23:00 |
| 2 | ACCOUNT SUMMARY | 2004-03-18 09:25:00 |
| 2 | ACCOUNT HISTORY | 2004-03-18 09:27:00 |
| 2 | FUNDS TRANSFER | 2004-03-18 09:31:00 |
| 2 | ACCOUNT SUMMARY | 2004-03-18 09:31:00 |
| 2 | FAQ | 2004-03-18 09:33:00 |
| 2 | FAQ | 2004-03-18 09:36:00 |
| 3 | ACCOUNT SUMMARY | 2004-03-18 22:41:00 |
| 3 | ACCOUNT HISTORY | 2004-03-18 22:45:00 |
| 3 | ACCOUNT SUMMARY | 2004-03-18 22:47:00 |
| 3 | ACCOUNT HISTORY | 2004-03-18 22:49:00 |
| 3 | FAQ | 2004-03-18 22:50:00 |
| 3 | ACCOUNT SUMMARY | 2004-03-18 22:53:00 |
| 3 | ACCOUNT SUMMARY | 2004-03-18 22:55:00 |
| 4 | ACCOUNT SUMMARY | 2004-03-19 08:33:00 |
| 4 | FAQ | 2004-03-19 08:36:00 |
| 4 | VIEW DEPOSIT DETAILS | 2004-03-19 08:38:00 |
| 4 | FAQ | 2004-03-19 08:41:00 |
| 5 | ACCOUNT SUMMARY | 2004-03-19 10:06:00 |
| 5 | FUNDS TRANSFER | 2004-03-19 10:09:00 |
| 5 | VIEW DEPOSIT DETAILS | 2004-03-19 10:11:00 |
| 5 | VIEW DEPOSIT DETAILS | 2004-03-19 10:13:00 |
| 5 | ACCOUNT HISTORY | 2004-03-19 10:14:00 |
SQL Call
SELECT * FROM FrequentPaths(
ON bank_web_clicks1 AS InputTable
OUT TABLE OutputTable(output1)
USING
PartitionColumns('session_id')
TimeColumn('datestamp')
ItemColumn('page')
MinSupport(2)
) AS dt ;
Output
message --------------------------------- Finished. 69 patterns were found. SELECT CAST(pattern AS varchar(500)), support, length FROM output1 ORDER BY 3 DESC, 2 DESC; pattern support length ------------------------------------------------------------------------------- ------- ------ account summary;account summary;faq;account summary;account summary 2 5 account summary;account summary;account summary;account history;account summary 2 5 account summary;account summary;account history;account summary 3 4 account summary;account summary;account summary;account summary 3 4 account summary;funds transfer;view deposit details;account history 2 4 account summary;faq;account summary;account summary 2 4 account summary;account history;account summary;faq 2 4 account summary;account history;account summary;account history 2 4 account summary;account summary;account summary;account history 2 4 account summary;faq;funds transfer;view deposit details 2 4 account summary;faq;account history;account summary 2 4 account summary;account summary;account history;faq 2 4 account summary;account summary;faq;account summary 2 4 account summary;faq;account summary;view deposit details 2 4 account summary;account summary;account summary;funds transfer 2 4 account summary;faq;account history;view deposit details 2 4 account summary;faq;funds transfer;account summary 2 4 account summary;account history;account summary 4 3 account summary;faq;view deposit details 3 3 account summary;account summary;account history 3 3 account summary;account summary;account summary 3 3 account summary;funds transfer;view deposit details 3 3 account summary;faq;account summary 3 3 account summary;account summary;faq 3 3 account summary;account history;view deposit details 2 3 account summary;account summary;view deposit details 2 3 faq;account history;view deposit details 2 3 account summary;funds transfer;account history 2 3 account summary;faq;funds transfer 2 3 faq;funds transfer;account summary 2 3 account summary;faq;faq 2 3 account summary;account history;funds transfer 2 3 account summary;view deposit details;account history 2 3 account summary;funds transfer;account summary 2 3 faq;account summary;view deposit details 2 3 account summary;account summary;funds transfer 2 3 faq;funds transfer;view deposit details 2 3 account summary;account history;faq 2 3 faq;account summary;account summary 2 3 account history;account summary;faq 2 3 account history;account summary;account history 2 3 account summary;account history;account history 2 3 funds transfer;view deposit details;account history 2 3 account summary;faq;account history 2 3 faq;account history;account summary 2 3 account summary;faq 5 2 account summary;account history 5 2 account summary;account summary 4 2 account history;account summary 4 2 account summary;funds transfer 4 2 account summary;view deposit details 4 2 faq;account summary 3 2 funds transfer;view deposit details 3 2 faq;view deposit details 3 2 account history;faq 2 2 faq;account history 2 2 account history;account history 2 2 funds transfer;account history 2 2 account history;funds transfer 2 2 faq;funds transfer 2 2 account history;view deposit details 2 2 view deposit details;account history 2 2 faq;faq 2 2 funds transfer;account summary 2 2 account summary 6 1 account history 5 1 faq 5 1 funds transfer 4 1 view deposit details 4 1
Download a zip file of all examples and a SQL script file that creates their input tables.