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.
Overview: 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.
Weekly 90th percentile time-to-first-quote by category
Using the marketplace schema below (PostgreSQL, UTC timestamps, weeks start on Monday), write a query that, for each category and calendar week between 2025-08-18 and 2025-09-01 inclusive, computes the 90th percentile (in minutes) of time-to-first-quote per request.
Consider only requests that received at least one quote. The time-to-first-quote for a request is defined as the difference between the earliest quote_ts and the request_ts for that request, in minutes.
Group results by the week_start_date (Monday date of the week containing request_ts) and category_id. Use an ordered-set aggregate (e.g., percentile_disc) over the distribution of (min(quote_ts) - request_ts) per request.
Output columns: week_start_date (DATE), category_id (INT), p90_minutes (NUMERIC).
Tables
requests(request_id INT, customer_id INT, category_id INT, request_ts TIMESTAMP)
quotes(quote_id INT, request_id INT, pro_id INT, quote_ts TIMESTAMP, price NUMERIC(10,2))
bookings(booking_id INT, request_id INT, pro_id INT, booking_ts TIMESTAMP, status TEXT)
pros(pro_id INT, region VARCHAR(10), activated_ts DATE)
categories(category_id INT, name TEXT)
Hints
- First compute minutes-to-first-quote per request in a CTE using MIN(quote_ts) and EXTRACT(EPOCH ...).
- Then aggregate those per-request values by week_start_date and category_id using percentile_disc(0.9) WITHIN GROUP.
Top 3 pros per category by completed bookings with dense_rank
Using the same schema, write a query that, for the period from 2025-08-03 through 2025-09-01 inclusive, finds the top 3 pros per category by count of completed bookings.
A booking is counted only if bookings.status = 'completed' and booking_ts falls within this date range. Join bookings to requests to determine the category of each booking.
Within each category_id, rank pros by booking_count in descending order using DENSE_RANK, so that ties share the same rank. Return only pros with dense_rank <= 3.
Output columns: category_id (INT), pro_id (INT), booking_count (INT), dense_rank (INT).
Tables
requests(request_id INT, customer_id INT, category_id INT, request_ts TIMESTAMP)
quotes(quote_id INT, request_id INT, pro_id INT, quote_ts TIMESTAMP, price NUMERIC(10,2))
bookings(booking_id INT, request_id INT, pro_id INT, booking_ts TIMESTAMP, status TEXT)
pros(pro_id INT, region VARCHAR(10), activated_ts DATE)
categories(category_id INT, name TEXT)
Hints
- Join bookings to requests to bring in category_id, then aggregate completed bookings per (category_id, pro_id).
- Use DENSE_RANK() OVER (PARTITION BY category_id ORDER BY booking_count DESC) and filter to ranks <= 3.
Percent rank of quote prices within each request
Using the same schema, write a query that, for quotes created between 2025-08-31 and 2025-09-01 inclusive, computes each quote’s percent_rank of price within its request_id.
Lower price is better, and exact-price ties should be broken by lower quote_id. Use the percent_rank window function over each request_id partition with ORDER BY price ASC, quote_id ASC.
Return only quotes that are in the cheapest 20% for their request, i.e., those with percent_rank <= 0.2.
Output columns: request_id (INT), quote_id (INT), price (NUMERIC), percent_rank (DOUBLE PRECISION).
Tables
requests(request_id INT, customer_id INT, category_id INT, request_ts TIMESTAMP)
quotes(quote_id INT, request_id INT, pro_id INT, quote_ts TIMESTAMP, price NUMERIC(10,2))
bookings(booking_id INT, request_id INT, pro_id INT, booking_ts TIMESTAMP, status TEXT)
pros(pro_id INT, region VARCHAR(10), activated_ts DATE)
categories(category_id INT, name TEXT)
Hints
- Filter quotes to the specified date range first, then apply a window function partitioned by request_id.
- Use percent_rank() OVER (PARTITION BY request_id ORDER BY price, quote_id) and then filter on percent_rank <= 0.2.