← Confluent Interview Insights

Confluent·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

Confluent data scientist interview with a SQL-heavy technical screen focused on A/B testing analysis. The question was more involved than it looked at first glance, requiring you to think carefully about what 'completion' even means when the max step differs by variant.

Questions Asked (1)

Q1

Given a users table and a tutorial events table, write a SQL query that returns, for each tutorial variant, the percentage of signed-up users who completed the tutorial (reached the maximum step) within 7 days of signing up.

A/B Testing & ExperimentationProduct Analytics & MetricsData Modeling
Author's notes

The 7-day filter tripped me up more than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and defining 'signed-up users' and 'completed' (reaching max step). Then write a query that joins users to their tutorial events, filters for completion within 7 days of signup, and calculates the percentage per variant using conditional aggregation.

Pro tip: Always confirm the definition of 'signed-up users'—it might mean users who started the tutorial, not just registered. Also, consider using a subquery to get the max step per variant to avoid hardcoding.

1. Clarify schema and definitions

Ask about table structures, column names, and what 'signed-up users' and 'completed' mean. Confirm if 'maximum step' is per variant or global.

2. Identify signed-up users per variant

Determine how users are assigned to variants (e.g., via a variant column in users or events). Filter to signed-up users only.

3. Find completions within 7 days

Join users to tutorial events, filter events where step equals the maximum step for that variant, and event timestamp is within 7 days of signup.

4. Calculate percentage per variant

Use conditional aggregation: count distinct users who completed divided by total signed-up users per variant, multiplied by 100.

5. Handle edge cases and validate

Consider users with no events, multiple completions, or timezone issues. Validate results with a small sample.

Key Points to Mention

  • Use LEFT JOIN to include all signed-up users, even those with no events.
  • Use COUNT(DISTINCT user_id) to avoid double-counting if multiple completion events exist.
  • Filter events with a date condition: event_time <= signup_time + INTERVAL '7 days'.
  • Determine max step per variant using a subquery or window function.
  • Group by variant and calculate percentage with division and multiplication by 100.0.
  • Consider timezone consistency and whether signup_time and event_time are in the same timezone.

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