Interview Guides

Most Common User Navigation Paths | Dataford Interview Questions - Dataford - Ace your Interview

Most Common User Navigation Paths

Hard

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsWindow FunctionsCTEs

Also asked at

Problem

Task

NovaFlow wants to understand how users move through its product. Write a PostgreSQL query to identify the most common 3-step navigation paths users take within a session.

Use the event stream to build ordered page paths from consecutive events. Only include sessions tied to active users, and ignore events where page_name is NULL.

Requirements

Build 3-step paths using each user's ordered events within the same session.
Use event timestamp order, and break ties with event_id.
Only include sessions from users where users.is_active = TRUE.
Exclude incomplete paths where step 2 or step 3 is missing.
Return each unique path, the number of distinct sessions that followed it, and its rank by popularity.
Sort by path rank, then path name.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	User name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is active

`sessions`

column	type	description
session_id	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Session start time
device_type	VARCHAR(50)	Device used for the session

`events`

column	type	description
event_id	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Event timestamp
page_name	VARCHAR(100)	Product page or screen name
event_type	VARCHAR(50)	Event type

Sample Data

`users`

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false

`sessions`

session_id	user_id	session_start	device_type
101	1	2024-03-01 09:00:00	web
102	2	2024-03-01 10:00:00	ios
103	3	2024-03-01 11:00:00	web

`events`

event_id	session_id	event_time	page_name	event_type
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1004	102	2024-03-01 10:00:10	Home	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	3	1
Search > Product > Cart	2	2
Home > Product > Cart	1	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	User's full name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is currently active

sessions

Column	Type	Description
session_idPK	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Timestamp when the session started
device_type	VARCHAR(50)	Device type used in the session

events

Column	Type	Description
event_idPK	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Timestamp of the event
page_name	VARCHAR(100)	Page or screen viewed by the user
event_type	VARCHAR(50)	Type of event recorded

users

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false
4	Daniel Wu	2024-01-18	true
5	Ella Singh	2024-01-20	true
6	Farah Ali	2024-01-22	false
7	Gavin Ross	2024-01-25	true
8	Hana Lee	2024-01-28	true
1	Riya Kapoor	2024-01-13	true
2	Kevin Patel	2024-01-14	false
3	Chloe Kim	2024-01-08	true
4	Isaac Park	2024-01-27	false
5	Chloe Kim	2024-01-19	true
6	Ben Ortiz	2024-01-26	true
7	Gavin Ross	2024-01-17	true
8	Quinn Foster	2024-01-08	false
9	Kevin Patel	2024-01-11	false
10	Ximena Cruz	2024-01-10	true
11	Quinn Foster	2024-01-26	true
12	Riya Kapoor	2024-01-08	true
13	Quinn Foster	2024-01-16	true
14	Daniel Wu	2024-01-09	false
15	Farah Ali	2024-01-24	false
16	Farah Ali	2024-01-30	false
17	Quinn Foster	2024-01-30	false
18	Tara Nguyen	2024-01-11	true
19	Yusuf Malik	2024-01-11	true
20	Owen Price	2024-01-17	false
21	Quinn Foster	2024-01-13	true

events

event_id	session_id	event_time	page_name	event_type
1004	102	2024-03-01 10:00:10	Home	view
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view
1007	102	2024-03-01 10:02:00	Cart	view
1008	103	2024-03-01 11:00:05	Home	view
1009	103	2024-03-01 11:00:40	Search	view
1010	103	2024-03-01 11:01:20	Product	view
1011	104	2024-03-01 12:00:05	Home	view
1012	104	2024-03-01 12:00:05	Search	view
1013	104	2024-03-01 12:01:00	Product	view
1014	104	2024-03-01 12:02:00	Cart	view
1015	105	2024-03-01 13:00:10	Home	view
1016	105	2024-03-01 13:00:20		view
1017	105	2024-03-01 13:01:00	Product	view
1018	105	2024-03-01 13:02:00	Cart	view
1019	106	2024-03-01 14:00:10	Home	view
1020	106	2024-03-01 14:00:40	Search	view
1021	106	2024-03-01 14:01:10	Product	view
1022	107	2024-03-01 15:00:05	Home	view
1023	107	2024-03-01 15:00:25	Search	view
1024	107	2024-03-01 15:01:00	Search	view
1025	107	2024-03-01 15:02:00	Product	view
1026	108	2024-03-01 16:00:05	Home	view
1027	108	2024-03-01 16:00:45	Search	click
1028	108	2024-03-01 16:01:10	Product	view
1029	108	2024-03-01 16:02:20	Cart	view
1030	109	2024-03-01 17:00:05	Home	view

sessions

session_id	user_id	session_start	device_type
102	2	2024-03-01 10:00:00	ios
101	1	2024-03-01 09:00:00	web
104	4	2024-03-01 12:00:00	android
103	3	2024-03-01 11:00:00	web
106	6	2024-03-01 14:00:00	web
105	5	2024-03-01 13:00:00
107	7	2024-03-01 15:00:00	ios
108	8	2024-03-01 16:00:00	web
109	99	2024-03-01 17:00:00	android
1	9	2024-02-29 23:43:44	windows
2	25	2024-03-04 06:06:37	android
3	64	2024-02-28 08:20:38	partner_embed
4	68	2024-02-27 15:24:54	tablet
5	95	2024-03-01 03:19:36	firefox_extension
6	19	2024-03-04 01:36:29	partner_embed
7	5	2024-02-29 00:11:59	ios
8	75	2024-03-02 15:50:34	ios
9	90	2024-02-29 22:48:47	firefox_extension
10	58	2024-02-29 13:07:53	mobile_web
11	68	2024-03-02 18:15:56	voice_assistant
12	92	2024-03-03 16:02:04	voice_assistant
13	30	2024-03-04 16:15:44	firefox_extension
14	23	2024-02-29 02:18:25	kiosk
15	39	2024-03-03 18:05:19	iphone
16	4	2024-02-29 08:29:17	smart_tv
17	86	2024-03-03 21:06:22	windows
18	43	2024-02-29 20:03:09	iphone
19	18	2024-03-04 01:45:08	ios
20	34	2024-03-04 08:41:32	tablet
21	79	2024-03-04 01:28:17	smart_tv

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	4	1
Search > Product > Cart	3	2
Home > Product > Cart	1	3
Home > Search > Search	1	4
Search > Search > Product	1	5

Problem

Task

NovaFlow wants to understand how users move through its product. Write a PostgreSQL query to identify the most common 3-step navigation paths users take within a session.

Use the event stream to build ordered page paths from consecutive events. Only include sessions tied to active users, and ignore events where page_name is NULL.

Requirements

Build 3-step paths using each user's ordered events within the same session.
Use event timestamp order, and break ties with event_id.
Only include sessions from users where users.is_active = TRUE.
Exclude incomplete paths where step 2 or step 3 is missing.
Return each unique path, the number of distinct sessions that followed it, and its rank by popularity.
Sort by path rank, then path name.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	User name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is active

`sessions`

column	type	description
session_id	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Session start time
device_type	VARCHAR(50)	Device used for the session

`events`

column	type	description
event_id	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Event timestamp
page_name	VARCHAR(100)	Product page or screen name
event_type	VARCHAR(50)	Event type

Sample Data

`users`

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false

`sessions`

session_id	user_id	session_start	device_type
101	1	2024-03-01 09:00:00	web
102	2	2024-03-01 10:00:00	ios
103	3	2024-03-01 11:00:00	web

`events`

event_id	session_id	event_time	page_name	event_type
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1004	102	2024-03-01 10:00:10	Home	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	3	1
Search > Product > Cart	2	2
Home > Product > Cart	1	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	User's full name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is currently active

sessions

Column	Type	Description
session_idPK	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Timestamp when the session started
device_type	VARCHAR(50)	Device type used in the session

events

Column	Type	Description
event_idPK	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Timestamp of the event
page_name	VARCHAR(100)	Page or screen viewed by the user
event_type	VARCHAR(50)	Type of event recorded

users

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false
4	Daniel Wu	2024-01-18	true
5	Ella Singh	2024-01-20	true
6	Farah Ali	2024-01-22	false
7	Gavin Ross	2024-01-25	true
8	Hana Lee	2024-01-28	true
1	Riya Kapoor	2024-01-13	true
2	Kevin Patel	2024-01-14	false
3	Chloe Kim	2024-01-08	true
4	Isaac Park	2024-01-27	false
5	Chloe Kim	2024-01-19	true
6	Ben Ortiz	2024-01-26	true
7	Gavin Ross	2024-01-17	true
8	Quinn Foster	2024-01-08	false
9	Kevin Patel	2024-01-11	false
10	Ximena Cruz	2024-01-10	true
11	Quinn Foster	2024-01-26	true
12	Riya Kapoor	2024-01-08	true
13	Quinn Foster	2024-01-16	true
14	Daniel Wu	2024-01-09	false
15	Farah Ali	2024-01-24	false
16	Farah Ali	2024-01-30	false
17	Quinn Foster	2024-01-30	false
18	Tara Nguyen	2024-01-11	true
19	Yusuf Malik	2024-01-11	true
20	Owen Price	2024-01-17	false
21	Quinn Foster	2024-01-13	true

events

event_id	session_id	event_time	page_name	event_type
1004	102	2024-03-01 10:00:10	Home	view
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view
1007	102	2024-03-01 10:02:00	Cart	view
1008	103	2024-03-01 11:00:05	Home	view
1009	103	2024-03-01 11:00:40	Search	view
1010	103	2024-03-01 11:01:20	Product	view
1011	104	2024-03-01 12:00:05	Home	view
1012	104	2024-03-01 12:00:05	Search	view
1013	104	2024-03-01 12:01:00	Product	view
1014	104	2024-03-01 12:02:00	Cart	view
1015	105	2024-03-01 13:00:10	Home	view
1016	105	2024-03-01 13:00:20		view
1017	105	2024-03-01 13:01:00	Product	view
1018	105	2024-03-01 13:02:00	Cart	view
1019	106	2024-03-01 14:00:10	Home	view
1020	106	2024-03-01 14:00:40	Search	view
1021	106	2024-03-01 14:01:10	Product	view
1022	107	2024-03-01 15:00:05	Home	view
1023	107	2024-03-01 15:00:25	Search	view
1024	107	2024-03-01 15:01:00	Search	view
1025	107	2024-03-01 15:02:00	Product	view
1026	108	2024-03-01 16:00:05	Home	view
1027	108	2024-03-01 16:00:45	Search	click
1028	108	2024-03-01 16:01:10	Product	view
1029	108	2024-03-01 16:02:20	Cart	view
1030	109	2024-03-01 17:00:05	Home	view

sessions

session_id	user_id	session_start	device_type
102	2	2024-03-01 10:00:00	ios
101	1	2024-03-01 09:00:00	web
104	4	2024-03-01 12:00:00	android
103	3	2024-03-01 11:00:00	web
106	6	2024-03-01 14:00:00	web
105	5	2024-03-01 13:00:00
107	7	2024-03-01 15:00:00	ios
108	8	2024-03-01 16:00:00	web
109	99	2024-03-01 17:00:00	android
1	9	2024-02-29 23:43:44	windows
2	25	2024-03-04 06:06:37	android
3	64	2024-02-28 08:20:38	partner_embed
4	68	2024-02-27 15:24:54	tablet
5	95	2024-03-01 03:19:36	firefox_extension
6	19	2024-03-04 01:36:29	partner_embed
7	5	2024-02-29 00:11:59	ios
8	75	2024-03-02 15:50:34	ios
9	90	2024-02-29 22:48:47	firefox_extension
10	58	2024-02-29 13:07:53	mobile_web
11	68	2024-03-02 18:15:56	voice_assistant
12	92	2024-03-03 16:02:04	voice_assistant
13	30	2024-03-04 16:15:44	firefox_extension
14	23	2024-02-29 02:18:25	kiosk
15	39	2024-03-03 18:05:19	iphone
16	4	2024-02-29 08:29:17	smart_tv
17	86	2024-03-03 21:06:22	windows
18	43	2024-02-29 20:03:09	iphone
19	18	2024-03-04 01:45:08	ios
20	34	2024-03-04 08:41:32	tablet
21	79	2024-03-04 01:28:17	smart_tv

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	4	1
Search > Product > Cart	3	2
Home > Product > Cart	1	3
Home > Search > Search	1	4
Search > Search > Product	1	5

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Find Onboarding Funnel Drop-OffMedium

First and Last User EventsMedium ATop Active Users by Event CountMedium

Next question

Most Common User Navigation Paths

Hard

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsWindow FunctionsCTEs

Also asked at

Problem

Task

NovaFlow wants to understand how users move through its product. Write a PostgreSQL query to identify the most common 3-step navigation paths users take within a session.

Use the event stream to build ordered page paths from consecutive events. Only include sessions tied to active users, and ignore events where page_name is NULL.

Requirements

Build 3-step paths using each user's ordered events within the same session.
Use event timestamp order, and break ties with event_id.
Only include sessions from users where users.is_active = TRUE.
Exclude incomplete paths where step 2 or step 3 is missing.
Return each unique path, the number of distinct sessions that followed it, and its rank by popularity.
Sort by path rank, then path name.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	User name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is active

`sessions`

column	type	description
session_id	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Session start time
device_type	VARCHAR(50)	Device used for the session

`events`

column	type	description
event_id	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Event timestamp
page_name	VARCHAR(100)	Product page or screen name
event_type	VARCHAR(50)	Event type

Sample Data

`users`

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false

`sessions`

session_id	user_id	session_start	device_type
101	1	2024-03-01 09:00:00	web
102	2	2024-03-01 10:00:00	ios
103	3	2024-03-01 11:00:00	web

`events`

event_id	session_id	event_time	page_name	event_type
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1004	102	2024-03-01 10:00:10	Home	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	3	1
Search > Product > Cart	2	2
Home > Product > Cart	1	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	User's full name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is currently active

sessions

Column	Type	Description
session_idPK	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Timestamp when the session started
device_type	VARCHAR(50)	Device type used in the session

events

Column	Type	Description
event_idPK	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Timestamp of the event
page_name	VARCHAR(100)	Page or screen viewed by the user
event_type	VARCHAR(50)	Type of event recorded

users

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false
4	Daniel Wu	2024-01-18	true
5	Ella Singh	2024-01-20	true
6	Farah Ali	2024-01-22	false
7	Gavin Ross	2024-01-25	true
8	Hana Lee	2024-01-28	true
1	Riya Kapoor	2024-01-13	true
2	Kevin Patel	2024-01-14	false
3	Chloe Kim	2024-01-08	true
4	Isaac Park	2024-01-27	false
5	Chloe Kim	2024-01-19	true
6	Ben Ortiz	2024-01-26	true
7	Gavin Ross	2024-01-17	true
8	Quinn Foster	2024-01-08	false
9	Kevin Patel	2024-01-11	false
10	Ximena Cruz	2024-01-10	true
11	Quinn Foster	2024-01-26	true
12	Riya Kapoor	2024-01-08	true
13	Quinn Foster	2024-01-16	true
14	Daniel Wu	2024-01-09	false
15	Farah Ali	2024-01-24	false
16	Farah Ali	2024-01-30	false
17	Quinn Foster	2024-01-30	false
18	Tara Nguyen	2024-01-11	true
19	Yusuf Malik	2024-01-11	true
20	Owen Price	2024-01-17	false
21	Quinn Foster	2024-01-13	true

events

event_id	session_id	event_time	page_name	event_type
1004	102	2024-03-01 10:00:10	Home	view
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view
1007	102	2024-03-01 10:02:00	Cart	view
1008	103	2024-03-01 11:00:05	Home	view
1009	103	2024-03-01 11:00:40	Search	view
1010	103	2024-03-01 11:01:20	Product	view
1011	104	2024-03-01 12:00:05	Home	view
1012	104	2024-03-01 12:00:05	Search	view
1013	104	2024-03-01 12:01:00	Product	view
1014	104	2024-03-01 12:02:00	Cart	view
1015	105	2024-03-01 13:00:10	Home	view
1016	105	2024-03-01 13:00:20		view
1017	105	2024-03-01 13:01:00	Product	view
1018	105	2024-03-01 13:02:00	Cart	view
1019	106	2024-03-01 14:00:10	Home	view
1020	106	2024-03-01 14:00:40	Search	view
1021	106	2024-03-01 14:01:10	Product	view
1022	107	2024-03-01 15:00:05	Home	view
1023	107	2024-03-01 15:00:25	Search	view
1024	107	2024-03-01 15:01:00	Search	view
1025	107	2024-03-01 15:02:00	Product	view
1026	108	2024-03-01 16:00:05	Home	view
1027	108	2024-03-01 16:00:45	Search	click
1028	108	2024-03-01 16:01:10	Product	view
1029	108	2024-03-01 16:02:20	Cart	view
1030	109	2024-03-01 17:00:05	Home	view

sessions

session_id	user_id	session_start	device_type
102	2	2024-03-01 10:00:00	ios
101	1	2024-03-01 09:00:00	web
104	4	2024-03-01 12:00:00	android
103	3	2024-03-01 11:00:00	web
106	6	2024-03-01 14:00:00	web
105	5	2024-03-01 13:00:00
107	7	2024-03-01 15:00:00	ios
108	8	2024-03-01 16:00:00	web
109	99	2024-03-01 17:00:00	android
1	9	2024-02-29 23:43:44	windows
2	25	2024-03-04 06:06:37	android
3	64	2024-02-28 08:20:38	partner_embed
4	68	2024-02-27 15:24:54	tablet
5	95	2024-03-01 03:19:36	firefox_extension
6	19	2024-03-04 01:36:29	partner_embed
7	5	2024-02-29 00:11:59	ios
8	75	2024-03-02 15:50:34	ios
9	90	2024-02-29 22:48:47	firefox_extension
10	58	2024-02-29 13:07:53	mobile_web
11	68	2024-03-02 18:15:56	voice_assistant
12	92	2024-03-03 16:02:04	voice_assistant
13	30	2024-03-04 16:15:44	firefox_extension
14	23	2024-02-29 02:18:25	kiosk
15	39	2024-03-03 18:05:19	iphone
16	4	2024-02-29 08:29:17	smart_tv
17	86	2024-03-03 21:06:22	windows
18	43	2024-02-29 20:03:09	iphone
19	18	2024-03-04 01:45:08	ios
20	34	2024-03-04 08:41:32	tablet
21	79	2024-03-04 01:28:17	smart_tv

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	4	1
Search > Product > Cart	3	2
Home > Product > Cart	1	3
Home > Search > Search	1	4
Search > Search > Product	1	5

Problem

Task

NovaFlow wants to understand how users move through its product. Write a PostgreSQL query to identify the most common 3-step navigation paths users take within a session.

Use the event stream to build ordered page paths from consecutive events. Only include sessions tied to active users, and ignore events where page_name is NULL.

Requirements

Build 3-step paths using each user's ordered events within the same session.
Use event timestamp order, and break ties with event_id.
Only include sessions from users where users.is_active = TRUE.
Exclude incomplete paths where step 2 or step 3 is missing.
Return each unique path, the number of distinct sessions that followed it, and its rank by popularity.
Sort by path rank, then path name.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	User name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is active

`sessions`

column	type	description
session_id	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Session start time
device_type	VARCHAR(50)	Device used for the session

`events`

column	type	description
event_id	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Event timestamp
page_name	VARCHAR(100)	Product page or screen name
event_type	VARCHAR(50)	Event type

Sample Data

`users`

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false

`sessions`

session_id	user_id	session_start	device_type
101	1	2024-03-01 09:00:00	web
102	2	2024-03-01 10:00:00	ios
103	3	2024-03-01 11:00:00	web

`events`

event_id	session_id	event_time	page_name	event_type
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1004	102	2024-03-01 10:00:10	Home	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	3	1
Search > Product > Cart	2	2
Home > Product > Cart	1	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	User's full name
signup_date	DATE	Date the user signed up
is_active	BOOLEAN	Whether the user is currently active

sessions

Column	Type	Description
session_idPK	INT	Primary key for the session
user_id	INT	User who owns the session
session_start	TIMESTAMP	Timestamp when the session started
device_type	VARCHAR(50)	Device type used in the session

events

Column	Type	Description
event_idPK	INT	Primary key for the event
session_id	INT	Session where the event occurred
event_time	TIMESTAMP	Timestamp of the event
page_name	VARCHAR(100)	Page or screen viewed by the user
event_type	VARCHAR(50)	Type of event recorded

users

user_id	user_name	signup_date	is_active
1	Ava Chen	2024-01-10	true
2	Ben Ortiz	2024-01-12	true
3	Chloe Kim	2024-01-15	false
4	Daniel Wu	2024-01-18	true
5	Ella Singh	2024-01-20	true
6	Farah Ali	2024-01-22	false
7	Gavin Ross	2024-01-25	true
8	Hana Lee	2024-01-28	true
1	Riya Kapoor	2024-01-13	true
2	Kevin Patel	2024-01-14	false
3	Chloe Kim	2024-01-08	true
4	Isaac Park	2024-01-27	false
5	Chloe Kim	2024-01-19	true
6	Ben Ortiz	2024-01-26	true
7	Gavin Ross	2024-01-17	true
8	Quinn Foster	2024-01-08	false
9	Kevin Patel	2024-01-11	false
10	Ximena Cruz	2024-01-10	true
11	Quinn Foster	2024-01-26	true
12	Riya Kapoor	2024-01-08	true
13	Quinn Foster	2024-01-16	true
14	Daniel Wu	2024-01-09	false
15	Farah Ali	2024-01-24	false
16	Farah Ali	2024-01-30	false
17	Quinn Foster	2024-01-30	false
18	Tara Nguyen	2024-01-11	true
19	Yusuf Malik	2024-01-11	true
20	Owen Price	2024-01-17	false
21	Quinn Foster	2024-01-13	true

events

event_id	session_id	event_time	page_name	event_type
1004	102	2024-03-01 10:00:10	Home	view
1001	101	2024-03-01 09:00:05	Home	view
1002	101	2024-03-01 09:00:20	Search	view
1003	101	2024-03-01 09:01:00	Product	view
1005	102	2024-03-01 10:00:30	Search	view
1006	102	2024-03-01 10:01:10	Product	view
1007	102	2024-03-01 10:02:00	Cart	view
1008	103	2024-03-01 11:00:05	Home	view
1009	103	2024-03-01 11:00:40	Search	view
1010	103	2024-03-01 11:01:20	Product	view
1011	104	2024-03-01 12:00:05	Home	view
1012	104	2024-03-01 12:00:05	Search	view
1013	104	2024-03-01 12:01:00	Product	view
1014	104	2024-03-01 12:02:00	Cart	view
1015	105	2024-03-01 13:00:10	Home	view
1016	105	2024-03-01 13:00:20		view
1017	105	2024-03-01 13:01:00	Product	view
1018	105	2024-03-01 13:02:00	Cart	view
1019	106	2024-03-01 14:00:10	Home	view
1020	106	2024-03-01 14:00:40	Search	view
1021	106	2024-03-01 14:01:10	Product	view
1022	107	2024-03-01 15:00:05	Home	view
1023	107	2024-03-01 15:00:25	Search	view
1024	107	2024-03-01 15:01:00	Search	view
1025	107	2024-03-01 15:02:00	Product	view
1026	108	2024-03-01 16:00:05	Home	view
1027	108	2024-03-01 16:00:45	Search	click
1028	108	2024-03-01 16:01:10	Product	view
1029	108	2024-03-01 16:02:20	Cart	view
1030	109	2024-03-01 17:00:05	Home	view

sessions

session_id	user_id	session_start	device_type
102	2	2024-03-01 10:00:00	ios
101	1	2024-03-01 09:00:00	web
104	4	2024-03-01 12:00:00	android
103	3	2024-03-01 11:00:00	web
106	6	2024-03-01 14:00:00	web
105	5	2024-03-01 13:00:00
107	7	2024-03-01 15:00:00	ios
108	8	2024-03-01 16:00:00	web
109	99	2024-03-01 17:00:00	android
1	9	2024-02-29 23:43:44	windows
2	25	2024-03-04 06:06:37	android
3	64	2024-02-28 08:20:38	partner_embed
4	68	2024-02-27 15:24:54	tablet
5	95	2024-03-01 03:19:36	firefox_extension
6	19	2024-03-04 01:36:29	partner_embed
7	5	2024-02-29 00:11:59	ios
8	75	2024-03-02 15:50:34	ios
9	90	2024-02-29 22:48:47	firefox_extension
10	58	2024-02-29 13:07:53	mobile_web
11	68	2024-03-02 18:15:56	voice_assistant
12	92	2024-03-03 16:02:04	voice_assistant
13	30	2024-03-04 16:15:44	firefox_extension
14	23	2024-02-29 02:18:25	kiosk
15	39	2024-03-03 18:05:19	iphone
16	4	2024-02-29 08:29:17	smart_tv
17	86	2024-03-03 21:06:22	windows
18	43	2024-02-29 20:03:09	iphone
19	18	2024-03-04 01:45:08	ios
20	34	2024-03-04 08:41:32	tablet
21	79	2024-03-04 01:28:17	smart_tv

Expected Output

path_3_steps	session_count	path_rank
Home > Search > Product	4	1
Search > Product > Cart	3	2
Home > Product > Cart	1	3
Home > Search > Search	1	4
Search > Search > Product	1	5

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Find Onboarding Funnel Drop-OffMedium

First and Last User EventsMedium ATop Active Users by Event CountMedium

Next question