Write SQL for monthly spend and ratios

Assume you are working with a food delivery dataset. ### Tables (schemas) **users** - user_id INT PRIMARY KEY - annual_income_usd INT - signup_ts TIMESTAMP **restaurants** - restaurant_id INT PRIMARY KEY - neighborhood_income_usd INT -- e.g., median income of the restaurant’s census tract **orders** - order_id INT PRIMARY KEY - user_id INT REFERENCES users(user_id) - restaurant_id INT REFERENCES restaurants(restaurant_id) - order_ts TIMESTAMP -- UTC - order_amount_usd NUMERIC(10,2) - status STRING -- e.g., 'completed', 'canceled' ### Definitions / assumptions - Only include orders with status = 'completed'. - “Month” means calendar month in UTC: DATE_TRUNC('month', order_ts). - When ties occur for “top spender”, return the smallest user_id. ### Tasks 1) **Monthly spend > $100:** For each user-month, compute total spend. Return rows where the user’s monthly spend is strictly greater than 100. - Output: month, user_id, monthly_spend 2) **Top spender each month among (1):** Considering only the user-months returned by (1), find the top spender for each month. - Output: month, user_id, monthly_spend 3) **Restaurant monthly spend change (MoM):** For each restaurant and month, compute monthly revenue and the month-over-month change (absolute and percent) versus the previous month. - Output: restaurant_id, month, monthly_revenue, prev_month_revenue, mom_change, mom_change_pct - Only output months where a previous month exists for that restaurant. 4) **Share of customers ordering from bottom-income restaurants:** Define “bottom 25% income restaurants” as restaurants whose neighborhood_income_usd is in the bottom quartile across all restaurants (using NTILE/percentile logic). For each month, compute: - numerator = number of distinct users who placed ≥1 completed order in that month at a bottom-quartile restaurant - denominator = number of distinct users who placed ≥1 completed order in that month at any restaurant - ratio = numerator / denominator Output: month, numerator_users, denominator_users, ratio