Compute churn metrics and rank top students
Company: Flatiron Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You have two tasks.
Part A (R): You receive a Customers CSV with columns: id (INT), signup_date (YYYY-MM-DD), last_active_date (YYYY-MM-DD), churned (0/1/NULL), pay (DECIMAL/NULL). Clean and compute metrics under these rules: (1) Drop rows where id is NULL/blank; (2) If pay is NULL, set pay = 0; (3) If churned is NULL but pay > 0, set churned = 0 (treat as active); (4) Define active_days = DATEDIFF(last_active_date, signup_date); if last_active_date < signup_date, set active_days = 0; (5) Treat last_active_date as the churn date when churned = 1. Produce: (a) the average active_days across all remaining customers; and (b) the average pay among customers with churned = 1. Return both rounded to 2 decimals and explain briefly how each rule is enforced in your code. Use idiomatic tidyverse R.
Sample data (for format clarity):
Customers
+-----+-------------+------------------+---------+------+
| id | signup_date | last_active_date | churned | pay |
+-----+-------------+------------------+---------+------+
| 1 | 2025-01-10 | 2025-03-05 | 1 | 99 |
| 2 | 2025-02-01 | 2025-02-20 | NULL | 15 |
| 3 | 2025-01-15 | 2025-01-18 | 0 | NULL |
| NULL| 2025-02-10 | 2025-02-12 | 1 | 50 |
| 4 | 2025-02-11 | 2025-02-05 | 1 | 25 |
+-----+-------------+------------------+---------+------+
Part B (SQL, MySQL 8.0): Using the schema below, write ONE query that: (i) computes each student’s avg_score across all grades; (ii) excludes students with zero completed assignments; (iii) keeps only the top CEIL(N/2) students by avg_score where N is the number of students with at least one grade; break ties using student_id ASC; (iv) outputs student_id, name, avg_score (rounded to 1 decimal), and a dense_rank over the selected students ordered by avg_score DESC, student_id ASC; and (v) still works correctly as more students/grades are added.
Schema:
Teachers(id PK, name, classroom)
Students(id PK, name, primary_teacher_id FK -> Teachers.id)
Assignments(id PK, teacher_id FK -> Teachers.id)
Grades(student_id FK -> Students.id, assignment_id FK -> Assignments.id, grade DECIMAL(4,1), PK(student_id, assignment_id))
Sample tables:
Teachers
+----+------------+----------+
| id | name | classroom|
+----+------------+----------+
| 4 | Mr. Feeny | 301 |
| 8 | Mr. Cooper | 260 |
+----+------------+----------+
Students
+----+---------+--------------------+
| id | name | primary_teacher_id |
+----+---------+--------------------+
| 1 | Bobby | 4 |
| 2 | Susie | 8 |
| 3 | Deborah | 8 |
| 9 | Bruce | NULL |
+----+---------+--------------------+
Assignments
+----+------------+
| id | teacher_id |
+----+------------+
| 1 | 4 |
| 2 | 8 |
+----+------------+
Grades
+------------+---------------+-------+
| student_id | assignment_id | grade |
+------------+---------------+-------+
| 1 | 1 | 100.0 |
| 1 | 2 | 50.0 |
| 2 | 1 | 100.0 |
| 2 | 2 | 100.0 |
| 3 | 1 | 40.0 |
| 3 | 2 | 8.0 |
| 9 | 1 | 65.0 |
| 9 | 2 | 65.0 |
+------------+---------------+-------+
Overview: This question evaluates proficiency in data cleaning, transformation, aggregation, and deterministic ranking across R (tidyverse) and SQL, focusing on handling missing values, date arithmetic, aggregation and rounding of metrics, and top-N selection with tie-breaking.
Clean customer churn data and compute summary metrics
You are given a `Customers` table (PostgreSQL) that contains raw, partially-dirty subscription records. Each row describes one customer's signup date, last active date, churn flag, and amount paid.
Write a **single SQL query** that first cleans the data according to the rules below, then returns one summary row.
**Cleaning rules (apply in order):**
1. **Drop** any row where `id` is `NULL`.
2. If `pay` is `NULL`, treat it as `0`.
3. If `churned` is `NULL` **and** `pay > 0`, treat `churned` as `0` (the customer is considered active). Leave `churned` unchanged otherwise.
4. Define `active_days` as the number of days between `signup_date` and `last_active_date` (i.e. `last_active_date - signup_date`). If `last_active_date < signup_date`, set `active_days` to `0`.
**Metrics to compute over the cleaned rows:**
- `avg_active_days`: the average of `active_days` across **all** remaining customers.
- `avg_pay_churned`: the average (cleaned) `pay` among customers whose cleaned `churned = 1`.
**Output:** return exactly **one row** with two columns, `avg_active_days` and `avg_pay_churned`, each **rounded to 2 decimal places**.
Tables
Customers(id INT, signup_date DATE, last_active_date DATE, churned SMALLINT, pay DECIMAL(10,2))
Hints
- Do the cleaning in a CTE first, then aggregate the cleaned columns — keeps the final SELECT simple.
- In PostgreSQL, `date_a - date_b` already returns the integer day difference; you do not need DATEDIFF.
Rank top students by average grade (top half by performance)
Using the schema below in MySQL 8.0, write ONE query that: (i) computes each student’s avg_score as the average of all their grades in the Grades table; (ii) excludes students who have zero completed assignments (i.e., no rows in Grades); (iii) among those students, keeps only the top CEIL(N/2) students by avg_score, where N is the number of students with at least one grade; break ties for ordering using student_id ASC; (iv) outputs columns student_id, name, avg_score (rounded to 1 decimal), and a dense_rank over the selected students ordered by avg_score DESC, student_id ASC; and (v) still works correctly as more students and grades are added.
Tables
Teachers(id INT, name VARCHAR(100), classroom VARCHAR(20))
Students(id INT, name VARCHAR(100), primary_teacher_id INT)
Assignments(id INT, teacher_id INT)
Grades(student_id INT, assignment_id INT, grade DECIMAL(4,1))
Hints
- First compute each student’s average grade using a GROUP BY over Grades joined to Students.
- Use window functions both to determine the number of students (for CEIL(N/2)) and to assign row numbers and dense ranks for ordering and filtering.