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 |
