Seat Utilization

ASKED IN INTERVIEW

4pts

Slack, Zoom

Problem Statement

Slack’s enterprise team wants to identify accounts at risk of churning. A high-risk account is defined as a company that has purchased "Pro" seats but is using less than 20% of them.

Write an SQL query to find these companies.

Rules:

Only consider "Pro" plan subscriptions.
Utilization Rate = (active_seats / purchased_seats) * 100.
active_seats is the count of unique users from the CompanyUsers table with an active status.
Output columns: company_id, company_name, purchased_seats, active_seats, utilization_rate.
Round utilization_rate to 2 decimal places.
Results must be returned in ascending order of utilization_rate.

Table Schema:

Companies: company_id, company_name.
Subscriptions: sub_id, company_id, plan_type (Pro, Basic), purchased_seats (INTEGER).
CompanyUsers: user_id, company_id, status (active, inactive).

Tests your understanding of

Joins, Arithmetic, Filtering and SaaS Analytics

Input Tables

Companies

company_id(INTEGER)	company_name(VARCHAR)
1	TechCorp
2	DataViz
3	CloudNine

Subscriptions

company_id(INTEGER)	plan_type(VARCHAR)	purchased_seats(INTEGER)
1	Pro	1000
2	Pro	500
3	Pro	100

CompanyUsers

company_id(INTEGER)	user_id(INTEGER)	status(VARCHAR)
1	101	active
1	102	active
2	201	active
2	202	active
2	203	active
3	301	active
3	302	active

Expected Output

company_id(INTEGER)	company_name(VARCHAR)	purchased_seats(INTEGER)	active_seats(INTEGER)	utilization_rate(DECIMAL)
1	TechCorp	1000	2	0.2
2	DataViz	500	3	0.6
2	CloudNine	100	2	2