Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2023Remote

Summary

Meta Data Scientist technical screen, SQL-heavy with a multi-part question that combined window functions and a date-matched currency join. Felt manageable but the join condition tripped me up more than I expected.

Questions Asked (1)

For each user, write a SQL query that finds the ad with the highest click-through rate (clicks divided by impressions) over the last 7 days. Then, using an exchange rates table, convert each transaction amount to USD using the rate that matches the transaction date.

Product Analytics & MetricsData Modeling

Author's notes

Two parts jammed into one question.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into two independent SQL tasks: (1) compute CTR per ad per user over the last 7 days, then select the top ad per user using a window function; (2) join transactions to the exchange rates table on the transaction date to convert amounts to USD. Clarify assumptions about date ranges, ties, and rate availability before writing the queries.

Pro tip: Always state your assumptions about edge cases—like ads with zero impressions, ties in CTR, or missing exchange rates—and mention how you'd handle them (e.g., using ROW_NUMBER with a deterministic tiebreaker, or COALESCE for missing rates). This shows production-level thinking.

1. Clarify requirements and assumptions

Ask about the exact definition of 'last 7 days' (rolling vs. calendar), how to handle ties in CTR, and whether exchange rates are available for all dates. Confirm the grain of the output (one row per user per ad or just the top ad).

2. Compute CTR and rank ads per user

Write a subquery or CTE that aggregates clicks and impressions per user per ad over the last 7 days, calculates CTR as clicks/impressions, and uses ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY CTR DESC) to pick the top ad.

3. Convert transaction amounts to USD

Join the transactions table to the exchange rates table on the transaction date (and currency if needed), then multiply the amount by the rate to get USD. Handle missing rates with COALESCE or a fallback.

4. Combine results and validate

If needed, join the top ad per user with the converted transactions, ensuring the join keys are correct. Validate by checking for nulls, duplicates, and edge cases like users with no transactions or no ads.

Key Points to Mention

Use of window functions (ROW_NUMBER, RANK) to select the top ad per user efficiently.
Handling ties in CTR with deterministic ordering (e.g., by ad_id or impressions).
Date filtering for the last 7 days using CURRENT_DATE - INTERVAL '7 days' or equivalent.
Joining exchange rates on the exact transaction date, and considering currency codes if multiple currencies exist.
Edge cases: zero impressions (avoid division by zero), missing exchange rates, and users with no qualifying ads.
Performance considerations: indexing on date columns, partitioning, and avoiding cross joins.

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