← Nextdoor Interview Insights

Nextdoor·Software Engineer·Technical Phone Screen·Senior

Senior
Apr 2026

Summary

Nextdoor analytics engineering interview that went deep on SQL ETL design, specifically building idempotent pipelines that compute KPIs from raw event tables into rollup structures. Pretty technical throughout, no fluff questions.

Questions Asked (4)

Q1

Write an idempotent SQL ETL query that computes user growth KPIs (new users and cumulative users) for a given target date and upserts the results into a rollup table.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

The idempotency part is what tripped me up at first.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and requirements, then design a query that computes daily new users and cumulative users for the target date. Use an idempotent upsert (e.g., INSERT ... ON CONFLICT DO UPDATE) to ensure re-runs don't duplicate data, and explain how you handle late-arriving data and incremental updates.

Pro tip: Mention that you'd use a MERGE or ON CONFLICT clause with a unique key on the date, and that you'd compute cumulative users by summing historical new users up to the target date—this shows you understand both idempotency and efficient rollup maintenance.

1. Clarify requirements and schema

Ask about the source table structure (e.g., user events with timestamps), the rollup table schema (date, new_users, cumulative_users), and the definition of 'new user' (e.g., first activity date). Confirm the target date and whether late-arriving data should be handled.

2. Compute new users for target date

Write a subquery that counts distinct users whose first activity date equals the target date. If using a users table with a created_at column, simply count users created on that date.

3. Compute cumulative users up to target date

Calculate cumulative users as the sum of new users from the beginning up to and including the target date. This can be done by summing the new_users column from the rollup table for all prior dates plus the new users for the target date, or by counting distinct users with first activity date <= target date.

4. Upsert into rollup table idempotently

Use an INSERT ... ON CONFLICT (date) DO UPDATE statement to insert or update the row for the target date. Ensure the unique constraint on the date column exists to prevent duplicates.

5. Discuss trade-offs and edge cases

Explain how you'd handle late-arriving data (e.g., recomputing affected dates), performance considerations (indexes, incremental vs full refresh), and how to ensure idempotency when re-running for the same date.

Key Points to Mention

  • Idempotency via unique key on date and upsert (ON CONFLICT DO UPDATE or MERGE)
  • Definition of 'new user' (e.g., first activity date) and how to compute it efficiently
  • Cumulative users calculation: sum of new users up to target date or distinct count with first activity <= target date
  • Handling late-arriving data: recompute affected dates or use incremental updates
  • Performance considerations: indexing on date and user_id, partitioning, and avoiding full table scans
  • Trade-offs between full refresh and incremental rollup maintenance

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

Q2

Write an idempotent SQL ETL query to compute DAU (distinct active users on a target date) from the sessions table and load it into the rollup table.

Product Analytics & MetricsData Modeling
Author's notes

Easiest of the four honestly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and idempotency requirements, then write a query that computes DAU for the target date and upserts it into the rollup table using a unique key (e.g., date). Use a MERGE or INSERT ... ON CONFLICT statement to ensure re-running the query doesn't create duplicates or errors.

Pro tip: Mention that idempotency is best achieved by making the rollup table's primary key the date column and using an upsert, so reruns overwrite the same row. Also, consider using a transaction to ensure atomicity.

1. Clarify requirements and schema

Ask about the sessions table columns (user_id, session_date, etc.), the rollup table structure, and the exact definition of 'active user' (e.g., any session on that date). Confirm the target date and idempotency expectations.

2. Compute DAU for the target date

Write a SELECT statement that counts distinct user_id from sessions where the session date matches the target date. Use a subquery or CTE to isolate the aggregation.

3. Design idempotent load

Use an upsert (MERGE or INSERT ... ON CONFLICT) to insert the computed DAU into the rollup table, keyed by date. This ensures reruns update the existing row instead of duplicating it.

4. Wrap in transaction and handle edge cases

Enclose the operation in a transaction for atomicity. Consider handling cases where no sessions exist (DAU = 0) and ensure the query is safe to run multiple times.

5. Validate and explain

Walk through the query, explaining how it achieves idempotency and correctness. Mention potential performance considerations (e.g., indexing on session_date).

Key Points to Mention

  • Use COUNT(DISTINCT user_id) to compute DAU.
  • Idempotency via upsert (MERGE or INSERT ... ON CONFLICT) on the date key.
  • Ensure the rollup table has a unique constraint on the date column.
  • Consider timezone or date boundary definitions for 'active'.
  • Use a transaction to guarantee atomicity.
  • Handle the case of zero active users (insert 0 or skip).

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

Q3

Write an idempotent SQL ETL query for photo activity KPIs: photos uploaded, likes received, and comments received on a target date, loading results into the rollup table.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

Three separate aggregations that need to join back to photos for the likes and comments counts since you want likes/comments on photos uploaded on :ds vs likes/comments that occurred on :ds.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and business definitions (e.g., what counts as a photo upload, like, or comment, and how to handle late-arriving data). Then design an idempotent query using a MERGE or INSERT ... ON CONFLICT pattern that upserts daily aggregates into the rollup table, ensuring re-runs don't duplicate data. Finally, discuss trade-offs like performance, backfill strategy, and data freshness.

Pro tip: Mention that idempotency often requires a unique key (e.g., date + metric) and that you should use a transaction or atomic upsert to avoid partial writes. Also, consider partitioning the rollup table by date for efficient backfills and queries.

1. Clarify requirements and schema

Ask about the source tables (e.g., photos, likes, comments) and the rollup table schema. Confirm definitions: does 'photos uploaded' count distinct photos or upload events? Are likes/comments counted on the photo's creation date or the like/comment date?

2. Design the aggregation query

Write a query that aggregates the three metrics for the target date. Use LEFT JOINs or UNION ALL to combine counts, ensuring all metrics are captured even if one is zero. Filter by the target date using appropriate timestamp columns.

3. Make it idempotent

Use an upsert pattern (e.g., MERGE in SQL Server/Oracle, INSERT ... ON CONFLICT in PostgreSQL, or INSERT ... ON DUPLICATE KEY UPDATE in MySQL) to insert or update the rollup row for the target date. Ensure the rollup table has a unique constraint on the date (and any other dimensions).

4. Handle edge cases and performance

Consider late-arriving data: if the ETL runs multiple times, it should reflect the latest data. Use a transaction to ensure atomicity. For performance, filter on partitioned columns and avoid full table scans.

5. Discuss trade-offs and alternatives

Mention trade-offs: MERGE can be slow on large datasets; consider staging tables or incremental updates. Also, discuss whether to store daily snapshots or cumulative totals, and how to handle backfills.

Key Points to Mention

  • Idempotency via unique key (date) and upsert (MERGE/ON CONFLICT)
  • Correct aggregation logic: counting distinct photos, likes, comments with proper date filtering
  • Handling late-arriving data and re-runs without duplication
  • Performance considerations: partitioning, indexing, and avoiding full scans
  • Transaction atomicity to prevent partial writes
  • Schema design of rollup table: date as primary key, metric columns, and possibly dimensions like user or photo category

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

Q4

Write an idempotent SQL ETL query to compute follower distribution stats as of end of a target date: average followers, median, 90th percentile, and percent of users with at least one follower.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

This one took the most time.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the schema and define 'as of end of target date' using a snapshot or event table. Write a single SQL query that aggregates follower counts per user, then computes the required statistics using window functions or percentile functions, ensuring idempotency by filtering to the target date and avoiding mutable state.

Pro tip: Mention that idempotency is achieved by making the query purely deterministic and re-runnable, e.g., by using a fixed date parameter and avoiding time-dependent functions like NOW(). Also, consider performance by pre-aggregating follower counts if the table is large.

1. Clarify requirements and schema

Ask about the table structure (e.g., user_follows with follower_id, followee_id, created_at) and define 'end of target date' as a specific timestamp (e.g., 23:59:59). Confirm whether to include users with zero followers.

2. Compute follower counts per user

Write a subquery or CTE that counts followers for each user as of the target date, filtering follows where created_at <= target_date. Use a LEFT JOIN from users to follows to include users with zero followers.

3. Calculate aggregate statistics

Use AVG, PERCENTILE_CONT (or APPROX_PERCENTILE) for median and 90th percentile, and compute the percentage of users with at least one follower. Ensure the query is idempotent by parameterizing the date and avoiding non-deterministic functions.

4. Optimize and validate

Consider indexing on created_at and followee_id, and discuss trade-offs between exact and approximate percentiles. Validate results with edge cases (e.g., no followers, all users have followers).

Key Points to Mention

  • Idempotency: query must produce same results when re-run, so use a fixed date parameter and avoid NOW() or CURRENT_DATE.
  • Definition of 'as of end of target date': use a timestamp like 'YYYY-MM-DD 23:59:59' and filter follows with created_at <= that timestamp.
  • Handling users with zero followers: use LEFT JOIN from users to follows to include them in the distribution.
  • Percentile calculation: use PERCENTILE_CONT(0.5) and PERCENTILE_CONT(0.9) for median and 90th percentile, or APPROX_PERCENTILE for large datasets.
  • Percentage with at least one follower: COUNT(DISTINCT user_id) with follower_count > 0 divided by total users.
  • Performance considerations: pre-aggregate follower counts if possible, and index on followee_id and created_at.

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