Write complex joins and window functions
Company: Thumbtack
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
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:
- requests(request_id INT PRIMARY KEY, customer_id INT, category_id INT, request_ts TIMESTAMP)
- quotes(quote_id INT PRIMARY KEY, request_id INT, pro_id INT, quote_ts TIMESTAMP, price NUMERIC)
- bookings(booking_id INT PRIMARY KEY, request_id INT, pro_id INT, booking_ts TIMESTAMP, status TEXT) -- status in ('completed','canceled','pending')
- pros(pro_id INT PRIMARY KEY, region TEXT, activated_ts DATE)
- categories(category_id INT PRIMARY KEY, name TEXT)
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.
Quick Answer: This question evaluates advanced SQL analytics skills including complex joins, window functions, ordered-set aggregates and percentile calculations, time/date arithmetic, grouping and dense-ranking for computing time-to-first-quote, top-N per category, and within-request price percentiles.