Quick Overview

This question evaluates data engineering competencies including SQL analytics with window functions and MERGE/INSERT patterns for idempotent incremental jobs, event-time handling (duplicates and late events), sessionization and retention metrics, plus Python-based JSON parsing, normalization, partitioned Parquet output and basic unit testing.

Write SQL and Python for data prep

Company: Meta

Role: Data Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

Given clickstream events (user_id, event_type, ts, properties) and a users table (user_id, signup_ts, plan), write SQL to compute DAU/WAU/MAU, D1/W1 retention cohorts, and sessionized metrics; implement an incremental daily job that updates aggregates idempotently using window functions and MERGE/INSERT patterns; and diagnose/handle duplicates and late events. Then, using Python (no heavy frameworks), implement a data-cleaning script that parses semi-structured JSON in the properties column, normalizes nested fields, and writes partitioned Parquet outputs with basic unit tests.

Overview: This question evaluates data engineering competencies including SQL analytics with window functions and MERGE/INSERT patterns for idempotent incremental jobs, event-time handling (duplicates and late events), sessionization and retention metrics, plus Python-based JSON parsing, normalization, partitioned Parquet output and basic unit testing.

Read the full Meta Data Engineer interview experience this question came from

Compute DAU, WAU, and MAU from clickstream events

You are given a clickstream_events table with user-level events and a users table with signup information. Using these tables, write a SQL query to compute, for each calendar day from 2025-05-18 to 2025-05-24 (inclusive), the number of distinct active users for: (1) that day (DAU), (2) the trailing 7-day window ending on that day (WAU), and (3) the trailing 30-day window ending on that day (MAU). A user is considered active on a day if they have at least one event whose ts falls on that calendar date. Return one row per activity_date with columns activity_date, dau, wau_7d, and mau_30d.

Tables

users(user_id INT, signup_ts TIMESTAMP, plan VARCHAR(20))

clickstream_events(event_id BIGINT, user_id INT, event_type VARCHAR(50), ts TIMESTAMP, ingested_at TIMESTAMP, properties VARCHAR(1000))

Hints

  1. Derive a calendar date from ts and work at the (user_id, activity_date) grain first.
  2. Use conditional COUNT(DISTINCT ...) over date ranges to compute DAU/WAU/MAU.

Compute D1 and W1 retention cohorts

Using the users and clickstream_events tables, compute Day-1 (D1) and Week-1 (W1) retention for signup cohorts in May 2025. Define cohort_date as CAST(signup_ts AS DATE). For each cohort_date in 2025-05-01 through 2025-05-31, return: (1) the number of users who signed up on that date (signups), (2) the number who had at least one event on cohort_date + 1 day (d1_retained_users), (3) the D1 retention rate (d1_retained_users / signups), (4) the number who had at least one event between cohort_date + 1 day and cohort_date + 7 days inclusive (w1_retained_users), and (5) the W1 retention rate (w1_retained_users / signups). Return one row per cohort_date.

Tables

users(user_id INT, signup_ts TIMESTAMP, plan VARCHAR(20))

clickstream_events(event_id BIGINT, user_id INT, event_type VARCHAR(50), ts TIMESTAMP, ingested_at TIMESTAMP, properties VARCHAR(1000))

Hints

  1. First map each user to a cohort_date based on signup_ts.
  2. Join cohorts to events and use conditional COUNT(DISTINCT ...) over date ranges for D1 and W1.

Sessionize clickstream events with a 30-minute inactivity threshold

Using the `clickstream_events` table, define user sessions over the inclusive date range from 2025-05-18 through 2025-05-21. Consider only events whose `ts` falls on one of those dates. For each `user_id`, sort events by `ts`. Start a new session when the gap from the previous event for that user is strictly greater than 30 minutes; otherwise keep the event in the same session. Session IDs should start at 1 for each user and increase chronologically. Return one row per session with: - `user_id` - `session_id` - `session_start` formatted as `YYYY-MM-DD HH24:MI:SS` - `session_end` formatted as `YYYY-MM-DD HH24:MI:SS` - `event_count` - `session_duration_minutes` as the integer minute difference between session end and start Order the output by `user_id`, then `session_id`.

Tables

clickstream_events(event_id BIGINT, user_id INT, event_type VARCHAR(50), ts TIMESTAMP, ingested_at TIMESTAMP, properties VARCHAR(1000))

Hints

  1. Use `LAG(ts)` over each user's chronological events to detect inactivity gaps.
  2. A running `SUM` of the new-session flag is a compact way to assign session IDs per user.

Incremental, idempotent DAU aggregation with deduplication and late-event handling

You maintain a daily DAU aggregate table, **`daily_user_activity`**, derived from raw clickstream events in **`clickstream_events`**. The raw feed has two data-quality issues you must handle: - **Duplicates**: the same logical event can be ingested more than once. A duplicate is any group sharing the same `(user_id, event_type, ts)`; the rows differ only in `event_id`, `ingested_at`, and possibly `properties`. Keep only the row with the **earliest `ingested_at`** in each such group, and discard the rest. - **Late events**: an event's `ingested_at` can land on a day later than its activity day `ts` (e.g. `event_id = 20` has `ts` on 2025-05-18 but `ingested_at` on 2025-05-20). Because of this you must **recompute** affected days from scratch rather than trust the previously stored DAU. Assume today is **2025-06-01**. Your incremental job recomputes DAU for `activity_date` in the inclusive window **2025-05-18 through 2025-05-21** using all events currently available, then upserts the recomputed values into `daily_user_activity` (idempotently: rows in that window are overwritten, any date in the window not yet present would be inserted). DAU for a day is the number of **distinct `user_id`s** whose deduplicated `ts` falls on that calendar day. Write a single PostgreSQL **`SELECT`** that returns the **full contents of `daily_user_activity` as it would look immediately after this upsert runs**. Specifically: - For each `activity_date` in 2025-05-18 .. 2025-05-21, return the freshly recomputed `dau` and a `last_updated_at` of `2025-06-01 00:00:00` (the recompute timestamp). - Any `daily_user_activity` row whose `activity_date` is outside the recompute window must be passed through unchanged (in this dataset there are none, but your query must not drop them). Return exactly the columns **`activity_date`, `dau`, `last_updated_at`**, one row per `activity_date`, sorted by `activity_date` ascending.

Tables

clickstream_events(event_id BIGINT, user_id INT, event_type VARCHAR(50), ts TIMESTAMP, ingested_at TIMESTAMP, properties VARCHAR(1000))

daily_user_activity(activity_date DATE, dau INT, last_updated_at TIMESTAMP)

Hints

  1. Deduplicate first: ROW_NUMBER() OVER (PARTITION BY user_id, event_type, ts ORDER BY ingested_at) and keep rn = 1 to retain the earliest-ingested copy.
  2. Bucket events by the activity day CAST(ts AS DATE) — not ingested_at — so late-arriving events land on the correct day; then COUNT(DISTINCT user_id) per day.

Community answers

Answer by ginb

with activity_dates as ( select ts::date as activity_date from clickstream_events where ts::date between '2025-05-18' and '2025-05-24' ) SELECT activity_date, count(distinct case when ts::date=activity_date then user_id end) as dau, count(distinct case when ts::date between activity_date -interval '7 days' and activity_date then c.user_id else null end) as wau_7d, count(distinct case when ts::date between activity_date -interval '30 days' and activity_date then c.user_id else null end) as mau_30d FROM clickstream_events c join activity_dates a on c.ts::date between a.activity_date - interval '30 days' and a.activity_date group by 1 having count(event_id)>0

Loading coding console...