Coding questions · Meta

Write SQL for retention, conversion, and churn

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.

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.

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
tz	VARCHAR(64)	NOT NULL

Sample data:

signup_date	tz	user_id
2025-04-01	America/Los_Angeles	1
2025-04-15	America/New_York	2
2025-05-01	America/Los_Angeles	3
2025-03-20	America/Chicago	4
2025-05-20	America/New_York	5

Table `app_sessions`

column	type	constraints
session_id	VARCHAR(32)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
session_start_ts	TIMESTAMP	NOT NULL
city	VARCHAR(64)	NOT NULL
platform	VARCHAR(16)	NOT NULL

Sample data:

city	platform	session_id	session_start_ts	user_id
San Francisco	iOS	sf_s0525	2025-05-25 19:00:00	1
San Francisco	iOS	sf_s0526	2025-05-26 19:00:00	1
San Francisco	iOS	sf_s0527	2025-05-27 19:00:00	1
San Francisco	iOS	sf_s0527b	2025-05-27 20:00:00	1
San Francisco	iOS	sf_s0528	2025-05-28 19:00:00	1
San Francisco	iOS	sf_s0529	2025-05-29 19:00:00	1
San Francisco	iOS	sf_s0530	2025-05-30 19:00:00	1
San Francisco	iOS	sf_s0531	2025-05-31 19:00:00	1
San Francisco	iOS	sf_s0601	2025-06-01 19:00:00	1
New York	Android	ny_s0526	2025-05-26 16:00:00	2
New York	Android	ny_s0527	2025-05-27 16:00:00	2
New York	Android	ny_s0528	2025-05-28 16:00:00	2
New York	Android	ny_s0529	2025-05-29 16:00:00	2
New York	Android	ny_s0530	2025-05-30 16:00:00	2
New York	Android	ny_s0531	2025-05-31 16:00:00	2
New York	Android	ny_s0601	2025-06-01 16:00:00	2
San Jose	Web	sj_s0530	2025-05-30 20:00:00	3
San Jose	Web	sj_s0601	2025-06-01 07:00:00	3
Chicago	iOS	chi_s0526	2025-05-26 13:00:00	4
New York	iOS	nyios_s0601	2025-06-01 03:00:00	5

Table `orders`

column	type	constraints
order_id	VARCHAR(32)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
city	VARCHAR(64)	NOT NULL
platform	VARCHAR(16)	NOT NULL
status	VARCHAR(16)	NOT NULL

Sample data:

city	order_id	order_ts	platform	status	user_id
Chicago	chi_r3	2025-05-26 18:00:00	Web	completed	1
Chicago	chi_r4	2025-05-28 18:00:00	Web	cancelled	1
Chicago	chi_r5	2025-05-31 18:00:00	Web	completed	1
San Francisco	sf_r08	2025-05-27 19:10:00	iOS	completed	1
San Francisco	sf_r09	2025-05-28 19:10:00	iOS	cancelled	1
San Francisco	sf_r10	2025-05-29 19:10:00	iOS	completed	1
San Francisco	sf_r11	2025-05-30 19:10:00	iOS	cancelled	1
San Francisco	sf_r12	2025-05-31 19:10:00	iOS	completed	1
San Francisco	sf_r13	2025-06-01 19:10:00	iOS	completed	1
New York	ny_r05	2025-05-27 16:10:00	Android	completed	2
New York	ny_r06	2025-05-29 16:10:00	Android	completed	2
New York	ny_r07	2025-05-31 16:10:00	Android	completed	2
New York	ny_ios1	2025-05-31 03:10:00	iOS	completed	5
San Jose	sj_r4	2025-05-27 20:10:00	Web	cancelled	3
San Jose	sj_r5	2025-05-30 20:10:00	Web	completed	3
San Jose	sj_r6	2025-06-01 07:05:00	Web	completed	3

Expected output

42 rows = 7 local dates (2025-05-26..2025-06-01) x 6 city-platform pairs (Chicago/iOS, Chicago/Web, New York/Android, New York/iOS, San Francisco/iOS, San Jose/Web), each with distinct session users, distinct completed-order users, and the conversion rate (0 when no sessions), ordered by local_date, city, platform.

city	conversion_rate	converted_users	local_date	platform	session_users
Chicago	0.0000	0	2025-05-26	iOS	1
Chicago	0	1	2025-05-26	Web	0
New York	0.0000	0	2025-05-26	Android	1
New York	0	0	2025-05-26	iOS	0
San Francisco	0.0000	0	2025-05-26	iOS	1
San Jose	0	0	2025-05-26	Web	0
Chicago	0	0	2025-05-27	iOS	0
Chicago	0	0	2025-05-27	Web	0
New York	1.0000	1	2025-05-27	Android	1
New York	0	0	2025-05-27	iOS	0
San Francisco	1.0000	1	2025-05-27	iOS	1
San Jose	0	0	2025-05-27	Web	0
Chicago	0	0	2025-05-28	iOS	0
Chicago	0	0	2025-05-28	Web	0
New York	0.0000	0	2025-05-28	Android	1
New York	0	0	2025-05-28	iOS	0
San Francisco	0.0000	0	2025-05-28	iOS	1
San Jose	0	0	2025-05-28	Web	0
Chicago	0	0	2025-05-29	iOS	0
Chicago	0	0	2025-05-29	Web	0
New York	1.0000	1	2025-05-29	Android	1
New York	0	0	2025-05-29	iOS	0
San Francisco	1.0000	1	2025-05-29	iOS	1
San Jose	0	0	2025-05-29	Web	0
Chicago	0	0	2025-05-30	iOS	0
Chicago	0	0	2025-05-30	Web	0
New York	0.0000	0	2025-05-30	Android	1
New York	0	1	2025-05-30	iOS	0
San Francisco	0.0000	0	2025-05-30	iOS	1
San Jose	1.0000	1	2025-05-30	Web	1
Chicago	0	0	2025-05-31	iOS	0
Chicago	0	1	2025-05-31	Web	0
New York	1.0000	1	2025-05-31	Android	1
New York	0.0000	0	2025-05-31	iOS	1
San Francisco	1.0000	1	2025-05-31	iOS	1
San Jose	0	0	2025-05-31	Web	0
Chicago	0	0	2025-06-01	iOS	0
Chicago	0	0	2025-06-01	Web	0
New York	0.0000	0	2025-06-01	Android	1
New York	0	0	2025-06-01	iOS	0
San Francisco	1.0000	1	2025-06-01	iOS	1
San Jose	1.0000	1	2025-06-01	Web	1

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.
Guard the division with NULLIF(session_users, 0) and wrap the result in COALESCE(..., 0) so zero-session rows report a rate of 0 instead of NULL.

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
tz	VARCHAR(64)	NOT NULL

Sample data:

signup_date	tz	user_id
2025-04-01	America/Los_Angeles	1
2025-04-15	America/New_York	2
2025-05-01	America/Los_Angeles	3
2025-03-20	America/Chicago	4
2025-05-20	America/New_York	5

Table `app_sessions`

column	type	constraints
session_id	VARCHAR(32)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
session_start_ts	TIMESTAMP	NOT NULL
city	VARCHAR(64)	NOT NULL
platform	VARCHAR(16)	NOT NULL

Sample data:

city	platform	session_id	session_start_ts	user_id
San Francisco	iOS	sf_s0518	2025-05-18 19:00:00	1
San Francisco	iOS	sf_s0519	2025-05-19 19:00:00	1
San Francisco	iOS	sf_s0520	2025-05-20 19:00:00	1
San Francisco	iOS	sf_s0521	2025-05-21 19:00:00	1
San Francisco	iOS	sf_s0522	2025-05-22 19:00:00	1
San Francisco	iOS	sf_s0523	2025-05-23 19:00:00	1
San Francisco	iOS	sf_s0524	2025-05-24 19:00:00	1
San Francisco	iOS	sf_s0525	2025-05-25 19:00:00	1
San Francisco	iOS	sf_s0526	2025-05-26 19:00:00	1
San Francisco	iOS	sf_s0527	2025-05-27 19:00:00	1
San Francisco	iOS	sf_s0527b	2025-05-27 20:00:00	1
San Francisco	iOS	sf_s0528	2025-05-28 19:00:00	1
San Francisco	iOS	sf_s0529	2025-05-29 19:00:00	1
San Francisco	iOS	sf_s0530	2025-05-30 19:00:00	1
San Francisco	iOS	sf_s0531	2025-05-31 19:00:00	1
New York	Android	ny_s0518	2025-05-18 16:00:00	2
New York	Android	ny_s0519	2025-05-19 16:00:00	2
New York	Android	ny_s0520	2025-05-20 16:00:00	2
New York	Android	ny_s0521	2025-05-21 16:00:00	2
New York	Android	ny_s0522	2025-05-22 16:00:00	2
New York	Android	ny_s0523	2025-05-23 16:00:00	2
New York	Android	ny_s0524	2025-05-24 16:00:00	2
New York	Android	ny_s0525	2025-05-25 16:00:00	2
New York	Android	ny_s0526	2025-05-26 16:00:00	2
New York	Android	ny_s0527	2025-05-27 16:00:00	2
New York	Android	ny_s0528	2025-05-28 16:00:00	2
New York	Android	ny_s0529	2025-05-29 16:00:00	2
New York	Android	ny_s0530	2025-05-30 16:00:00	2
New York	Android	ny_s0531	2025-05-31 16:00:00	2
San Jose	Web	sj_s0530	2025-05-30 20:00:00	3
Chicago	iOS	chi_s0526	2025-05-26 13:00:00	4
New York	iOS	nyios_s0601	2025-06-01 03:00:00	5

Table `orders`

column	type	constraints
order_id	VARCHAR(32)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
city	VARCHAR(64)	NOT NULL
platform	VARCHAR(16)	NOT NULL
status	VARCHAR(16)	NOT NULL

Sample data:

city	order_id	order_ts	platform	status	user_id
San Francisco	sf_p10	2025-05-18 19:10:00	iOS	completed	1
San Francisco	sf_r01	2025-05-19 19:10:00	iOS	completed	1
San Francisco	sf_r02	2025-05-20 19:10:00	iOS	cancelled	1
San Francisco	sf_r03	2025-05-20 19:20:00	iOS	completed	1
San Francisco	sf_r04	2025-05-21 19:10:00	iOS	completed	1
San Francisco	sf_r05	2025-05-22 19:10:00	iOS	completed	1
San Francisco	sf_r06	2025-05-23 19:10:00	iOS	completed	1
San Francisco	sf_r07	2025-05-24 19:10:00	iOS	cancelled	1
San Francisco	sf_r08	2025-05-27 19:10:00	iOS	completed	1
San Francisco	sf_r10	2025-05-29 19:10:00	iOS	completed	1
San Francisco	sf_r12	2025-05-31 19:10:00	iOS	completed	1
New York	ny_p08	2025-05-18 16:10:00	Android	completed	2
New York	ny_r01	2025-05-20 16:10:00	Android	completed	2
New York	ny_r02	2025-05-22 16:10:00	Android	completed	2
New York	ny_r03	2025-05-24 16:10:00	Android	completed	2
New York	ny_r04	2025-05-25 16:10:00	Android	completed	2
New York	ny_r05	2025-05-27 16:10:00	Android	completed	2
New York	ny_r06	2025-05-29 16:10:00	Android	completed	2
New York	ny_r07	2025-05-31 16:10:00	Android	completed	2

Expected output

One row per (city, platform) whose current 7-day avg conversion dropped >30% vs the prior 7-day avg; here only San Francisco / iOS qualifies.

city	current_avg_conversion	pct_change	platform	prior_avg_conversion
San Francisco	0.4286	-0.4999	iOS	0.8571

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.
Average the daily rates per window with conditional AVG(CASE WHEN local_date BETWEEN ... THEN rate END), and only divide by prior_avg when it is > 0.

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
tz	VARCHAR(64)	NOT NULL

Sample data:

signup_date	tz	user_id
2025-04-01	America/Los_Angeles	1
2025-04-15	America/New_York	2
2025-05-01	America/Los_Angeles	3
2025-03-20	America/Chicago	4
2025-05-20	America/New_York	5

Table `orders`

column	type	constraints
order_id	VARCHAR(32)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
city	VARCHAR(64)	NOT NULL
platform	VARCHAR(16)	NOT NULL
status	VARCHAR(16)	NOT NULL

Sample data:

city	order_id	order_ts	platform	status	user_id
Chicago	chi_old1	2025-04-10 15:00:00	iOS	completed	4
San Francisco	sf_p01	2025-05-05 19:10:00	iOS	completed	1
San Francisco	sf_p06	2025-05-10 19:10:00	iOS	completed	1
San Francisco	sf_r08	2025-05-27 19:10:00	iOS	completed	1
New York	ny_p03	2025-05-09 16:10:00	Android	completed	2
New York	ny_r05	2025-05-27 16:10:00	Android	completed	2
San Jose	sj_p3	2025-05-10 20:10:00	Web	completed	3
San Jose	sj_r6	2025-06-01 07:05:00	Web	completed	3

Expected output

One row per churned user: their last completed-order user-local date on/before 2025-06-01 and the whole days since that date. With this sample data only user 4 (Chicago) qualifies.

days_since_last_order	last_order_local_date	user_id
52	2025-04-10	4

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.
Subtracting two DATE values in Postgres (DATE '2025-06-01' - last_order_local_date) returns the number of days directly; no DATEDIFF needed.

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
tz	VARCHAR(64)	NOT NULL

Sample data:

signup_date	tz	user_id
2025-04-01	America/Los_Angeles	1
2025-04-15	America/New_York	2
2025-05-01	America/Los_Angeles	3
2025-03-20	America/Chicago	4
2025-05-20	America/New_York	5

Table `orders`

column	type	constraints
order_id	VARCHAR(32)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
city	VARCHAR(64)	NOT NULL
platform	VARCHAR(16)	NOT NULL
status	VARCHAR(16)	NOT NULL

Sample data:

city	order_id	order_ts	platform	status	user_id
Chicago	chi_p1	2025-05-06 18:00:00	Web	completed	1
Chicago	chi_p2	2025-05-08 18:00:00	Web	completed	1
Chicago	chi_p3	2025-05-12 18:00:00	Web	completed	1
Chicago	chi_p4	2025-05-15 18:00:00	Web	completed	1
Chicago	chi_p5	2025-05-18 18:00:00	Web	completed	1
Chicago	chi_r1	2025-05-20 18:00:00	Web	completed	1
Chicago	chi_r2	2025-05-22 18:00:00	Web	cancelled	1
Chicago	chi_r3	2025-05-26 18:00:00	Web	completed	1
Chicago	chi_r4	2025-05-28 18:00:00	Web	cancelled	1
Chicago	chi_r5	2025-05-31 18:00:00	Web	completed	1
San Francisco	sf_p01	2025-05-05 19:10:00	iOS	completed	1
San Francisco	sf_p02	2025-05-06 19:10:00	iOS	completed	1
San Francisco	sf_p03	2025-05-07 19:10:00	iOS	completed	1
San Francisco	sf_p04	2025-05-08 19:10:00	iOS	completed	1
San Francisco	sf_p05	2025-05-09 19:10:00	iOS	completed	1
San Francisco	sf_p06	2025-05-10 19:10:00	iOS	completed	1
San Francisco	sf_p07	2025-05-12 19:10:00	iOS	completed	1
San Francisco	sf_p08	2025-05-14 19:10:00	iOS	completed	1
San Francisco	sf_p09	2025-05-16 19:10:00	iOS	cancelled	1
San Francisco	sf_p10	2025-05-18 19:10:00	iOS	completed	1
San Francisco	sf_r01	2025-05-19 19:10:00	iOS	completed	1
San Francisco	sf_r02	2025-05-20 19:10:00	iOS	cancelled	1
San Francisco	sf_r03	2025-05-20 19:20:00	iOS	completed	1
San Francisco	sf_r04	2025-05-21 19:10:00	iOS	completed	1
San Francisco	sf_r05	2025-05-22 19:10:00	iOS	completed	1
San Francisco	sf_r06	2025-05-23 19:10:00	iOS	completed	1
San Francisco	sf_r07	2025-05-24 19:10:00	iOS	cancelled	1
San Francisco	sf_r08	2025-05-27 19:10:00	iOS	completed	1
San Francisco	sf_r09	2025-05-28 19:10:00	iOS	cancelled	1
San Francisco	sf_r10	2025-05-29 19:10:00	iOS	completed	1
San Francisco	sf_r11	2025-05-30 19:10:00	iOS	cancelled	1
San Francisco	sf_r12	2025-05-31 19:10:00	iOS	completed	1
San Francisco	sf_r13	2025-06-01 19:10:00	iOS	completed	1
New York	ny_p01	2025-05-05 16:10:00	Android	completed	2
New York	ny_p02	2025-05-07 16:10:00	Android	cancelled	2
New York	ny_p03	2025-05-09 16:10:00	Android	completed	2
New York	ny_p04	2025-05-11 16:10:00	Android	completed	2
New York	ny_p05	2025-05-13 16:10:00	Android	cancelled	2
New York	ny_p06	2025-05-15 16:10:00	Android	completed	2
New York	ny_p07	2025-05-17 16:10:00	Android	completed	2
New York	ny_p08	2025-05-18 16:10:00	Android	completed	2
New York	ny_r00	2025-05-19 16:10:00	Android	cancelled	2
New York	ny_r01	2025-05-20 16:10:00	Android	completed	2
New York	ny_r02	2025-05-22 16:10:00	Android	completed	2
New York	ny_r03	2025-05-24 16:10:00	Android	completed	2
New York	ny_r04	2025-05-25 16:10:00	Android	completed	2
New York	ny_r05	2025-05-27 16:10:00	Android	completed	2
New York	ny_r06	2025-05-29 16:10:00	Android	completed	2
New York	ny_r07	2025-05-31 16:10:00	Android	completed	2
New York	ny_ios1	2025-05-31 03:10:00	iOS	completed	5
San Jose	sj_p1	2025-05-06 20:10:00	Web	cancelled	3
San Jose	sj_p2	2025-05-08 20:10:00	Web	cancelled	3
San Jose	sj_p3	2025-05-10 20:10:00	Web	completed	3
San Jose	sj_p4	2025-05-12 20:10:00	Web	cancelled	3
San Jose	sj_p5	2025-05-14 20:10:00	Web	completed	3
San Jose	sj_p6	2025-05-16 20:10:00	Web	refunded	3
San Jose	sj_r1	2025-05-20 20:10:00	Web	cancelled	3
San Jose	sj_r2	2025-05-22 20:10:00	Web	cancelled	3
San Jose	sj_r3	2025-05-24 20:10:00	Web	completed	3
San Jose	sj_r4	2025-05-27 20:10:00	Web	cancelled	3
San Jose	sj_r5	2025-05-30 20:10:00	Web	completed	3
San Jose	sj_r6	2025-06-01 07:05:00	Web	completed	3

Expected output

Top 3 cities by absolute change in cancellation rate between the recent 14 days (2025-05-19..2025-06-01) and the prior 14 days (2025-05-05..2025-05-18), each with per-period order/cancel counts, rates, and Wilson 95% CIs, ordered by abs_increase desc then city asc.

abs_increase	city	prior_cancel_rate	prior_cancelled	prior_ci_lower	prior_ci_upper	prior_orders	recent_cancel_rate	recent_cancelled	recent_ci_lower	recent_ci_upper	recent_orders	signed_increase
0.4000	Chicago	0.0000	0	0.0000	0.4345	5	0.4000	2	0.1176	0.7693	5	0.4000
0.2077	San Francisco	0.1000	1	0.0179	0.4042	10	0.3077	4	0.1268	0.5763	13	0.2077
0.1389	New York	0.2500	2	0.0715	0.5907	8	0.1111	1	0.0199	0.4350	9	-0.1389

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.
Rank cities by ABS(recent_rate - prior_rate) and LIMIT 3 (not FETCH FIRST / SELECT TOP).

Daily conversion rate by city and platform (last 7 local days, with zero-filled days)

Task

Time-zone handling

Conversion rate definition

Output requirements

Table users

Table app_sessions

Table orders

Expected output

Hints

Detect city-platform conversion drops >30% between two 7-day windows

Table users

Table app_sessions

Table orders

Expected output

Hints

Churned users as of 2025-06-01 (user-local time)

Table users

Table orders

Expected output

Hints

Cancellation-rate change by city with Wilson 95% confidence intervals (top 3 by absolute change)

Table users

Table orders

Expected output

Hints

Table `users`

Table `app_sessions`

Table `orders`

Table `users`

Table `app_sessions`

Table `orders`

Table `users`

Table `orders`

Table `users`

Table `orders`