← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jul 2026

Summary

Meta DS interview with a SQL question focused on Oculus app engagement. Pretty standard analytics prompt but the NULL handling detail tripped me up a bit.

Questions Asked (1)

Q1

Given a user activity table and an apps table, compute the percentage of total session time spent in each app category over the last 7 days relative to a given reference date. Handle cases where an app_id has no matching category.

Product Analytics & MetricsData Modeling
Author's notes

The core math is straightforward, a simple ratio of category duration to total duration.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., session time, reference date, category mapping). Then outline a SQL query that joins user activity with apps, filters to the last 7 days relative to the reference date, handles missing categories (e.g., using COALESCE to 'Unknown'), and computes the percentage of total session time per category. Finally, discuss edge cases and validation.

Pro tip: Mention that you would first check for data quality issues like null app_ids or negative session times, and consider using a LEFT JOIN to preserve all activity records even when app metadata is missing.

1. Clarify requirements and schema

Ask about the table structures, definitions of session time, and how to handle missing categories. Confirm the reference date and the 7-day window.

2. Filter and aggregate session time

Filter user activity to the last 7 days relative to the reference date, then sum session time per app_id.

3. Join with apps and handle missing categories

Left join the aggregated activity with the apps table on app_id, and replace null categories with a placeholder like 'Unknown'.

4. Compute percentage per category

Group by category, sum the session time, and divide by the total session time across all categories to get the percentage.

5. Validate and discuss edge cases

Check for anomalies, ensure percentages sum to 100%, and discuss how to handle apps with no activity or categories with zero time.

Key Points to Mention

  • Use of LEFT JOIN to include apps with no matching category and COALESCE to label them as 'Unknown' or 'Other'.
  • Filtering with a date range: activity_date BETWEEN reference_date - INTERVAL '7 days' AND reference_date (or equivalent).
  • Aggregation: SUM(session_time) per app_id, then per category, and overall total for percentage calculation.
  • Handling of NULLs: ensure missing categories are not dropped and that session_time is not null.
  • Consideration of time zones and whether the 7-day window includes the reference date.
  • Validation: check that percentages sum to 100% and investigate any unexpected results.

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