You are given a simplified Thumbtack-like marketplace schema in PostgreSQL. Assume UTC timestamps and weeks start on Monday. Treat "today" as 2025-09-01.
Schema:
Small sample tables: requests request_id | customer_id | category_id | request_ts -----------+-------------+-------------+--------------------- 1 | 101 | 10 | 2025-08-18 09:00:00 2 | 102 | 10 | 2025-08-24 10:30:00 3 | 103 | 11 | 2025-08-31 14:00:00 4 | 104 | 10 | 2025-09-01 08:15:00 5 | 105 | 12 | 2025-08-20 16:45:00 6 | 106 | 11 | 2025-08-19 12:05:00
quotes quote_id | request_id | pro_id | quote_ts | price ---------+------------+--------+----------------------+------ 11 | 1 | 201 | 2025-08-18 09:10:00 | 120 12 | 1 | 202 | 2025-08-18 09:12:00 | 115 13 | 2 | 201 | 2025-08-24 10:40:00 | 200 14 | 3 | 203 | 2025-08-31 14:30:00 | 150 15 | 3 | 204 | 2025-08-31 14:45:00 | 140 16 | 4 | 202 | 2025-09-01 08:25:00 | 180 17 | 5 | 205 | 2025-08-20 18:00:00 | 220 18 | 5 | 206 | 2025-08-21 09:00:00 | 210
bookings booking_id | request_id | pro_id | booking_ts | status -----------+------------+--------+----------------------+---------- 1001 | 1 | 202 | 2025-08-18 09:20:00 | completed 1002 | 2 | 201 | 2025-08-24 11:00:00 | canceled 1003 | 3 | 204 | 2025-08-31 15:00:00 | completed 1004 | 5 | 206 | 2025-08-21 10:00:00 | completed 1005 | 4 | 202 | 2025-09-01 08:40:00 | pending
pros pro_id | region | activated_ts ------+--------+------------- 201 | SF | 2025-01-15 202 | SF | 2025-02-12 203 | LA | 2024-12-01 204 | LA | 2025-03-05 205 | SF | 2025-07-20 206 | SF | 2025-06-30
categories category_id | name ------------+---------------- 10 | Plumbing 11 | House Cleaning 12 | Painting
Write SQL for the following: (a) For each category and calendar week between 2025-08-18 and 2025-09-01 inclusive, compute the 90th percentile (in minutes) of time-to-first-quote per request. Include only requests that received at least one quote. Output: week_start_date, category_id, p90_minutes. Use an ordered-set aggregate (e.g., percentile_disc or percentile_cont) over the distribution of (min(quote_ts) - request_ts). (b) For the last 30 days ending 2025-09-01 (i.e., 2025-08-03 through 2025-09-01), find the top 3 pros per category by count of completed bookings. Ties must share the same dense_rank. Output: category_id, pro_id, booking_count, dense_rank. (c) For quotes created on 2025-08-31 through 2025-09-01, compute each quote’s percent_rank of price within its request_id (lower price is better). Return only quotes in the cheapest 20% for their request. Break exact-price ties by lowest quote_id. Output: request_id, quote_id, price, percent_rank.