Interview Guides

Rank Customer Segments by Revenue | Dataford Interview Questions - Dataford - Ace your Interview

Rank Customer Segments by Revenue

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsGroup ByRanking

Also asked at

Problem

Task

You are given signup, account, and payment data from a B2B SaaS product. Write a SQL query to identify the highest-performing customer segments based on both revenue and signup-to-paid conversion. Treat a customer as converted if they have at least one successful payment within 30 days of signup. Return each segment's total customers, converted customers, conversion rate, total revenue from successful payments, and a rank ordered by highest revenue.

Use the segmentation dimensions already stored on the customer record, and only include segments with at least 2 customers.

Schema

`rippling_customers`

column	type	description
customer_id	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment by employee count
region	VARCHAR(20)	Customer region
signup_date	DATE	Date the customer signed up

`rippling_accounts`

column	type	description
account_id	INT	Unique billing account identifier
customer_id	INT	Customer tied to the billing account
plan_name	VARCHAR(30)	Purchased plan
account_status	VARCHAR(20)	Current account status

`rippling_payments`

column	type	description
payment_id	INT	Unique payment identifier
account_id	INT	Billing account identifier
payment_date	DATE	Date of payment
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment result

Sample data

`rippling_customers`

customer_id	company_name	company_size_segment	region	signup_date
1	Northstar Health	SMB	West	2024-01-05
2	Cedar Labs	Mid-Market	West	2024-01-10
3	Blue Mesa	SMB	East	2024-01-15

`rippling_accounts`

account_id	customer_id	plan_name	account_status
101	1	Core Platform	active
102	2	Core Platform	active
103	3	Payroll	trial

`rippling_payments`

payment_id	account_id	payment_date	amount_usd	payment_status
1001	101	2024-01-20	500.00	succeeded
1002	102	2024-02-25	1200.00	succeeded
1003	103	2024-01-25	0.00	failed

Expected output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	2	2	1.0000	3000.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	1	0.5000	400.00	3

Schema

rippling_customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment based on employee count
region	VARCHAR(20)	Customer geographic region
signup_date	DATE	Date the customer signed up

rippling_accounts

Column	Type	Description
account_idPK	INT	Unique billing account identifier
customer_id	INT	Customer associated with the account
plan_name	VARCHAR(30)	Subscribed Rippling plan
account_status	VARCHAR(20)	Current billing account status

rippling_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
account_id	INT	Billing account tied to the payment
payment_date	DATE	Date the payment was processed
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment outcome

rippling_accounts

account_id	customer_id	plan_name	account_status
108	8	Core Platform	active
101	1	Core Platform	active
105	5	Global Payroll	active
103	3	Payroll	trial
109	9	Core Platform	active
102	2	Core Platform	active
104	4	Payroll	active
106	6	Core Platform	canceled
110	10		trial
111	999	Core Platform	active
1	94	Policy Management	renewing
2	5	Onboarding	inactive
3	58	App Management	pending
4	60	Global Payroll	live
5	77	Analytics	on_hold
6	39	Global Payroll	reactivated
7	93	Employer of Record	live
8	26	Employer of Record	pending
9	74	Compliance	pending
10	87	Recruiting	past_due
11	43	Employer of Record	pending
12	90	null	expired
13	74	Performance	delinquent
14	57	Recruiting	on_hold
15	65	Payroll	setup
16	34	Spend	past_due
17	81	Workflow Studio	churned
18	70	Workflow Studio	paused
19	12	HR Help Desk	reactivated

rippling_payments

payment_id	account_id	payment_date	amount_usd	payment_status
1008	108	2024-02-20	1500.00	succeeded
1002	101	2024-03-01	750.00	succeeded
1005	103	2024-02-20	400.00	succeeded
1001	101	2024-01-20	500.00	succeeded
1010	105	2024-02-18	3000.00	succeeded
1003	102	2024-02-25	1200.00	succeeded
1004	102	2024-01-25	800.00	succeeded
1006	104	2024-02-25	0.00	failed
1007	106	2024-03-10		failed
1009	109	2024-03-05	700.00	succeeded
1011	110	2024-03-01	200.00	refunded
1	68	2024-03-12	1651	succeeded
2	77	2024-02-02	1118	settled
3	33	2024-02-10	1896	canceled
4	82	2024-03-10	1981	scheduled
5	86	2024-02-15	74	canceled
6	78	2024-02-09	1241	settled
7	47	2024-03-06	851	partially_refunded
8	8	2024-03-05	-50	canceled
9	39	2024-02-15	142	refunded
10	26	2024-02-23	null	processing
11	77	2024-02-04	3015	completed
12	49	2024-01-26	2504	initiated
13	90	2024-03-08	320	reversed
14	74	2024-02-12	null	on_hold
15	1	2024-02-19	-108	posted
16	55	2024-02-10	null	settled
17	54	2024-01-22	2163	disputed
18	72	2024-03-03	3513	rejected

rippling_customers

customer_id	company_name	company_size_segment	region	signup_date
3	Blue Mesa	SMB	East	2024-01-15
1	Northstar Health	SMB	West	2024-01-05
8	Pioneer Works	Mid-Market	West	2024-02-01
5	Atlas Freight	Enterprise	East	2024-01-20
2	Cedar Labs	Mid-Market	West	2024-01-10
7	Lattice Grove	SMB	West	2024-02-05
4	Harbor One	SMB	East	2024-02-10
6	Quartz Retail	Enterprise		2024-02-15
9	Summit Bio	Mid-Market	West	2024-02-18
10	Elm Security		South	2024-02-20
1	Pioneer Works	Upper Mid-Market	UK	2024-01-13
2	Silverline Ops	Startup	Great Lakes	2024-01-27
3	Crescent Media	Healthcare	Mountain	2024-01-22
4	Mosaic Commerce	Scale-Up	Europe	2024-02-21
5	Oakline Dental	Private Equity	North	2024-02-05
6	Vertex Motion	Hospitality	North	2024-01-21
7	Lattice Grove	Nonprofit	South	2024-02-07
8	Elm Security	SMB	Europe	2024-01-26
9	Pioneer Works	Growth	Southeast	2024-01-22
10	Lattice Grove	National	Mountain	2024-02-22
11	Elm Security	Nonprofit	South	2024-02-22
12	Blue Mesa	Mid-Market	East	2024-01-19
13	Northstar Health	Scale-Up	null	2024-02-16
14	Maple Peak	Technology	Canada	2024-02-14
15	Harbor One	International	Mid-Atlantic	2024-02-19

Expected Output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	3	3	1.0000	4200.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	0	0.0000	400.00	3

Problem

Task

Use the segmentation dimensions already stored on the customer record, and only include segments with at least 2 customers.

Schema

`rippling_customers`

column	type	description
customer_id	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment by employee count
region	VARCHAR(20)	Customer region
signup_date	DATE	Date the customer signed up

`rippling_accounts`

column	type	description
account_id	INT	Unique billing account identifier
customer_id	INT	Customer tied to the billing account
plan_name	VARCHAR(30)	Purchased plan
account_status	VARCHAR(20)	Current account status

`rippling_payments`

column	type	description
payment_id	INT	Unique payment identifier
account_id	INT	Billing account identifier
payment_date	DATE	Date of payment
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment result

Sample data

`rippling_customers`

customer_id	company_name	company_size_segment	region	signup_date
1	Northstar Health	SMB	West	2024-01-05
2	Cedar Labs	Mid-Market	West	2024-01-10
3	Blue Mesa	SMB	East	2024-01-15

`rippling_accounts`

account_id	customer_id	plan_name	account_status
101	1	Core Platform	active
102	2	Core Platform	active
103	3	Payroll	trial

`rippling_payments`

payment_id	account_id	payment_date	amount_usd	payment_status
1001	101	2024-01-20	500.00	succeeded
1002	102	2024-02-25	1200.00	succeeded
1003	103	2024-01-25	0.00	failed

Expected output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	2	2	1.0000	3000.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	1	0.5000	400.00	3

Schema

rippling_customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment based on employee count
region	VARCHAR(20)	Customer geographic region
signup_date	DATE	Date the customer signed up

rippling_accounts

Column	Type	Description
account_idPK	INT	Unique billing account identifier
customer_id	INT	Customer associated with the account
plan_name	VARCHAR(30)	Subscribed Rippling plan
account_status	VARCHAR(20)	Current billing account status

rippling_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
account_id	INT	Billing account tied to the payment
payment_date	DATE	Date the payment was processed
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment outcome

rippling_accounts

account_id	customer_id	plan_name	account_status
108	8	Core Platform	active
101	1	Core Platform	active
105	5	Global Payroll	active
103	3	Payroll	trial
109	9	Core Platform	active
102	2	Core Platform	active
104	4	Payroll	active
106	6	Core Platform	canceled
110	10		trial
111	999	Core Platform	active
1	94	Policy Management	renewing
2	5	Onboarding	inactive
3	58	App Management	pending
4	60	Global Payroll	live
5	77	Analytics	on_hold
6	39	Global Payroll	reactivated
7	93	Employer of Record	live
8	26	Employer of Record	pending
9	74	Compliance	pending
10	87	Recruiting	past_due
11	43	Employer of Record	pending
12	90	null	expired
13	74	Performance	delinquent
14	57	Recruiting	on_hold
15	65	Payroll	setup
16	34	Spend	past_due
17	81	Workflow Studio	churned
18	70	Workflow Studio	paused
19	12	HR Help Desk	reactivated

rippling_payments

payment_id	account_id	payment_date	amount_usd	payment_status
1008	108	2024-02-20	1500.00	succeeded
1002	101	2024-03-01	750.00	succeeded
1005	103	2024-02-20	400.00	succeeded
1001	101	2024-01-20	500.00	succeeded
1010	105	2024-02-18	3000.00	succeeded
1003	102	2024-02-25	1200.00	succeeded
1004	102	2024-01-25	800.00	succeeded
1006	104	2024-02-25	0.00	failed
1007	106	2024-03-10		failed
1009	109	2024-03-05	700.00	succeeded
1011	110	2024-03-01	200.00	refunded
1	68	2024-03-12	1651	succeeded
2	77	2024-02-02	1118	settled
3	33	2024-02-10	1896	canceled
4	82	2024-03-10	1981	scheduled
5	86	2024-02-15	74	canceled
6	78	2024-02-09	1241	settled
7	47	2024-03-06	851	partially_refunded
8	8	2024-03-05	-50	canceled
9	39	2024-02-15	142	refunded
10	26	2024-02-23	null	processing
11	77	2024-02-04	3015	completed
12	49	2024-01-26	2504	initiated
13	90	2024-03-08	320	reversed
14	74	2024-02-12	null	on_hold
15	1	2024-02-19	-108	posted
16	55	2024-02-10	null	settled
17	54	2024-01-22	2163	disputed
18	72	2024-03-03	3513	rejected

rippling_customers

customer_id	company_name	company_size_segment	region	signup_date
3	Blue Mesa	SMB	East	2024-01-15
1	Northstar Health	SMB	West	2024-01-05
8	Pioneer Works	Mid-Market	West	2024-02-01
5	Atlas Freight	Enterprise	East	2024-01-20
2	Cedar Labs	Mid-Market	West	2024-01-10
7	Lattice Grove	SMB	West	2024-02-05
4	Harbor One	SMB	East	2024-02-10
6	Quartz Retail	Enterprise		2024-02-15
9	Summit Bio	Mid-Market	West	2024-02-18
10	Elm Security		South	2024-02-20
1	Pioneer Works	Upper Mid-Market	UK	2024-01-13
2	Silverline Ops	Startup	Great Lakes	2024-01-27
3	Crescent Media	Healthcare	Mountain	2024-01-22
4	Mosaic Commerce	Scale-Up	Europe	2024-02-21
5	Oakline Dental	Private Equity	North	2024-02-05
6	Vertex Motion	Hospitality	North	2024-01-21
7	Lattice Grove	Nonprofit	South	2024-02-07
8	Elm Security	SMB	Europe	2024-01-26
9	Pioneer Works	Growth	Southeast	2024-01-22
10	Lattice Grove	National	Mountain	2024-02-22
11	Elm Security	Nonprofit	South	2024-02-22
12	Blue Mesa	Mid-Market	East	2024-01-19
13	Northstar Health	Scale-Up	null	2024-02-16
14	Maple Peak	Technology	Canada	2024-02-14
15	Harbor One	International	Mid-Atlantic	2024-02-19

Expected Output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	3	3	1.0000	4200.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	0	0.0000	400.00	3

Practice SQL

PostgreSQL

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

Up next

Rank Top Funnel SegmentsMedium ARank Campaign Segments by ConversionMedium

Rank Top Clients by Product RevenueMedium

Next question

Rank Customer Segments by Revenue

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsGroup ByRanking

Also asked at

Problem

Task

Use the segmentation dimensions already stored on the customer record, and only include segments with at least 2 customers.

Schema

`rippling_customers`

column	type	description
customer_id	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment by employee count
region	VARCHAR(20)	Customer region
signup_date	DATE	Date the customer signed up

`rippling_accounts`

column	type	description
account_id	INT	Unique billing account identifier
customer_id	INT	Customer tied to the billing account
plan_name	VARCHAR(30)	Purchased plan
account_status	VARCHAR(20)	Current account status

`rippling_payments`

column	type	description
payment_id	INT	Unique payment identifier
account_id	INT	Billing account identifier
payment_date	DATE	Date of payment
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment result

Sample data

`rippling_customers`

customer_id	company_name	company_size_segment	region	signup_date
1	Northstar Health	SMB	West	2024-01-05
2	Cedar Labs	Mid-Market	West	2024-01-10
3	Blue Mesa	SMB	East	2024-01-15

`rippling_accounts`

account_id	customer_id	plan_name	account_status
101	1	Core Platform	active
102	2	Core Platform	active
103	3	Payroll	trial

`rippling_payments`

payment_id	account_id	payment_date	amount_usd	payment_status
1001	101	2024-01-20	500.00	succeeded
1002	102	2024-02-25	1200.00	succeeded
1003	103	2024-01-25	0.00	failed

Expected output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	2	2	1.0000	3000.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	1	0.5000	400.00	3

Schema

rippling_customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment based on employee count
region	VARCHAR(20)	Customer geographic region
signup_date	DATE	Date the customer signed up

rippling_accounts

Column	Type	Description
account_idPK	INT	Unique billing account identifier
customer_id	INT	Customer associated with the account
plan_name	VARCHAR(30)	Subscribed Rippling plan
account_status	VARCHAR(20)	Current billing account status

rippling_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
account_id	INT	Billing account tied to the payment
payment_date	DATE	Date the payment was processed
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment outcome

rippling_accounts

account_id	customer_id	plan_name	account_status
108	8	Core Platform	active
101	1	Core Platform	active
105	5	Global Payroll	active
103	3	Payroll	trial
109	9	Core Platform	active
102	2	Core Platform	active
104	4	Payroll	active
106	6	Core Platform	canceled
110	10		trial
111	999	Core Platform	active
1	94	Policy Management	renewing
2	5	Onboarding	inactive
3	58	App Management	pending
4	60	Global Payroll	live
5	77	Analytics	on_hold
6	39	Global Payroll	reactivated
7	93	Employer of Record	live
8	26	Employer of Record	pending
9	74	Compliance	pending
10	87	Recruiting	past_due
11	43	Employer of Record	pending
12	90	null	expired
13	74	Performance	delinquent
14	57	Recruiting	on_hold
15	65	Payroll	setup
16	34	Spend	past_due
17	81	Workflow Studio	churned
18	70	Workflow Studio	paused
19	12	HR Help Desk	reactivated

rippling_payments

payment_id	account_id	payment_date	amount_usd	payment_status
1008	108	2024-02-20	1500.00	succeeded
1002	101	2024-03-01	750.00	succeeded
1005	103	2024-02-20	400.00	succeeded
1001	101	2024-01-20	500.00	succeeded
1010	105	2024-02-18	3000.00	succeeded
1003	102	2024-02-25	1200.00	succeeded
1004	102	2024-01-25	800.00	succeeded
1006	104	2024-02-25	0.00	failed
1007	106	2024-03-10		failed
1009	109	2024-03-05	700.00	succeeded
1011	110	2024-03-01	200.00	refunded
1	68	2024-03-12	1651	succeeded
2	77	2024-02-02	1118	settled
3	33	2024-02-10	1896	canceled
4	82	2024-03-10	1981	scheduled
5	86	2024-02-15	74	canceled
6	78	2024-02-09	1241	settled
7	47	2024-03-06	851	partially_refunded
8	8	2024-03-05	-50	canceled
9	39	2024-02-15	142	refunded
10	26	2024-02-23	null	processing
11	77	2024-02-04	3015	completed
12	49	2024-01-26	2504	initiated
13	90	2024-03-08	320	reversed
14	74	2024-02-12	null	on_hold
15	1	2024-02-19	-108	posted
16	55	2024-02-10	null	settled
17	54	2024-01-22	2163	disputed
18	72	2024-03-03	3513	rejected

rippling_customers

customer_id	company_name	company_size_segment	region	signup_date
3	Blue Mesa	SMB	East	2024-01-15
1	Northstar Health	SMB	West	2024-01-05
8	Pioneer Works	Mid-Market	West	2024-02-01
5	Atlas Freight	Enterprise	East	2024-01-20
2	Cedar Labs	Mid-Market	West	2024-01-10
7	Lattice Grove	SMB	West	2024-02-05
4	Harbor One	SMB	East	2024-02-10
6	Quartz Retail	Enterprise		2024-02-15
9	Summit Bio	Mid-Market	West	2024-02-18
10	Elm Security		South	2024-02-20
1	Pioneer Works	Upper Mid-Market	UK	2024-01-13
2	Silverline Ops	Startup	Great Lakes	2024-01-27
3	Crescent Media	Healthcare	Mountain	2024-01-22
4	Mosaic Commerce	Scale-Up	Europe	2024-02-21
5	Oakline Dental	Private Equity	North	2024-02-05
6	Vertex Motion	Hospitality	North	2024-01-21
7	Lattice Grove	Nonprofit	South	2024-02-07
8	Elm Security	SMB	Europe	2024-01-26
9	Pioneer Works	Growth	Southeast	2024-01-22
10	Lattice Grove	National	Mountain	2024-02-22
11	Elm Security	Nonprofit	South	2024-02-22
12	Blue Mesa	Mid-Market	East	2024-01-19
13	Northstar Health	Scale-Up	null	2024-02-16
14	Maple Peak	Technology	Canada	2024-02-14
15	Harbor One	International	Mid-Atlantic	2024-02-19

Expected Output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	3	3	1.0000	4200.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	0	0.0000	400.00	3

Problem

Task

Use the segmentation dimensions already stored on the customer record, and only include segments with at least 2 customers.

Schema

`rippling_customers`

column	type	description
customer_id	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment by employee count
region	VARCHAR(20)	Customer region
signup_date	DATE	Date the customer signed up

`rippling_accounts`

column	type	description
account_id	INT	Unique billing account identifier
customer_id	INT	Customer tied to the billing account
plan_name	VARCHAR(30)	Purchased plan
account_status	VARCHAR(20)	Current account status

`rippling_payments`

column	type	description
payment_id	INT	Unique payment identifier
account_id	INT	Billing account identifier
payment_date	DATE	Date of payment
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment result

Sample data

`rippling_customers`

customer_id	company_name	company_size_segment	region	signup_date
1	Northstar Health	SMB	West	2024-01-05
2	Cedar Labs	Mid-Market	West	2024-01-10
3	Blue Mesa	SMB	East	2024-01-15

`rippling_accounts`

account_id	customer_id	plan_name	account_status
101	1	Core Platform	active
102	2	Core Platform	active
103	3	Payroll	trial

`rippling_payments`

payment_id	account_id	payment_date	amount_usd	payment_status
1001	101	2024-01-20	500.00	succeeded
1002	102	2024-02-25	1200.00	succeeded
1003	103	2024-01-25	0.00	failed

Expected output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	2	2	1.0000	3000.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	1	0.5000	400.00	3

Schema

rippling_customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
company_name	VARCHAR(100)	Customer company name
company_size_segment	VARCHAR(20)	Segment based on employee count
region	VARCHAR(20)	Customer geographic region
signup_date	DATE	Date the customer signed up

rippling_accounts

Column	Type	Description
account_idPK	INT	Unique billing account identifier
customer_id	INT	Customer associated with the account
plan_name	VARCHAR(30)	Subscribed Rippling plan
account_status	VARCHAR(20)	Current billing account status

rippling_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
account_id	INT	Billing account tied to the payment
payment_date	DATE	Date the payment was processed
amount_usd	DECIMAL(10,2)	Payment amount in USD
payment_status	VARCHAR(20)	Payment outcome

rippling_accounts

account_id	customer_id	plan_name	account_status
108	8	Core Platform	active
101	1	Core Platform	active
105	5	Global Payroll	active
103	3	Payroll	trial
109	9	Core Platform	active
102	2	Core Platform	active
104	4	Payroll	active
106	6	Core Platform	canceled
110	10		trial
111	999	Core Platform	active
1	94	Policy Management	renewing
2	5	Onboarding	inactive
3	58	App Management	pending
4	60	Global Payroll	live
5	77	Analytics	on_hold
6	39	Global Payroll	reactivated
7	93	Employer of Record	live
8	26	Employer of Record	pending
9	74	Compliance	pending
10	87	Recruiting	past_due
11	43	Employer of Record	pending
12	90	null	expired
13	74	Performance	delinquent
14	57	Recruiting	on_hold
15	65	Payroll	setup
16	34	Spend	past_due
17	81	Workflow Studio	churned
18	70	Workflow Studio	paused
19	12	HR Help Desk	reactivated

rippling_payments

payment_id	account_id	payment_date	amount_usd	payment_status
1008	108	2024-02-20	1500.00	succeeded
1002	101	2024-03-01	750.00	succeeded
1005	103	2024-02-20	400.00	succeeded
1001	101	2024-01-20	500.00	succeeded
1010	105	2024-02-18	3000.00	succeeded
1003	102	2024-02-25	1200.00	succeeded
1004	102	2024-01-25	800.00	succeeded
1006	104	2024-02-25	0.00	failed
1007	106	2024-03-10		failed
1009	109	2024-03-05	700.00	succeeded
1011	110	2024-03-01	200.00	refunded
1	68	2024-03-12	1651	succeeded
2	77	2024-02-02	1118	settled
3	33	2024-02-10	1896	canceled
4	82	2024-03-10	1981	scheduled
5	86	2024-02-15	74	canceled
6	78	2024-02-09	1241	settled
7	47	2024-03-06	851	partially_refunded
8	8	2024-03-05	-50	canceled
9	39	2024-02-15	142	refunded
10	26	2024-02-23	null	processing
11	77	2024-02-04	3015	completed
12	49	2024-01-26	2504	initiated
13	90	2024-03-08	320	reversed
14	74	2024-02-12	null	on_hold
15	1	2024-02-19	-108	posted
16	55	2024-02-10	null	settled
17	54	2024-01-22	2163	disputed
18	72	2024-03-03	3513	rejected

rippling_customers

customer_id	company_name	company_size_segment	region	signup_date
3	Blue Mesa	SMB	East	2024-01-15
1	Northstar Health	SMB	West	2024-01-05
8	Pioneer Works	Mid-Market	West	2024-02-01
5	Atlas Freight	Enterprise	East	2024-01-20
2	Cedar Labs	Mid-Market	West	2024-01-10
7	Lattice Grove	SMB	West	2024-02-05
4	Harbor One	SMB	East	2024-02-10
6	Quartz Retail	Enterprise		2024-02-15
9	Summit Bio	Mid-Market	West	2024-02-18
10	Elm Security		South	2024-02-20
1	Pioneer Works	Upper Mid-Market	UK	2024-01-13
2	Silverline Ops	Startup	Great Lakes	2024-01-27
3	Crescent Media	Healthcare	Mountain	2024-01-22
4	Mosaic Commerce	Scale-Up	Europe	2024-02-21
5	Oakline Dental	Private Equity	North	2024-02-05
6	Vertex Motion	Hospitality	North	2024-01-21
7	Lattice Grove	Nonprofit	South	2024-02-07
8	Elm Security	SMB	Europe	2024-01-26
9	Pioneer Works	Growth	Southeast	2024-01-22
10	Lattice Grove	National	Mountain	2024-02-22
11	Elm Security	Nonprofit	South	2024-02-22
12	Blue Mesa	Mid-Market	East	2024-01-19
13	Northstar Health	Scale-Up	null	2024-02-16
14	Maple Peak	Technology	Canada	2024-02-14
15	Harbor One	International	Mid-Atlantic	2024-02-19

Expected Output

company_size_segment	region	total_customers	converted_customers	conversion_rate	total_revenue_usd	revenue_rank
Mid-Market	West	3	3	1.0000	4200.00	1
SMB	West	2	1	0.5000	1250.00	2
SMB	East	2	0	0.0000	400.00	3

Practice SQL

PostgreSQL

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

Up next

Rank Top Funnel SegmentsMedium ARank Campaign Segments by ConversionMedium

Rank Top Clients by Product RevenueMedium

Next question