Write complex joins and window functions

Q: Write complex joins and window functions

This is a Data Manipulation (SQL/Python) interview question from Thumbtack for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Write complex joins and window functions

Comments (0)