← Thumbtack Interview Insights

Thumbtack·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Got a SQL question for a Data Scientist role at Thumbtack that was more involved than I expected. Single query, no dynamic SQL, and they wanted you to verify your output against sample data too. Felt like a legit test of whether you actually understand window functions and conditional aggregation or just know the syntax.

Questions Asked (1)

Q1

Given a users table and a requests table, write a single PostgreSQL query (no dynamic SQL) that returns, for every calendar month present in requests, the counts and percentage shares of requests from NEW versus RETURNING users. A user is NEW in any month where that month equals their first-ever request month; otherwise they are RETURNING. Output should include month, new request count, returning request count, new share pct, and returning share pct, ordered by month ascending. Also verify your query against the provided sample data.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures
Author's notes

This one took me a minute to untangle.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, compute each user's first request month using a subquery or window function, then join this back to the requests table to classify each request as NEW or RETURNING. Finally, aggregate by month to get counts and percentage shares, and verify the results against the sample data.

Pro tip: Use a CTE to compute first request month and then join, which is more readable and avoids correlated subqueries. Also, consider edge cases like users with no requests or months with zero requests of one type.

1. Compute first request month per user

Use a subquery or window function to find the minimum request month for each user from the requests table.

2. Classify each request as NEW or RETURNING

Join the first request month back to the requests table and compare the request month to the first month; label as NEW if equal, else RETURNING.

3. Aggregate by month

Group by month and count the number of NEW and RETURNING requests, then compute percentage shares.

4. Order and format output

Order the results by month ascending and ensure the output columns are month, new_count, returning_count, new_share_pct, returning_share_pct.

5. Verify with sample data

Manually compute expected results for the provided sample data and compare with the query output to ensure correctness.

Key Points to Mention

  • Use of window functions or subqueries to compute first request month
  • Handling of users with multiple requests in the same month
  • Calculation of percentage shares using window functions or subqueries
  • Ensuring all months present in requests are included, even if one category has zero count
  • Ordering by month ascending
  • Verification against sample data to catch off-by-one errors or misclassifications

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.