← Thumbtack Interview Insights
The percentile_cont part wasn't the hard bit.
Start by clarifying the schema and defining the metric precisely: time-to-first-quote is the minimum quote timestamp per request minus the request timestamp, in minutes. Then join requests to quotes, filter to requests with at least one quote, and use a window function to compute the first quote time per request before aggregating to the category-week level and calculating the 90th percentile.
Pro tip: Mention that you would validate the percentile calculation by checking for edge cases like requests with multiple quotes in the same minute or missing timestamps, and consider using approximate percentile functions for scalability if the dataset is large.
Confirm the table structures, timestamp columns, and how categories are linked to requests. Define time-to-first-quote as the difference between the earliest quote timestamp and the request timestamp, in minutes.
Join requests to quotes on request_id, filter to requests that have at least one quote, and use a window function (e.g., MIN(quote_timestamp) OVER (PARTITION BY request_id)) to get the first quote time for each request.
For each request, compute the time difference in minutes between the first quote timestamp and the request timestamp. Ensure the result is non-negative and handle any nulls appropriately.
Extract the calendar week from the request timestamp (e.g., using DATE_TRUNC('week', request_timestamp)), group by category and week, and compute the 90th percentile of the time-to-first-quote values.
Check for anomalies, such as weeks with very few requests, and consider using approximate percentiles for large datasets. Present the results with clear labels and note any assumptions.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Classic ranking question but the join chain is a little annoying here because bookings doesn't have category_id directly.
Clarify the schema and definitions (completed bookings, categories, pros), then write a SQL query that filters bookings to the 30-day window ending 2025-09-01, aggregates completed bookings per pro per category, applies DENSE_RANK() partitioned by category ordered by count descending, and selects ranks <= 3. Validate edge cases like ties and missing data.
Pro tip: Mention that DENSE_RANK ensures ties receive the same rank without gaps, and explicitly state how you'd handle ties at the cutoff (e.g., if multiple pros tie for 3rd, all are included). Also, confirm whether 'top 3' means exactly 3 pros or up to 3 ranks, as this affects the output.
Ask about table structures (bookings, pros, categories), definitions of 'completed' and '30-day window', and whether the window is inclusive of both endpoints. Confirm that 'top 3' means top 3 ranks per category.
Filter bookings to completed status and booking dates between 2025-08-03 and 2025-09-01 (inclusive). Group by category and pro to count completed bookings.
Use DENSE_RANK() OVER (PARTITION BY category ORDER BY completed_bookings DESC) to rank pros within each category. Then select rows where rank <= 3.
Check for ties, nulls, and categories with fewer than 3 pros. Explain how DENSE_RANK handles ties and confirm that the output includes all tied pros if they fall within the top 3 ranks.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
percent_rank with lower-is-better means ordering ASC on price.
First, filter the quotes to the specified date range (2025-08-31 through 2025-09-01). Then, for each request, compute the percent_rank of price where lower price ranks better (i.e., ascending order), and finally select only those quotes whose percent_rank is <= 0.20, breaking ties by lowest quote_id.
Pro tip: Clarify whether the date range is inclusive and whether percent_rank should be computed per request or globally; in SQL, use PERCENT_RANK() OVER (PARTITION BY request_id ORDER BY price ASC, quote_id ASC) to handle ties consistently.
Select only quotes where the creation date is between 2025-08-31 and 2025-09-01, inclusive. Ensure the date column is properly formatted and consider time zones if applicable.
For each request, calculate the percent_rank of each quote's price in ascending order (lower price = better rank). Use a window function like PERCENT_RANK() OVER (PARTITION BY request_id ORDER BY price ASC, quote_id ASC) to handle ties by quote_id.
Keep only quotes where the computed percent_rank is less than or equal to 0.20. This selects the cheapest 20% within each request.
Ensure that ties in price are broken by the lowest quote_id, as specified. The ORDER BY clause in the window function should include quote_id as a tiebreaker.
Output the selected quotes, possibly including request_id, quote_id, price, and percent_rank, ordered as needed for clarity.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.