← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2023Remote

Summary

Meta data scientist interview with a SQL-heavy technical screen. The question was practical, commerce-flavored, and felt like something you'd actually write on the job.

Questions Asked (1)

Q1

Given a table of shop view events with user IDs, shop IDs, and timestamps, write a SQL query that returns the number of unique users who viewed each shop for each calendar day over the past 7 days.

Product Analytics & MetricsData Modeling
Author's notes

Pretty applied question, not a brain teaser.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and the definition of 'past 7 days' (e.g., relative to current date or max date in data). Then write a SQL query that filters events to the last 7 days, groups by shop and calendar day, and counts distinct user IDs. Use date functions to extract the day from the timestamp and ensure proper handling of time zones if needed.

Pro tip: Mention that you would check for data quality issues like duplicate events or missing user IDs, and consider using a subquery or CTE to filter dates first for better performance. Also, clarify whether 'unique users' means distinct users per shop per day or overall unique users across all shops per day.

1. Clarify requirements and schema

Ask about the table name, column names, data types, and time zone. Confirm the definition of 'past 7 days' and whether it includes today.

2. Filter events for the last 7 days

Use a WHERE clause with a date function to restrict events to the last 7 days relative to the current date or the maximum date in the table.

3. Group by shop and calendar day

Extract the date part from the timestamp and group by shop_id and date. Use COUNT(DISTINCT user_id) to count unique users per group.

4. Handle edge cases and optimize

Consider time zones, missing dates (shops with zero views), and performance. Use CTEs or subqueries to filter early and avoid scanning the entire table.

5. Write and explain the final query

Present the SQL query clearly, explaining each part. Optionally, discuss how you would validate the results or handle large-scale data.

Key Points to Mention

  • Use of COUNT(DISTINCT user_id) to count unique users per shop per day.
  • Date truncation or casting timestamp to date to get calendar day.
  • Filtering for the last 7 days using DATE_SUB, CURRENT_DATE, or equivalent functions.
  • Handling time zones if timestamps are in UTC and reporting is in local time.
  • Performance considerations: indexing on timestamp and shop_id, using CTEs to filter early.
  • Clarifying whether to include days with zero views (e.g., using a calendar table or left join).

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