Write SQL for retention, conversion, and churn

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.