Interview Guides

Rank Top Funnel Segments | Dataford Interview Questions - Dataford - Ace your Interview

Rank Top Funnel Segments

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsGroup ByRanking

Also asked at

Problem

Task

Rippling’s growth team wants to identify which user segments convert best through the signup funnel in Rippling HR Cloud. Write a SQL query to find the top converting segments based on users who reached the final funnel step.

Use the tables below to calculate conversion from signup_started to payroll_enabled for each segment.

Requirements

Consider only users whose signup_started_at is in January 2024.
Define a converted user as one who has a payroll_enabled event within 14 days of signup.
Group results by segment_name from the segments table.
Return these columns: segment_name, started_users, converted_users, conversion_rate, and segment_rank.
Only include segments with at least 2 started users.
Rank segments by conversion_rate descending, then converted_users descending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	When the user started signup
company_country	VARCHAR(50)	Country of the company

`segments`

column	type	description
segment_id	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Funnel event name
event_time	TIMESTAMP	When the event occurred

Sample Data

`users`

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK

`segments`

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic

`funnel_events`

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
Startup Founders	3	2	0.6667	1
SMB Tech	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	Timestamp when the user started signup
company_country	VARCHAR(50)	Country of the company

segments

Column	Type	Description
segment_idPK	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

funnel_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Name of the funnel event
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK
105	3	2024-01-10 16:45:00	US
106	3	2024-01-14 11:20:00
107	1	2024-01-28 09:10:00	AU
108	4	2024-01-18 13:00:00	US
109	3	2024-01-31 23:30:00	DE
110		2024-01-12 07:50:00	US
111	2	2024-02-02 09:00:00	US
112	5	2024-01-22 15:00:00
1	9	2024-02-05 02:37:34	UK
2	65	2024-02-04 02:45:30	ES
3	57	2024-01-18 14:45:43	null
4	78	2024-01-02 15:52:51	ZA
5	19	2024-01-04 21:14:15	PT
6	78	2024-01-05 03:16:34	AE
7	88	2024-01-08 09:49:20	PT
8	23	2024-01-22 01:33:03	ZA
9	55	2024-01-03 09:17:43	IE
10	8	2024-01-25 20:06:41	KR
11	11	2024-01-12 17:22:32
12	25	2024-01-30 00:05:25	AE
13	92	2024-01-04 17:52:36	DE
14	31	2024-01-24 17:43:12	MX
15	93	2024-01-19 04:16:29	PT
16	20	2024-01-28 07:31:22	US
17	67	2024-01-19 12:16:15	JP

segments

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic
4	Enterprise IT	Partner
5	International SMB	Paid Social
6	Finance Leaders
7	Legacy Prospects	Events
8	Dormant Accounts	Email
9	Expansion Pipeline	Sales
10	Contractor-Heavy Teams	Organic
1	Biotech Startups	Community
2	Hospitality Groups	Partner
3	Mid-Market Ops	Partner
4	Global Payroll Prospects	SEO
5	Expansion Pipeline	Marketplace
6	Dormant Accounts	Webinar
7	Agency Owners	Sales
8	Professional Services	Email
9	Healthcare Admin	Podcast
10	Manufacturing HR	Outbound
11	Biotech Startups	Chat
12	Healthcare Admin	null
13	Agency Owners	Lifecycle
14	Global Payroll Prospects	null
15	International SMB	Conference
16	Legacy Prospects	Conference
17	Expansion Pipeline	Direct
18	Real Estate Brokers	Organic

funnel_events

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00
5	103	signup_started	2024-01-05 12:00:00
6	103	payroll_enabled	2024-01-18 12:00:00
7	104	signup_started	2024-01-20 08:15:00
8	104	payroll_enabled	2024-02-10 09:00:00
9	105	signup_started	2024-01-10 16:45:00
10	105	payroll_enabled	2024-01-20 10:00:00
11	106	signup_started	2024-01-14 11:20:00
12	106	payroll_enabled	2024-01-16 08:00:00
13	106	payroll_enabled	2024-01-17 08:00:00
14	107	signup_started	2024-01-28 09:10:00
15	107	payroll_enabled	2024-02-05 10:00:00
16	108	signup_started	2024-01-18 13:00:00
17	108	document_uploaded	2024-01-19 09:00:00
18	109	signup_started	2024-01-31 23:30:00
19	109	payroll_enabled	2024-02-20 12:00:00
20	111	signup_started	2024-02-02 09:00:00
21	111	payroll_enabled	2024-02-05 09:00:00
22	112	signup_started	2024-01-22 15:00:00
23	112	payroll_enabled
1	82	integration_connected	2024-02-02 23:47:40
2	74	app_installed	null
3	68	phone_verified	2024-02-10 07:04:46
4	94	phone_verified	2024-02-19 18:25:33
5	74	app_installed	2024-01-21 01:04:59
6	72	sso_enabled	2024-02-18 14:02:35
7	98	admin_invited	2024-01-02 14:11:13

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
SMB Tech	3	2	0.6667	1
Startup Founders	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Problem

Task

Use the tables below to calculate conversion from signup_started to payroll_enabled for each segment.

Requirements

Consider only users whose signup_started_at is in January 2024.
Define a converted user as one who has a payroll_enabled event within 14 days of signup.
Group results by segment_name from the segments table.
Return these columns: segment_name, started_users, converted_users, conversion_rate, and segment_rank.
Only include segments with at least 2 started users.
Rank segments by conversion_rate descending, then converted_users descending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	When the user started signup
company_country	VARCHAR(50)	Country of the company

`segments`

column	type	description
segment_id	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Funnel event name
event_time	TIMESTAMP	When the event occurred

Sample Data

`users`

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK

`segments`

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic

`funnel_events`

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
Startup Founders	3	2	0.6667	1
SMB Tech	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	Timestamp when the user started signup
company_country	VARCHAR(50)	Country of the company

segments

Column	Type	Description
segment_idPK	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

funnel_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Name of the funnel event
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK
105	3	2024-01-10 16:45:00	US
106	3	2024-01-14 11:20:00
107	1	2024-01-28 09:10:00	AU
108	4	2024-01-18 13:00:00	US
109	3	2024-01-31 23:30:00	DE
110		2024-01-12 07:50:00	US
111	2	2024-02-02 09:00:00	US
112	5	2024-01-22 15:00:00
1	9	2024-02-05 02:37:34	UK
2	65	2024-02-04 02:45:30	ES
3	57	2024-01-18 14:45:43	null
4	78	2024-01-02 15:52:51	ZA
5	19	2024-01-04 21:14:15	PT
6	78	2024-01-05 03:16:34	AE
7	88	2024-01-08 09:49:20	PT
8	23	2024-01-22 01:33:03	ZA
9	55	2024-01-03 09:17:43	IE
10	8	2024-01-25 20:06:41	KR
11	11	2024-01-12 17:22:32
12	25	2024-01-30 00:05:25	AE
13	92	2024-01-04 17:52:36	DE
14	31	2024-01-24 17:43:12	MX
15	93	2024-01-19 04:16:29	PT
16	20	2024-01-28 07:31:22	US
17	67	2024-01-19 12:16:15	JP

segments

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic
4	Enterprise IT	Partner
5	International SMB	Paid Social
6	Finance Leaders
7	Legacy Prospects	Events
8	Dormant Accounts	Email
9	Expansion Pipeline	Sales
10	Contractor-Heavy Teams	Organic
1	Biotech Startups	Community
2	Hospitality Groups	Partner
3	Mid-Market Ops	Partner
4	Global Payroll Prospects	SEO
5	Expansion Pipeline	Marketplace
6	Dormant Accounts	Webinar
7	Agency Owners	Sales
8	Professional Services	Email
9	Healthcare Admin	Podcast
10	Manufacturing HR	Outbound
11	Biotech Startups	Chat
12	Healthcare Admin	null
13	Agency Owners	Lifecycle
14	Global Payroll Prospects	null
15	International SMB	Conference
16	Legacy Prospects	Conference
17	Expansion Pipeline	Direct
18	Real Estate Brokers	Organic

funnel_events

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00
5	103	signup_started	2024-01-05 12:00:00
6	103	payroll_enabled	2024-01-18 12:00:00
7	104	signup_started	2024-01-20 08:15:00
8	104	payroll_enabled	2024-02-10 09:00:00
9	105	signup_started	2024-01-10 16:45:00
10	105	payroll_enabled	2024-01-20 10:00:00
11	106	signup_started	2024-01-14 11:20:00
12	106	payroll_enabled	2024-01-16 08:00:00
13	106	payroll_enabled	2024-01-17 08:00:00
14	107	signup_started	2024-01-28 09:10:00
15	107	payroll_enabled	2024-02-05 10:00:00
16	108	signup_started	2024-01-18 13:00:00
17	108	document_uploaded	2024-01-19 09:00:00
18	109	signup_started	2024-01-31 23:30:00
19	109	payroll_enabled	2024-02-20 12:00:00
20	111	signup_started	2024-02-02 09:00:00
21	111	payroll_enabled	2024-02-05 09:00:00
22	112	signup_started	2024-01-22 15:00:00
23	112	payroll_enabled
1	82	integration_connected	2024-02-02 23:47:40
2	74	app_installed	null
3	68	phone_verified	2024-02-10 07:04:46
4	94	phone_verified	2024-02-19 18:25:33
5	74	app_installed	2024-01-21 01:04:59
6	72	sso_enabled	2024-02-18 14:02:35
7	98	admin_invited	2024-01-02 14:11:13

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
SMB Tech	3	2	0.6667	1
Startup Founders	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Practice SQL

PostgreSQL

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

Up next

Rank Customer Segments by RevenueMedium

Analyze Signup Funnel Drop-OffMedium

Find Largest Funnel Step Drop-OffMedium

Next question

Rank Top Funnel Segments

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsGroup ByRanking

Also asked at

Problem

Task

Use the tables below to calculate conversion from signup_started to payroll_enabled for each segment.

Requirements

Consider only users whose signup_started_at is in January 2024.
Define a converted user as one who has a payroll_enabled event within 14 days of signup.
Group results by segment_name from the segments table.
Return these columns: segment_name, started_users, converted_users, conversion_rate, and segment_rank.
Only include segments with at least 2 started users.
Rank segments by conversion_rate descending, then converted_users descending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	When the user started signup
company_country	VARCHAR(50)	Country of the company

`segments`

column	type	description
segment_id	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Funnel event name
event_time	TIMESTAMP	When the event occurred

Sample Data

`users`

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK

`segments`

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic

`funnel_events`

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
Startup Founders	3	2	0.6667	1
SMB Tech	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	Timestamp when the user started signup
company_country	VARCHAR(50)	Country of the company

segments

Column	Type	Description
segment_idPK	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

funnel_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Name of the funnel event
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK
105	3	2024-01-10 16:45:00	US
106	3	2024-01-14 11:20:00
107	1	2024-01-28 09:10:00	AU
108	4	2024-01-18 13:00:00	US
109	3	2024-01-31 23:30:00	DE
110		2024-01-12 07:50:00	US
111	2	2024-02-02 09:00:00	US
112	5	2024-01-22 15:00:00
1	9	2024-02-05 02:37:34	UK
2	65	2024-02-04 02:45:30	ES
3	57	2024-01-18 14:45:43	null
4	78	2024-01-02 15:52:51	ZA
5	19	2024-01-04 21:14:15	PT
6	78	2024-01-05 03:16:34	AE
7	88	2024-01-08 09:49:20	PT
8	23	2024-01-22 01:33:03	ZA
9	55	2024-01-03 09:17:43	IE
10	8	2024-01-25 20:06:41	KR
11	11	2024-01-12 17:22:32
12	25	2024-01-30 00:05:25	AE
13	92	2024-01-04 17:52:36	DE
14	31	2024-01-24 17:43:12	MX
15	93	2024-01-19 04:16:29	PT
16	20	2024-01-28 07:31:22	US
17	67	2024-01-19 12:16:15	JP

segments

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic
4	Enterprise IT	Partner
5	International SMB	Paid Social
6	Finance Leaders
7	Legacy Prospects	Events
8	Dormant Accounts	Email
9	Expansion Pipeline	Sales
10	Contractor-Heavy Teams	Organic
1	Biotech Startups	Community
2	Hospitality Groups	Partner
3	Mid-Market Ops	Partner
4	Global Payroll Prospects	SEO
5	Expansion Pipeline	Marketplace
6	Dormant Accounts	Webinar
7	Agency Owners	Sales
8	Professional Services	Email
9	Healthcare Admin	Podcast
10	Manufacturing HR	Outbound
11	Biotech Startups	Chat
12	Healthcare Admin	null
13	Agency Owners	Lifecycle
14	Global Payroll Prospects	null
15	International SMB	Conference
16	Legacy Prospects	Conference
17	Expansion Pipeline	Direct
18	Real Estate Brokers	Organic

funnel_events

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00
5	103	signup_started	2024-01-05 12:00:00
6	103	payroll_enabled	2024-01-18 12:00:00
7	104	signup_started	2024-01-20 08:15:00
8	104	payroll_enabled	2024-02-10 09:00:00
9	105	signup_started	2024-01-10 16:45:00
10	105	payroll_enabled	2024-01-20 10:00:00
11	106	signup_started	2024-01-14 11:20:00
12	106	payroll_enabled	2024-01-16 08:00:00
13	106	payroll_enabled	2024-01-17 08:00:00
14	107	signup_started	2024-01-28 09:10:00
15	107	payroll_enabled	2024-02-05 10:00:00
16	108	signup_started	2024-01-18 13:00:00
17	108	document_uploaded	2024-01-19 09:00:00
18	109	signup_started	2024-01-31 23:30:00
19	109	payroll_enabled	2024-02-20 12:00:00
20	111	signup_started	2024-02-02 09:00:00
21	111	payroll_enabled	2024-02-05 09:00:00
22	112	signup_started	2024-01-22 15:00:00
23	112	payroll_enabled
1	82	integration_connected	2024-02-02 23:47:40
2	74	app_installed	null
3	68	phone_verified	2024-02-10 07:04:46
4	94	phone_verified	2024-02-19 18:25:33
5	74	app_installed	2024-01-21 01:04:59
6	72	sso_enabled	2024-02-18 14:02:35
7	98	admin_invited	2024-01-02 14:11:13

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
SMB Tech	3	2	0.6667	1
Startup Founders	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Problem

Task

Use the tables below to calculate conversion from signup_started to payroll_enabled for each segment.

Requirements

Consider only users whose signup_started_at is in January 2024.
Define a converted user as one who has a payroll_enabled event within 14 days of signup.
Group results by segment_name from the segments table.
Return these columns: segment_name, started_users, converted_users, conversion_rate, and segment_rank.
Only include segments with at least 2 started users.
Rank segments by conversion_rate descending, then converted_users descending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	When the user started signup
company_country	VARCHAR(50)	Country of the company

`segments`

column	type	description
segment_id	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Funnel event name
event_time	TIMESTAMP	When the event occurred

Sample Data

`users`

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK

`segments`

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic

`funnel_events`

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
Startup Founders	3	2	0.6667	1
SMB Tech	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
segment_id	INT	Segment assigned to the user
signup_started_at	TIMESTAMP	Timestamp when the user started signup
company_country	VARCHAR(50)	Country of the company

segments

Column	Type	Description
segment_idPK	INT	Unique segment identifier
segment_name	VARCHAR(100)	Segment label
acquisition_channel	VARCHAR(50)	Primary acquisition channel

funnel_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User tied to the event
event_name	VARCHAR(50)	Name of the funnel event
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	segment_id	signup_started_at	company_country
101	1	2024-01-03 09:00:00	US
102	1	2024-01-08 10:30:00	CA
103	2	2024-01-05 12:00:00	US
104	2	2024-01-20 08:15:00	UK
105	3	2024-01-10 16:45:00	US
106	3	2024-01-14 11:20:00
107	1	2024-01-28 09:10:00	AU
108	4	2024-01-18 13:00:00	US
109	3	2024-01-31 23:30:00	DE
110		2024-01-12 07:50:00	US
111	2	2024-02-02 09:00:00	US
112	5	2024-01-22 15:00:00
1	9	2024-02-05 02:37:34	UK
2	65	2024-02-04 02:45:30	ES
3	57	2024-01-18 14:45:43	null
4	78	2024-01-02 15:52:51	ZA
5	19	2024-01-04 21:14:15	PT
6	78	2024-01-05 03:16:34	AE
7	88	2024-01-08 09:49:20	PT
8	23	2024-01-22 01:33:03	ZA
9	55	2024-01-03 09:17:43	IE
10	8	2024-01-25 20:06:41	KR
11	11	2024-01-12 17:22:32
12	25	2024-01-30 00:05:25	AE
13	92	2024-01-04 17:52:36	DE
14	31	2024-01-24 17:43:12	MX
15	93	2024-01-19 04:16:29	PT
16	20	2024-01-28 07:31:22	US
17	67	2024-01-19 12:16:15	JP

segments

segment_id	segment_name	acquisition_channel
1	SMB Tech	Paid Search
2	Mid-Market Ops	Sales
3	Startup Founders	Organic
4	Enterprise IT	Partner
5	International SMB	Paid Social
6	Finance Leaders
7	Legacy Prospects	Events
8	Dormant Accounts	Email
9	Expansion Pipeline	Sales
10	Contractor-Heavy Teams	Organic
1	Biotech Startups	Community
2	Hospitality Groups	Partner
3	Mid-Market Ops	Partner
4	Global Payroll Prospects	SEO
5	Expansion Pipeline	Marketplace
6	Dormant Accounts	Webinar
7	Agency Owners	Sales
8	Professional Services	Email
9	Healthcare Admin	Podcast
10	Manufacturing HR	Outbound
11	Biotech Startups	Chat
12	Healthcare Admin	null
13	Agency Owners	Lifecycle
14	Global Payroll Prospects	null
15	International SMB	Conference
16	Legacy Prospects	Conference
17	Expansion Pipeline	Direct
18	Real Estate Brokers	Organic

funnel_events

event_id	user_id	event_name	event_time
1	101	signup_started	2024-01-03 09:00:00
2	101	payroll_enabled	2024-01-05 14:00:00
3	102	signup_started	2024-01-08 10:30:00
4	102	payroll_enabled	2024-01-30 09:00:00
5	103	signup_started	2024-01-05 12:00:00
6	103	payroll_enabled	2024-01-18 12:00:00
7	104	signup_started	2024-01-20 08:15:00
8	104	payroll_enabled	2024-02-10 09:00:00
9	105	signup_started	2024-01-10 16:45:00
10	105	payroll_enabled	2024-01-20 10:00:00
11	106	signup_started	2024-01-14 11:20:00
12	106	payroll_enabled	2024-01-16 08:00:00
13	106	payroll_enabled	2024-01-17 08:00:00
14	107	signup_started	2024-01-28 09:10:00
15	107	payroll_enabled	2024-02-05 10:00:00
16	108	signup_started	2024-01-18 13:00:00
17	108	document_uploaded	2024-01-19 09:00:00
18	109	signup_started	2024-01-31 23:30:00
19	109	payroll_enabled	2024-02-20 12:00:00
20	111	signup_started	2024-02-02 09:00:00
21	111	payroll_enabled	2024-02-05 09:00:00
22	112	signup_started	2024-01-22 15:00:00
23	112	payroll_enabled
1	82	integration_connected	2024-02-02 23:47:40
2	74	app_installed	null
3	68	phone_verified	2024-02-10 07:04:46
4	94	phone_verified	2024-02-19 18:25:33
5	74	app_installed	2024-01-21 01:04:59
6	72	sso_enabled	2024-02-18 14:02:35
7	98	admin_invited	2024-01-02 14:11:13

Expected Output

segment_name	started_users	converted_users	conversion_rate	segment_rank
SMB Tech	3	2	0.6667	1
Startup Founders	3	2	0.6667	2
Mid-Market Ops	2	1	0.5000	3

Practice SQL

PostgreSQL

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

Up next

Rank Customer Segments by RevenueMedium

Analyze Signup Funnel Drop-OffMedium

Find Largest Funnel Step Drop-OffMedium

Next question