Project 2 — SaaS Analytics
Activation, conversion, retention. What's actually going on?
Retention and activation are SaaS words. NOVA sells earbuds and desk lamps — where are the events and subscriptions coming from?
They aren't. Leadership read a SaaS metrics article and wants those exact words in the next report. NOVA has customers and orders, full stop. Welcome to the actual job — you're not usually handed the schema that fits the question.
This is the part nobody warns you about: the business asks for a metric that assumes a system NOVA doesn’t have. There’s no users table, no events, no subscriptions. What NOVA actually has is customers and orders.
Before writing a single query, the job is translation:
- A “user” is a customer.
- “Activation” — doing the thing that proves the product actually landed — is a customer’s first completed order.
- “Retention” is coming back and ordering again.
- “Expansion” is spending more on later orders than the first one.
None of this is exact — a real SaaS product tracks logins and feature usage, not purchases. But it’s an honest, defensible mapping, and it’s the same move you’ll make constantly in the real job: someone hands you a metric from a different world, and you decide what it means here, in writing, before you touch SQL.
Translate the ask into the data you actually have.
First question: of everyone who signed up, how many ever actually bought something?
Table: customers
| Column Name | Type |
|---|---|
| id | INTEGER |
| name | TEXT |
| country | TEXT |
| TEXT | |
| phone | TEXT |
| signup_date | DATE |
id is primary key. phone is nullable.
Table: products
| Column Name | Type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | DECIMAL(10,2) |
id is primary key.
Table: orders
| Column Name | Type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| amount | DECIMAL(10,2) |
| status | TEXT |
| placed_at | DATE |
id is primary key. customer_id is foreign key -> customers.id. product_id is foreign key -> products.id. amount is quantity * unit price at time of order. status is completed, pending, refunded, or cancelled.
Running…
83.3% — ten of NOVA’s twelve customers have completed at least one order. That LEFT JOIN matters: an inner join would silently drop the two customers who never ordered, and they’re the whole point of the question. COUNT(o.customer_id) only counts non-NULL matches, which is exactly what makes this work instead of COUNT(*).
Try it: What happens if you count COUNT(*) instead of COUNT(o.customer_id) on the joined result?
Run your query to see results here.
Both columns come back as 12 — every customer row survives the LEFT JOIN whether or not it matched, so COUNT(*) just counts rows, activated or not. The two never-ordered customers still show up as a row (with NULL in the orders side), they just don’t get counted by COUNT(column), which is exactly why that distinction is the whole trick here.
List the customers who never activated — the ones sitting in that 16.7%. Return name, country, and signup_date for every customer with zero completed orders.
Hint 1
This is the same LEFT JOIN shape as the activation query, but you want the rows where the join found nothing, not a count.
Hint 2
After a LEFT JOIN, an unmatched left-side row still appears, with every right-side column NULL. Filter on that.
Hint 3
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'completed', then WHERE o.id IS NULL.
Solution
Running…
Activation says someone showed up once. Retention asks whether they came back. In SaaS terms: of the customers who activated, what fraction are repeat customers?
WITH activity AS (
SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT
COUNT(*) AS activated_customers,
SUM(CASE WHEN completed_orders >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
ROUND(100.0 * SUM(CASE WHEN completed_orders >= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_pct
FROM activity;| activated_customers | repeat_customers | repeat_pct |
|---|---|---|
| 10 | 4 | 40 |
40% of activated customers bought more than once. That’s the honest NOVA analogue of a retention curve — not day-30 or day-90 retention, because there’s no login timestamp to build that from, but a real, defensible number about repeat behavior.
Last SaaS word: expansion — do returning customers spend more over time, or just repeat the same small purchase? For the four repeat customers, compare each one’s first order to the average of everything after it.
WITH seq AS (
SELECT o.customer_id, c.name, o.amount,
ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.placed_at) AS order_seq
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
)
SELECT customer_id, name,
MAX(CASE WHEN order_seq = 1 THEN amount END) AS first_order_amount,
ROUND(AVG(CASE WHEN order_seq > 1 THEN amount END), 2) AS avg_later_order_amount
FROM seq
GROUP BY customer_id, name
HAVING COUNT(*) >= 2
ORDER BY customer_id;| customer_id | name | first_order_amount | avg_later_order_amount |
|---|---|---|---|
| 1 | Alice Sharma | 79.99 | 69.49 |
| 2 | Bob Turner | 199.99 | 29.99 |
| 5 | Emma Wilson | 34.5 | 62.99 |
| 10 | Julia Souza | 49.99 | 64 |
No clean story here — Bob’s later orders are a fraction of his first, while Emma and Julia both spend more over time. Two expand, two contract. That’s a perfectly reportable finding: NOVA doesn’t have a consistent expansion pattern yet, on four customers. It would be dishonest to average those four into one number and call it a trend.
Leadership wants the flip side of activation: a “funnel” view. For each customer, label them ‘never activated’, ‘activated, one order’, or ‘activated, repeat’ based on how many completed orders they have, then count how many customers fall into each label.
Hint 1
You need one row per customer with their completed-order count first — a LEFT JOIN plus GROUP BY gets you that, same as the activation and retention queries above.
Hint 2
Once you have a completed-order count per customer (0, 1, or more), a CASE expression can turn that count into one of the three labels.
Hint 3
LEFT JOIN + GROUP BY c.id to get COUNT(o.id) per customer, wrap that count in a CASE (0 → never activated, 1 → one order, else → repeat), then GROUP BY that label in an outer query and COUNT(*).
Solution
Running…
The hardest part of this project was never SQL — it was resisting the urge to build a fake events table just to make the metric names fit. Real businesses ask for metrics from other businesses’ playbooks all the time. The job isn’t to refuse; it’s to say clearly what the metric means here, then answer that version honestly.
When a metric doesn’t map cleanly onto your schema, don’t force it or fake data to fit — write down the translation you’re making, then answer the translated question. A slightly different but honest metric beats an exact-sounding one built on data you don’t have.