← Flatiron Health Interview Insights

Flatiron Health·Data Scientist·Take-home Assignment·Intermediate

Intermediate
May 2026

Summary

Two-part take-home for a Data Scientist role at Flatiron Health, one chunk in R using tidyverse and one in SQL on MySQL 8.0. The problems were more involved than I expected for a screening stage, especially the SQL piece which had a dynamic top-N filter baked in.

Questions Asked (2)

Q1

You're given a Customers CSV with columns for id, signup date, last active date, a churned flag, and pay. Clean the data according to a specific set of rules (drop null ids, fill null pay with 0, infer churned status from pay, compute active days with a floor at zero) and then compute two metrics: average active days across all remaining customers, and average pay among churned customers. Use tidyverse R and round both results to 2 decimals.

Product Analytics & MetricsTechnical Trade-offs
Author's notes

The rules seemed straightforward at first but rule 3 tripped me up a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Walk through the data cleaning pipeline step by step, explaining the rationale for each rule and how you would implement it in tidyverse R. Then compute the two metrics, emphasizing the importance of rounding at the end and validating results. Finally, discuss potential pitfalls and how you would handle them.

Pro tip: Mention that you would validate the cleaning steps with sanity checks (e.g., ensuring no negative active days, checking the distribution of churned status) and that you would use `across()` or `if_else()` for efficient column-wise operations. Also, note that rounding should be applied only at the final step to avoid compounding rounding errors.

1. Load and inspect the data

Read the CSV using `read_csv()` and examine the structure, data types, and missing values to understand the cleaning needed.

2. Apply cleaning rules

Drop rows with null ids, fill null pay with 0, infer churned status from pay (e.g., churned if pay == 0), and compute active days as last active date minus signup date with a floor at zero.

3. Compute metrics

Calculate the average active days across all remaining customers and the average pay among churned customers using `summarise()`.

4. Round and present results

Round both averages to 2 decimal places using `round()` and present the final output clearly.

Key Points to Mention

  • Use `drop_na(id)` or `filter(!is.na(id))` to drop null ids.
  • Use `replace_na(pay, 0)` or `mutate(pay = ifelse(is.na(pay), 0, pay))` to fill null pay.
  • Infer churned status: if pay == 0, set churned = TRUE; otherwise, keep existing churned flag or set FALSE.
  • Compute active days: `as.numeric(last_active_date - signup_date)` and then `pmax(active_days, 0)` to floor at zero.
  • Calculate averages with `summarise(avg_active_days = mean(active_days), avg_pay_churned = mean(pay[churned == TRUE]))`.
  • Round final results with `round(..., 2)` and consider using `scales::percent` or formatting for presentation.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

Using a schema with Teachers, Students, Assignments, and Grades tables, write a single MySQL 8.0 query that computes each student's average score, excludes students with no grades, keeps only the top CEIL(N/2) students by average score (where N is the count of students with at least one grade), breaks ties by student_id ascending, and outputs student_id, name, rounded average score, and a dense rank. The query must remain correct as data grows.

Data ModelingAlgorithms & Data Structures
Author's notes

This one took me longer than I want to admit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into stages: first compute each student's average score and filter out those with no grades, then determine the cutoff for the top half using a window function, and finally rank and select the top students. Use CTEs to keep the logic clear and ensure the query scales correctly with data growth.

Pro tip: Explicitly handle ties and the CEIL(N/2) cutoff with window functions like ROW_NUMBER or DENSE_RANK, and mention that using a subquery to compute N avoids hardcoding and keeps the query correct as data grows.

1. Compute average scores per student

Join Students to Grades (and Assignments if needed) and group by student to calculate AVG(score), filtering out students with no grades using an INNER JOIN or HAVING COUNT(grade) > 0.

2. Determine the cutoff for top half

Use a window function or subquery to count the total number of students with grades (N) and compute CEIL(N/2) to know how many top students to keep.

3. Rank students by average score

Apply DENSE_RANK() OVER (ORDER BY average_score DESC, student_id ASC) to assign ranks, ensuring ties are broken by student_id ascending.

4. Filter to top CEIL(N/2) students

Select only rows where the dense rank is less than or equal to CEIL(N/2), using a CTE or subquery to reference the computed cutoff.

5. Format and output final result

Round the average score to the desired precision (e.g., ROUND(AVG(score), 2)) and output student_id, name, rounded average, and dense rank.

Key Points to Mention

  • Use of INNER JOIN or HAVING to exclude students with no grades
  • Window functions: DENSE_RANK() for ranking and COUNT() OVER () for total N
  • CEIL(N/2) calculation and dynamic filtering without hardcoding
  • Tie-breaking logic: ORDER BY average_score DESC, student_id ASC
  • CTEs for readability and maintainability
  • Scalability: avoiding self-joins or correlated subqueries that degrade with data growth

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.