Thumbtack·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Thumbtack data scientist interview with a pretty gnarly SQL question involving time zones, window functions, and calendar spine generation. One question, but it had enough moving parts to feel like three.

Questions Asked (1)

Write a single PostgreSQL query (no temp tables, CTEs only) that produces one row per calendar week in a given date range, showing the week's transaction total and a 3-week rolling sum. Weeks must be defined in America/Los_Angeles local time (Monday start), the calendar must include weeks with zero activity, and the rolling sum must treat missing weeks as zero.

Data ModelingTechnical Trade-offsProduct Analytics & Metrics

Author's notes

This one had a lot of layers and I underestimated the timezone piece at first.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by generating a complete series of weeks covering the date range using generate_series, then left join aggregated transaction data to include zero-activity weeks. Compute the rolling sum with a window function over the ordered weeks, ensuring missing weeks are treated as zero. Handle time zone conversion to America/Los_Angeles and week truncation to Monday carefully.

Pro tip: Explicitly state that you're using generate_series to build the calendar and that you'll convert timestamps to local time before truncating to week, because time zone handling is a common pitfall. Also mention that you'd validate the query against edge cases like DST transitions and partial weeks.

1. Generate the calendar of weeks

Use generate_series to produce a series of week start dates (Mondays) covering the entire date range, ensuring no gaps. This forms the base calendar.

2. Aggregate transactions by week

Convert transaction timestamps to America/Los_Angeles, truncate to week (Monday start), and sum amounts per week. This yields actual weekly totals.

3. Join calendar with aggregated data

Left join the generated weeks to the aggregated weekly totals, replacing NULLs with 0 to include zero-activity weeks.

4. Compute rolling 3-week sum

Use a window function (SUM with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) over the ordered weeks to calculate the rolling sum, treating missing weeks as zero.

5. Finalize and order output

Select the week start date, weekly total, and rolling sum, ordered chronologically. Ensure the query is a single statement using CTEs for clarity.

Key Points to Mention

Time zone conversion: use AT TIME ZONE 'America/Los_Angeles' to get local timestamps before truncating to week.
Week truncation: use DATE_TRUNC('week', ...) which starts on Monday in PostgreSQL, aligning with the requirement.
Calendar generation: generate_series with an interval of '1 week' to create a complete series of week starts.
Zero-filling: LEFT JOIN and COALESCE to replace NULL sums with 0 for weeks with no transactions.
Rolling sum: window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to include the current and two prior weeks.
Single query with CTEs: structure the solution using WITH clauses for readability and to avoid temp tables.

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