Write SQL for visibility, calls, and cohort activity
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
You have the following schema and toy data. Assume "today" = 2025-09-01.
users(user_id INT, signup_date DATE)
Sample:
user_id | signup_date
--------+------------
1 | 2025-08-31
2 | 2025-08-15
3 | 2025-06-01
4 | 2025-09-01
shops(shop_id INT, category TEXT)
Sample:
shop_id | category
--------+---------
10 | Bakery
11 | Grocery
12 | Pharmacy
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
Sample:
event_time | user_id | shop_id | position | visible | dwell_seconds
---------------------+---------+---------+----------+---------+--------------
2025-09-01 09:00:00 | 1 | 10 | 1 | true | 6
2025-09-01 09:00:05 | 1 | 11 | 4 | true | 2
2025-09-01 09:10:00 | 2 | 10 | 2 | true | 8
2025-09-01 10:00:00 | 3 | 12 | 1 | true | 12
2025-09-01 10:05:00 | 3 | 10 | 5 | false | 0
2025-09-01 11:00:00 | 4 | 11 | 2 | true | 7
calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
Sample:
started_at | caller_id | receiver_id
---------------------+-----------+------------
2025-09-01 08:00:00 | 1 | 2
2025-09-01 09:30:00 | 3 | 1
2025-08-31 22:00:00 | 2 | 4
Tasks (write SQL; be careful about edge cases like duplicates, invisible rows, and users with zero activity today):
A) For 2025-09-01, return shop_id, unique_viewers, avg_dwell_seconds among visible=true rows, for shops with unique_viewers >= 2, ordered by unique_viewers DESC then shop_id ASC. Use GROUP BY, HAVING, ORDER BY.
B) For 2025-09-01, join shops to visibility_events and compute, by category, the top_of_feed_visibility_rate = visible events with position <= 3 divided by all visible events. Return category and the rate rounded to 3 decimals, sorted DESC. Use a CASE and a JOIN.
C) For 2025-09-01, for each user_id, compute seconds_to_next_visible_event using a window function over that user's visible=true events ordered by event_time. Return user_id, event_time, seconds_to_next_visible_event (NULL for the last visible event in the day).
D) You’re told to compare new vs. old user engagement using only today’s snapshot (2025-09-01) and to “bucket by duration.” Define user_age_days = DATEDIFF(day, signup_date, DATE '2025-09-01'). Create buckets: [0,7), [7,30), [30,180), [180,INF). Define active_today as having at least one visible=true event on 2025-09-01 with dwell_seconds >= 5. Using only today’s data, compute per bucket: dau_today (distinct users with any visible=true event), active_users_today (distinct users with active_today), and active_rate_today = active_users_today / dau_today. Return bucket_label, dau_today, active_users_today, active_rate_today, sorted by bucket order. Discuss one bias introduced by restricting the denominator to users observed today.
E) On 2025-09-01, count distinct users who participated in at least one call (as caller or receiver). Provide two queries: one using UNION and one using UNION ALL, and explain precisely when they produce different counts on the sample data. Then state the general rule for when UNION ALL is safe here and when it will double count.
F) Bonus: Using a window function, for each shop_id on 2025-09-01 compute a 3-event moving average of dwell_seconds over time (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) and return shop_id, event_time, ma3_dwell_seconds.
Overview: This question evaluates a candidate's proficiency in SQL-based data manipulation, covering aggregations, joins, window functions, date arithmetic, deduplication, cohorting, and calculation of engagement metrics.
Read the full Meta Data Scientist interview experience this question came from
Shop visibility aggregates with HAVING and ORDER BY
Using the tables below, write a query for the date 2025-09-01 that returns, for each shop_id, the number of unique_viewers and the avg_dwell_seconds among rows with visible = TRUE. Only include shops where unique_viewers >= 2. Order the result by unique_viewers in descending order, then by shop_id in ascending order. Use GROUP BY, HAVING, and ORDER BY. Be careful to ignore invisible rows and to count each user at most once per shop.
Tables
users(user_id INT, signup_date DATE)
shops(shop_id INT, category VARCHAR(50))
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
Hints
- Filter to 2025-09-01 and visible = TRUE before aggregating.
- Use COUNT(DISTINCT user_id) in HAVING to enforce unique_viewers >= 2.
Top-of-feed visibility rate by shop category
For the date 2025-09-01, join shops to visibility_events and, for each shop category, compute top_of_feed_visibility_rate defined as: (number of visible = TRUE events with position <= 3) divided by (number of all visible = TRUE events). Return category and the rate rounded to 3 decimal places. Sort the result by top_of_feed_visibility_rate in descending order, breaking ties alphabetically by category. Use a JOIN between visibility_events and shops and a CASE expression to identify top-of-feed visibility.
Tables
users(user_id INT, signup_date DATE)
shops(shop_id INT, category VARCHAR(50))
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
Hints
- Filter to visible = TRUE events before joining to shops.
- Compute the rate as AVG(CASE WHEN position <= 3 THEN 1.0 ELSE 0.0 END).
Seconds to next visible event per user with window functions
For the date 2025-09-01, consider only visibility_events rows where visible = TRUE. For each such event, compute seconds_to_next_visible_event for that user as the number of seconds until their next visible = TRUE event later that day. Use a window function over each user_id, ordered by event_time. Return user_id, event_time, and seconds_to_next_visible_event, with NULL for the last visible event of the day for each user. Order the output by user_id and event_time.
Render `event_time` as `YYYY-MM-DD HH24:MI:SS` in the output.
Tables
users(user_id INT, signup_date DATE)
shops(shop_id INT, category VARCHAR(50))
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
Hints
- Use LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time).
- Compute the difference between the next event_time and the current event_time in seconds.
Cohort activity buckets and active rate bias
## Cohort Activity Buckets and Active-Rate Selection Bias
Using the `users` and `visibility_events` tables, analyze daily activity for **2025-09-01**, bucketed by how long each user had been signed up as of that date.
**Definitions**
- `user_age_days` = number of whole days between a user's `signup_date` and `DATE '2025-09-01'` (i.e. `DATE '2025-09-01' - signup_date`). A user who signed up on 2025-09-01 has `user_age_days = 0`.
- Assign each user to exactly one signup-age bucket and label it:
- `user_age_days` in `[0, 7)` -> `'0-6 days'`
- `user_age_days` in `[7, 30)` -> `'7-29 days'`
- `user_age_days` in `[30, 180)` -> `'30-179 days'`
- `user_age_days` >= 180 -> `'180+ days'`
- A user is counted in **DAU** for the day if they have at least one `visibility_events` row on 2025-09-01 with `visible = TRUE`.
- A user is **active_today** if they have at least one `visibility_events` row on 2025-09-01 with `visible = TRUE` **and** `dwell_seconds >= 5`.
**Task**
For each of the four buckets (every bucket must appear in the output, even if it has zero users that day), return:
- `bucket_label` — the bucket name.
- `dau_today` — count of distinct users in that bucket who have any `visible = TRUE` event on 2025-09-01.
- `active_users_today` — count of distinct users in that bucket who are `active_today`.
- `active_rate_today` — `active_users_today / dau_today`, rounded to 4 decimal places, or `NULL` when `dau_today = 0`.
Sort the rows in bucket order from youngest to oldest (`'0-6 days'`, `'7-29 days'`, `'30-179 days'`, `'180+ days'`).
**Also briefly note one bias** introduced by restricting the denominator (`dau_today`) to users who were *observed today* — i.e. only users with at least one visibility event on 2025-09-01.
Tables
users(user_id INT, signup_date DATE)
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
Hints
- PostgreSQL has no DATEDIFF — subtract one DATE from another (`DATE '2025-09-01' - signup_date`) to get an integer number of days.
- Compute one row per user from visibility_events first (DAU membership plus a MAX flag for dwell_seconds >= 5), then LEFT JOIN a fixed 4-row bucket list so empty buckets still appear.
Distinct call participants: UNION vs UNION ALL
On 2025-09-01, count distinct users who participated in at least one call, either as caller or as receiver. Show the effect of using UNION versus UNION ALL: write logic that uses UNION to deduplicate caller and receiver IDs, and logic that uses UNION ALL without deduplication, and report both counts side by side. Then explain, based on the sample data, when these two approaches produce different counts and state the general rule for when UNION ALL is safe here versus when it will double count participants.
Tables
users(user_id INT, signup_date DATE)
shops(shop_id INT, category VARCHAR(50))
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
Hints
- Build one derived table with UNION and another with UNION ALL, each over caller_id/receiver_id filtered to 2025-09-01.
- Think about what happens when the same user appears as both caller and receiver on the same day.
3-event moving average of dwell time per shop
For each shop_id on 2025-09-01, compute a 3-event moving average of dwell_seconds over time using a window function. The window for each row should be defined as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, partitioned by shop_id and ordered by event_time. Include both visible and invisible events (i.e., do not filter on visible). Return shop_id, event_time, and ma3_dwell_seconds (the 3-event moving average), ordered by shop_id and event_time.
Render `event_time` as `YYYY-MM-DD HH24:MI:SS` in the output.
Tables
users(user_id INT, signup_date DATE)
shops(shop_id INT, category VARCHAR(50))
visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
Hints
- Use AVG(dwell_seconds) as a window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
- Partition by shop_id and order by event_time; do not filter on visible.