Coding questions · Thumbtack

Write complex joins and window functions

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.

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.

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).

Table `requests`

column	type	constraints
request_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
category_id	INT	NOT NULL
request_ts	TIMESTAMP	NOT NULL

Sample data:

category_id	customer_id	request_id	request_ts
10	101	1	2025-08-18 09:00:00
10	102	2	2025-08-24 10:30:00
11	103	3	2025-08-31 14:00:00
10	104	4	2025-09-01 08:15:00
12	105	5	2025-08-20 16:45:00
11	106	6	2025-08-19 12:05:00

Table `quotes`

column	type	constraints
quote_id	INT	PRIMARY KEY, NOT NULL
request_id	INT	NOT NULL
pro_id	INT	NOT NULL
quote_ts	TIMESTAMP	NOT NULL
price	NUMERIC(10,2)	NOT NULL

Sample data:

price	pro_id	quote_id	quote_ts	request_id
120	201	11	2025-08-18 09:10:00	1
115	202	12	2025-08-18 09:12:00	1
200	201	13	2025-08-24 10:40:00	2
150	203	14	2025-08-31 14:30:00	3
140	204	15	2025-08-31 14:45:00	3
180	202	16	2025-09-01 08:25:00	4
220	205	17	2025-08-20 18:00:00	5
210	206	18	2025-08-21 09:00:00	5

Table `bookings`

column	type	constraints
booking_id	INT	PRIMARY KEY, NOT NULL
request_id	INT	NOT NULL
pro_id	INT	NOT NULL
booking_ts	TIMESTAMP	NOT NULL
status	TEXT	NOT NULL

Sample data:

booking_id	booking_ts	pro_id	request_id	status
1001	2025-08-18 09:20:00	202	1	completed
1002	2025-08-24 11:00:00	201	2	canceled
1003	2025-08-31 15:00:00	204	3	completed
1004	2025-08-21 10:00:00	206	5	completed
1005	2025-09-01 08:40:00	202	4	pending

Table `pros`

column	type	constraints
pro_id	INT	PRIMARY KEY, NOT NULL
region	VARCHAR(10)	NOT NULL
activated_ts	DATE	NOT NULL

Sample data:

activated_ts	pro_id	region
2025-01-15	201	SF
2025-02-12	202	SF
2024-12-01	203	LA
2025-03-05	204	LA
2025-07-20	205	SF
2025-06-30	206	SF

Table `categories`

column	type	constraints
category_id	INT	PRIMARY KEY, NOT NULL
name	TEXT	NOT NULL

Sample data:

category_id	name
10	Plumbing
11	House Cleaning
12	Painting

Expected output

One row per (week_start_date, category_id) with the 90th percentile of minutes to first quote among requests in that week and category that received at least one quote.

category_id	p90_minutes	week_start_date
10	10	2025-08-18
12	75	2025-08-18
11	30	2025-08-25
10	10	2025-09-01

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).

Table `requests`

column	type	constraints
request_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
category_id	INT	NOT NULL
request_ts	TIMESTAMP	NOT NULL

Sample data:

category_id	customer_id	request_id	request_ts
10	101	1	2025-08-18 09:00:00
10	102	2	2025-08-24 10:30:00
11	103	3	2025-08-31 14:00:00
10	104	4	2025-09-01 08:15:00
12	105	5	2025-08-20 16:45:00
11	106	6	2025-08-19 12:05:00

Table `quotes`

column	type	constraints
quote_id	INT	PRIMARY KEY, NOT NULL
request_id	INT	NOT NULL
pro_id	INT	NOT NULL
quote_ts	TIMESTAMP	NOT NULL
price	NUMERIC(10,2)	NOT NULL

Sample data:

price	pro_id	quote_id	quote_ts	request_id
120	201	11	2025-08-18 09:10:00	1
115	202	12	2025-08-18 09:12:00	1
200	201	13	2025-08-24 10:40:00	2
150	203	14	2025-08-31 14:30:00	3
140	204	15	2025-08-31 14:45:00	3
180	202	16	2025-09-01 08:25:00	4
220	205	17	2025-08-20 18:00:00	5
210	206	18	2025-08-21 09:00:00	5

Table `bookings`

column	type	constraints
booking_id	INT	PRIMARY KEY, NOT NULL
request_id	INT	NOT NULL
pro_id	INT	NOT NULL
booking_ts	TIMESTAMP	NOT NULL
status	TEXT	NOT NULL

Sample data:

booking_id	booking_ts	pro_id	request_id	status
1001	2025-08-18 09:20:00	202	1	completed
1002	2025-08-24 11:00:00	201	2	canceled
1003	2025-08-31 15:00:00	204	3	completed
1004	2025-08-21 10:00:00	206	5	completed
1005	2025-09-01 08:40:00	202	4	pending

Table `pros`

column	type	constraints
pro_id	INT	PRIMARY KEY, NOT NULL
region	VARCHAR(10)	NOT NULL
activated_ts	DATE	NOT NULL

Sample data:

activated_ts	pro_id	region
2025-01-15	201	SF
2025-02-12	202	SF
2024-12-01	203	LA
2025-03-05	204	LA
2025-07-20	205	SF
2025-06-30	206	SF

Table `categories`

column	type	constraints
category_id	INT	PRIMARY KEY, NOT NULL
name	TEXT	NOT NULL

Sample data:

category_id	name
10	Plumbing
11	House Cleaning
12	Painting

Expected output

Top 3 pros per category (by completed booking count) in the given 2025-08-03 through 2025-09-01 window, with dense_rank so ties share the same rank.

booking_count	category_id	dense_rank	pro_id
1	10	1	202
1	11	1	204
1	12	1	206

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).

Table `requests`

column	type	constraints
request_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
category_id	INT	NOT NULL
request_ts	TIMESTAMP	NOT NULL

Sample data:

category_id	customer_id	request_id	request_ts
10	101	1	2025-08-18 09:00:00
10	102	2	2025-08-24 10:30:00
11	103	3	2025-08-31 14:00:00
10	104	4	2025-09-01 08:15:00
12	105	5	2025-08-20 16:45:00
11	106	6	2025-08-19 12:05:00

Table `quotes`

column	type	constraints
quote_id	INT	PRIMARY KEY, NOT NULL
request_id	INT	NOT NULL
pro_id	INT	NOT NULL
quote_ts	TIMESTAMP	NOT NULL
price	NUMERIC(10,2)	NOT NULL

Sample data:

price	pro_id	quote_id	quote_ts	request_id
120	201	11	2025-08-18 09:10:00	1
115	202	12	2025-08-18 09:12:00	1
200	201	13	2025-08-24 10:40:00	2
150	203	14	2025-08-31 14:30:00	3
140	204	15	2025-08-31 14:45:00	3
180	202	16	2025-09-01 08:25:00	4
220	205	17	2025-08-20 18:00:00	5
210	206	18	2025-08-21 09:00:00	5

Table `bookings`

column	type	constraints
booking_id	INT	PRIMARY KEY, NOT NULL
request_id	INT	NOT NULL
pro_id	INT	NOT NULL
booking_ts	TIMESTAMP	NOT NULL
status	TEXT	NOT NULL

Sample data:

booking_id	booking_ts	pro_id	request_id	status
1001	2025-08-18 09:20:00	202	1	completed
1002	2025-08-24 11:00:00	201	2	canceled
1003	2025-08-31 15:00:00	204	3	completed
1004	2025-08-21 10:00:00	206	5	completed
1005	2025-09-01 08:40:00	202	4	pending

Table `pros`

column	type	constraints
pro_id	INT	PRIMARY KEY, NOT NULL
region	VARCHAR(10)	NOT NULL
activated_ts	DATE	NOT NULL

Sample data:

activated_ts	pro_id	region
2025-01-15	201	SF
2025-02-12	202	SF
2024-12-01	203	LA
2025-03-05	204	LA
2025-07-20	205	SF
2025-06-30	206	SF

Table `categories`

column	type	constraints
category_id	INT	PRIMARY KEY, NOT NULL
name	TEXT	NOT NULL

Sample data:

category_id	name
10	Plumbing
11	House Cleaning
12	Painting

Expected output

Quotes on 2025-08-31 and 2025-09-01 that fall in the cheapest 20% within their request, with percent_rank computed by price (then quote_id) ascending.

percent_rank	price	quote_id	request_id
0	140	15	3
0	180	16	4

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.

Weekly 90th percentile time-to-first-quote by category

Table requests

Table quotes

Table bookings

Table pros

Table categories

Expected output

Hints

Top 3 pros per category by completed bookings with dense_rank

Table requests

Table quotes

Table bookings

Table pros

Table categories

Expected output

Hints

Percent rank of quote prices within each request

Table requests

Table quotes

Table bookings

Table pros

Table categories

Expected output

Hints

Table `requests`

Table `quotes`

Table `bookings`

Table `pros`

Table `categories`

Table `requests`

Table `quotes`

Table `bookings`

Table `pros`

Table `categories`

Table `requests`

Table `quotes`

Table `bookings`

Table `pros`

Table `categories`