Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Sep 2023Remote

Summary

Meta DS interview with a SQL-heavy analytical question about in-app survey data. One question but it had enough layers to keep me busy for a while.

Questions Asked (1)

Given a survey_responses table with impression and click timestamps and a score column, write a query to compute the overall response rate (clicks / impressions). Then calculate a quality metric two ways: averaging all available scores versus averaging only the first score each user gave per survey. Which approach would you use and why?

Product Analytics & MetricsA/B Testing & Experimentation

Author's notes

The response rate part was fine, just counting non-null click_ts over total rows.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by writing a clear SQL query that computes the overall response rate as clicks divided by impressions, ensuring you handle edge cases like zero impressions. Then, for the quality metric, write two separate queries: one averaging all scores and another averaging only the first score per user per survey using a window function or subquery. Finally, discuss the trade-offs between the two approaches, emphasizing that the first-score-only method reduces bias from users who respond multiple times and is often more appropriate for measuring initial user sentiment.

Pro tip: When discussing the quality metric, mention that the first-score-only approach aligns with how many product teams measure user satisfaction (e.g., first impression) and avoids overrepresenting highly active users. Also, note that the overall response rate might need to be segmented by user or survey to avoid Simpson's paradox.

1. Clarify the schema and assumptions

Confirm the table structure: each row likely represents an impression with optional click timestamp and a score. Assume impressions are unique events and clicks are a subset of impressions. Clarify if a user can have multiple impressions per survey.

2. Compute overall response rate

Write a SQL query that counts clicks (where click timestamp is not null) divided by total impressions. Use COUNT or SUM with CASE statements, and handle division by zero.

3. Compute average of all scores

Write a query that averages the score column across all rows where score is not null. This gives the simple average of all available scores.

4. Compute average of first score per user per survey

Use a window function like ROW_NUMBER() partitioned by user and survey, ordered by impression timestamp, to identify the first score. Then average only those first scores.

5. Compare and justify the preferred approach

Discuss that averaging only the first score per user per survey reduces bias from repeated responses and better reflects initial user sentiment. Recommend this approach for quality metric, but note that overall average might be useful for other purposes.

Key Points to Mention

Response rate definition: clicks / impressions, ensuring denominator is total impressions, not unique users.
Handling NULLs: clicks are indicated by non-null click timestamps; scores may be null and should be excluded from averages.
Window functions: use ROW_NUMBER() or FIRST_VALUE() to get the first score per user per survey.
Bias in averaging all scores: users who respond multiple times can skew the average, especially if their scores change over time.
First-score-only approach: aligns with measuring initial user sentiment and avoids overrepresentation of power users.
Potential segmentation: response rate and quality metrics may vary by survey, user cohort, or time, so consider breaking down results.

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