Write SQL for retention, conversion, and churn
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Assume today is 2025-09-01 (use the user's local day boundaries based on users.tz). Given the following schema and sample data, write SQL to:
(a) Compute daily conversion rate by city and platform for the last 7 days ending today, where conversion rate = distinct users with ≥1 completed order that local day / distinct users with ≥1 session that local day. Handle days with no data by emitting zeroes.
(b) Flag city–platform pairs whose 7-day average conversion dropped by >30% versus the preceding 7 days (a 7-day rolling window ending 2025-08-25 to 2025-08-31 vs. 2025-08-18 to 2025-08-24). Use user-local dates and avoid double-counting users across platforms.
(c) Return the list of churned users as of today, where a user is churned if they had ≥1 completed order in [2025-07-07, 2025-08-10] but zero completed orders in [2025-08-11, 2025-09-01] in their local time. Include last_order_local_date and days_since_last_order.
(d) For the last 14 local days, compute cancellation rate by city (cancelled / all orders) and output the top 3 cities by largest absolute increase vs. the prior 14 days, with 95% Wilson CIs for each period.
Schema:
users(user_id INT, signup_date DATE, tz STRING)
app_sessions(session_id STRING, user_id INT, session_start_ts TIMESTAMP, city STRING, platform STRING)
orders(order_id STRING, user_id INT, order_ts TIMESTAMP, city STRING, platform STRING, status STRING) -- status ∈ {completed, cancelled, refunded}
Sample tables (minimal):
users
+---------+-------------+-----------------------+
| user_id | signup_date | tz |
+---------+-------------+-----------------------+
| 1 | 2025-06-01 | America/Los_Angeles |
| 2 | 2025-07-15 | America/New_York |
| 3 | 2025-08-01 | America/Los_Angeles |
| 4 | 2025-05-20 | America/Chicago |
| 5 | 2025-08-20 | America/New_York |
+---------+-------------+-----------------------+
app_sessions
+-----------+---------+---------------------+---------------+----------+
| session_id| user_id | session_start_ts | city | platform |
+-----------+---------+---------------------+---------------+----------+
| s1 | 1 | 2025-08-30 06:30:00 | San Francisco | iOS |
| s2 | 1 | 2025-08-31 07:15:00 | San Francisco | iOS |
| s3 | 2 | 2025-08-30 12:00:00 | New York | Android |
| s4 | 3 | 2025-08-15 18:10:00 | San Jose | Web |
| s5 | 3 | 2025-08-31 23:50:00 | San Jose | Web |
| s6 | 4 | 2025-08-05 01:05:00 | Chicago | iOS |
| s7 | 5 | 2025-09-01 00:20:00 | New York | iOS |
| s8 | 2 | 2025-09-01 03:59:59 | New York | Android |
+-----------+---------+---------------------+---------------+----------+
orders
+---------+---------+---------------------+---------------+----------+-----------+
| order_id| user_id | order_ts | city | platform | status |
+---------+---------+---------------------+---------------+----------+-----------+
| o1 | 1 | 2025-08-31 07:20:00 | San Francisco | iOS | completed |
| o2 | 1 | 2025-08-20 05:00:00 | San Francisco | iOS | cancelled |
| o3 | 2 | 2025-08-30 12:05:00 | New York | Android | completed |
| o4 | 3 | 2025-08-31 23:55:00 | San Jose | Web | completed |
| o5 | 4 | 2025-07-10 02:00:00 | Chicago | iOS | completed |
| o6 | 5 | 2025-09-01 00:25:00 | New York | iOS | completed |
| o7 | 2 | 2025-09-01 04:10:00 | New York | Android | completed |
| o8 | 3 | 2025-08-01 20:00:00 | San Jose | Web | refunded |
+---------+---------+---------------------+---------------+----------+-----------+
Your SQL should be ANSI-compliant, correctly convert UTC timestamps to user-local dates using users.tz, de-duplicate sessions and orders if needed, and avoid look-ahead bias in all rolling windows.
Overview: This question evaluates proficiency in SQL-based analytics and time-aware data manipulation, covering cohort and retention computation, conversion and churn metrics, user-level deduplication across platforms, timezone-local day handling, rolling-window comparisons, and calculation of statistical confidence intervals.
Daily conversion rate by city and platform (last 7 local days, with zero-filled days)
## Task
Using the `users`, `app_sessions`, and `orders` tables, compute the **daily conversion rate** for each `(local_date, city, platform)` combination over the 7-day window **2025-05-26 through 2025-06-01 (inclusive)**.
### Time-zone handling
- `app_sessions.session_start_ts` and `orders.order_ts` are stored as naive timestamps **in UTC**.
- Each user has a timezone in `users.tz` (an IANA name such as `America/Los_Angeles`).
- The reporting day must be the user's **local calendar date**. In PostgreSQL, convert a UTC timestamp to the user's local wall-clock time with:
`(ts AT TIME ZONE 'UTC' AT TIME ZONE u.tz)::date`
### Conversion rate definition
For a given `(local_date, city, platform)`:
- **`session_users`** = number of DISTINCT users with at least one session on that local date for that city + platform.
- **`converted_users`** = number of DISTINCT users with at least one order whose `status = 'completed'` on that local date for that city + platform.
- **`conversion_rate`** = `converted_users / session_users`, rounded to 4 decimal places. When `session_users = 0`, output `conversion_rate = 0`.
### Output requirements
- Build a **date spine** covering all 7 days so every date appears even when there is no activity.
- Include every `(city, platform)` pair that appears in **either** sessions **or** completed orders within the window, and **zero-fill** missing combinations (so a date with no sessions/orders for a pair still emits a row with `session_users = 0`, `converted_users = 0`, `conversion_rate = 0`).
- Return exactly these columns: `local_date`, `city`, `platform`, `session_users`, `converted_users`, `conversion_rate`.
- Sort by `local_date` ASC, then `city` ASC, then `platform` ASC.
Note: because a pair is included if it appears in *either* source, a `(date, city, platform)` may legitimately show `converted_users > 0` while `session_users = 0` (a completed order on a platform with no session that day); in that case `conversion_rate` is `0` by the rule above.
Tables
users(user_id INT, signup_date DATE, tz VARCHAR(64))
app_sessions(session_id VARCHAR(32), user_id INT, session_start_ts TIMESTAMP, city VARCHAR(64), platform VARCHAR(16))
orders(order_id VARCHAR(32), user_id INT, order_ts TIMESTAMP, city VARCHAR(64), platform VARCHAR(16), status VARCHAR(16))
Hints
- In PostgreSQL there is no CONVERT_TIMEZONE; get a user's local date with `(ts AT TIME ZONE 'UTC' AT TIME ZONE u.tz)::date` — the first AT TIME ZONE labels the naive timestamp as UTC, the second converts to the user's zone.
- Build the 7-day date spine with a RECURSIVE CTE and CROSS JOIN it to the set of (city, platform) pairs so every day x pair appears; LEFT JOIN your per-day distinct-user counts and COALESCE missing ones to 0.
Detect city-platform conversion drops >30% between two 7-day windows
You are analyzing a food-delivery app. All timestamps are stored in **UTC**, and each user has a home time zone in `users.tz` (an IANA name such as `America/Los_Angeles`). "Today" is **2025-06-01**. All day boundaries must use each user's **local** day (convert the UTC timestamp to the user's `tz`, then take the date).
For every `(city, platform)` pair, compute the **7-day average daily conversion rate** for two fixed windows:
- **Current window:** 2025-05-25 through 2025-05-31 (inclusive)
- **Prior window:** 2025-05-18 through 2025-05-24 (inclusive)
**Daily conversion rate** (computed per local day, per `city`+`platform`):
```
conversion_rate = (distinct users with >= 1 COMPLETED order that local day)
/ (distinct users with >= 1 session that local day)
```
A day on which a `(city, platform)` had **at least one session but no completed orders** counts as a `0.0` conversion-rate day. A day with **zero sessions** has an undefined rate and must NOT contribute to the average (treat it as the rate being absent for that day). The **window average** is the simple mean of the seven daily conversion rates in that window (count a no-session day as having no rate so it does not pull the average down).
Ground rules:
- Use `((ts AT TIME ZONE 'UTC') AT TIME ZONE u.tz)::date` to derive the local date from a stored UTC timestamp.
- Only orders with `status = 'completed'` count as conversions.
- De-duplicate users: a user counts once per `(local_date, city, platform)` even with multiple sessions/orders.
- Use ONLY the two explicit windows above (no look-ahead into 2025-06-01 data).
- Round each window average to **4 decimal places** before comparing.
**Flag** a `(city, platform)` pair when its current-window average dropped by **more than 30%** versus the prior-window average:
```
(current_avg - prior_avg) / prior_avg < -0.30
```
If `prior_avg` is 0 (or there is no prior-window rate), the pair is **not droppable** and must be excluded (never divide by zero).
**Output:** one row per flagged pair with columns `city`, `platform`, `prior_avg_conversion`, `current_avg_conversion`, and `pct_change` (the ratio above, rounded to 4 decimals). Sort by `city`, then `platform` ascending.
Tables
users(user_id INT, signup_date DATE, tz VARCHAR(64))
app_sessions(session_id VARCHAR(32), user_id INT, session_start_ts TIMESTAMP, city VARCHAR(64), platform VARCHAR(16))
orders(order_id VARCHAR(32), user_id INT, order_ts TIMESTAMP, city VARCHAR(64), platform VARCHAR(16), status VARCHAR(16))
Hints
- Convert a naive-UTC timestamp to a user-local date with `((ts AT TIME ZONE 'UTC') AT TIME ZONE u.tz)::date` — this is the Postgres stand-in for CONVERT_TIMEZONE.
- Build a date spine with `generate_series` and cross-join it with the active (city, platform) pairs so every day gets a row, then LEFT JOIN the distinct-user session and order counts.
Churned users as of 2025-06-01 (user-local time)
You have two tables tracking user orders. `orders.order_ts` is a naive `TIMESTAMP` whose values are recorded in **UTC**. Each user has a time zone in `users.tz` (an IANA name like `America/Los_Angeles`). All order-window comparisons must be done in **user-local time**, derived by converting `order_ts` from UTC into the user's `tz`, then taking the local calendar **date**.
Treat the current day as **2025-06-01**. A user is **churned** as of 2025-06-01 when **both** of the following hold:
- They have **at least one** order with `status = 'completed'` whose user-local date falls in the **old window** `[2025-04-06, 2025-05-10]` (inclusive), **AND**
- They have **zero** orders with `status = 'completed'` whose user-local date falls in the **recent window** `[2025-05-11, 2025-06-01]` (inclusive).
For every churned user, return exactly these three columns:
- `user_id`
- `last_order_local_date` — the **maximum** user-local date among that user's completed orders on or before 2025-06-01
- `days_since_last_order` — the number of whole days from `last_order_local_date` to 2025-06-01 (i.e. `DATE '2025-06-01' - last_order_local_date`)
Sort the result by `user_id` ascending.
**Postgres note:** convert a UTC `TIMESTAMP` into the user's local time with `(order_ts AT TIME ZONE 'UTC') AT TIME ZONE tz`, then cast to `DATE`.
Tables
users(user_id INT, signup_date DATE, tz VARCHAR(64))
orders(order_id VARCHAR(32), user_id INT, order_ts TIMESTAMP, city VARCHAR(64), platform VARCHAR(16), status VARCHAR(16))
Hints
- In PostgreSQL, convert a UTC TIMESTAMP to a user's local time with `(order_ts AT TIME ZONE 'UTC') AT TIME ZONE tz`, then cast to DATE before comparing against the windows.
- Group per user and use two conditional SUM(CASE ...) counters — one for the old window, one for the recent window — plus MAX(local_date) for the last order.
Cancellation-rate change by city with Wilson 95% confidence intervals (top 3 by absolute change)
Assume `orders.order_ts` is a naive `TIMESTAMP` whose value is in **UTC**, and "today" is **2025-06-01**. All period boundaries are evaluated on **user-local order dates**, where each order's local date is derived from its user's timezone (`users.tz`).
In PostgreSQL, convert a UTC timestamp to a user's local timestamp with:
`(o.order_ts AT TIME ZONE 'UTC') AT TIME ZONE u.tz`
then cast to `DATE` to get the local order date.
For each `city`, compute the cancellation rate over two inclusive 14-day periods, bucketed by **local order date**:
- **Recent period:** 2025-05-19 to 2025-06-01
- **Prior period:** 2025-05-05 to 2025-05-18
Definitions:
- `cancellation_rate = cancelled_orders / total_orders`
- `total_orders` counts **all** statuses (`completed`, `cancelled`, `refunded`); `cancelled_orders` counts only `status = 'cancelled'`.
Return the **TOP 3 cities** ranked by the **largest absolute change** in cancellation rate, `abs_increase = ABS(recent_rate - prior_rate)` (ties broken by `city` ascending).
For each of those 3 cities output exactly these columns, in this order:
`city`, `prior_orders`, `prior_cancelled`, `prior_cancel_rate`, `prior_ci_lower`, `prior_ci_upper`, `recent_orders`, `recent_cancelled`, `recent_cancel_rate`, `recent_ci_lower`, `recent_ci_upper`, `signed_increase`, `abs_increase`.
- `prior_cancel_rate` / `recent_cancel_rate` are the cancellation rates for each period.
- `signed_increase = recent_rate - prior_rate`; `abs_increase = ABS(signed_increase)`.
- `*_ci_lower` / `*_ci_upper` are the **95% Wilson confidence interval** bounds for each period's proportion (use **z = 1.96**).
**Wilson 95% CI** for a proportion `p = x/n` (with `x` = cancelled, `n` = total):
- `denom = 1 + z^2/n`
- `center = (p + z^2/(2n)) / denom`
- `half = z * sqrt((p*(1-p) + z^2/(4n)) / n) / denom`
- `CI = [center - half, center + half]`
**Rounding:** round every rate and CI bound (and `signed_increase`/`abs_increase`) to **4 decimal places**. Order the final 3 rows by `abs_increase` descending, then `city` ascending.
Tables
users(user_id INT, signup_date DATE, tz VARCHAR(64))
orders(order_id VARCHAR(32), user_id INT, order_ts TIMESTAMP, city VARCHAR(64), platform VARCHAR(16), status VARCHAR(16))
Hints
- In PostgreSQL convert the UTC timestamp to local with (order_ts AT TIME ZONE 'UTC') AT TIME ZONE u.tz, then cast to DATE before bucketing into the prior/recent periods.
- Aggregate x (cancelled) and n (total, all statuses) per city-period first, then plug them into the Wilson formula; use ::numeric and NULLIF(n,0) so division stays exact and safe.