← Nextdoor Interview Insights

Nextdoor·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

SQL-heavy technical screen for an analytics engineer role at Nextdoor. Three queries back to back, then a discussion about indexing. Pretty standard stuff but the tie-breaking and index conversation took longer than I expected.

Questions Asked (4)

Q1

Given a users table with a signup timestamp and a follows table, write a SQL query to return the total number of users.

Product Analytics & Metrics
Author's notes

Easiest of the three, just COUNT(*) on users.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the question: confirm whether 'total number of users' means all users or only those who have signed up. Then write a simple SQL query using COUNT(*) on the users table. If the question implies active users, consider using the follows table to count distinct users who have at least one follow.

Pro tip: Always clarify ambiguous requirements before writing SQL. Mention that you would check with the interviewer whether they want a simple count or a more complex metric like monthly active users, showing you think about business context.

1. Clarify the requirement

Ask the interviewer to define 'total number of users' – does it mean all registered users, or users who have performed a specific action (e.g., followed someone)?

2. Identify the relevant table(s)

If counting all users, use the users table. If counting users who follow someone, use the follows table and count distinct user IDs.

3. Write the SQL query

For all users: SELECT COUNT(*) FROM users; For users who follow: SELECT COUNT(DISTINCT follower_id) FROM follows;

4. Consider edge cases and performance

Mention that COUNT(*) is efficient, but COUNT(DISTINCT) can be slower on large tables. Also consider if NULLs or duplicates affect the count.

5. Explain the result and context

Discuss how this metric might be used (e.g., for growth tracking) and whether additional filters (e.g., date range) are needed.

Key Points to Mention

  • Difference between COUNT(*) and COUNT(column) and handling of NULLs
  • Using DISTINCT when counting from the follows table to avoid duplicates
  • Importance of clarifying business definitions (e.g., active vs. registered users)
  • Performance implications of COUNT(DISTINCT) on large datasets
  • Potential need for date filtering based on signup timestamp
  • How the metric aligns with product analytics goals (e.g., user growth)

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

Q2

Write a SQL query to find the calendar month in which the most users signed up, returning the month and the count.

Product Analytics & MetricsData Modeling
Author's notes

This is where I fumbled a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and assumptions (e.g., table name, date column, user identifier). Then write a query that groups by the calendar month of signup, counts distinct users, orders by count descending, and limits to 1. Consider edge cases like ties and time zones.

Pro tip: Mention that you'd use DATE_TRUNC('month', signup_date) to get the calendar month, and if there's a tie, you might need to decide how to handle it (e.g., return all tied months or pick the earliest). Also, clarify whether 'users' means distinct user IDs or all signup events.

1. Clarify requirements and schema

Ask about the table structure, column names, and what defines a 'user signup' (e.g., user_id, signup_date). Confirm if the count should be distinct users or total signups.

2. Extract calendar month

Use a date function like DATE_TRUNC('month', signup_date) or EXTRACT(YEAR FROM ...) and EXTRACT(MONTH FROM ...) to group by calendar month.

3. Aggregate and count

Group by the extracted month and count distinct user IDs (or count all rows if each signup is unique).

4. Order and limit

Order the results by count descending and limit to 1 to get the month with the most signups.

5. Handle ties and edge cases

Discuss how to handle ties (e.g., using RANK() or returning all tied months) and consider time zone implications for signup dates.

Key Points to Mention

  • Use of DATE_TRUNC or EXTRACT to get calendar month
  • Counting distinct users vs. total signups
  • Grouping by month and ordering by count
  • Handling ties with window functions like RANK() or DENSE_RANK()
  • Time zone considerations for signup timestamps
  • Performance implications of grouping on a large dataset

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

Q3

Write a SQL query to find the user with the most followers, returning the user_id and follower count. How would you handle ties?

Product Analytics & MetricsData Modeling
Author's notes

Counting followee_id groups in the follows table is the easy part.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and edge cases (e.g., ties, nulls). Then write a query using a subquery or window function to find the maximum follower count, and finally select users matching that count, explaining how you handle ties (e.g., return all tied users or use a tiebreaker).

Pro tip: Mention that window functions like RANK() or DENSE_RANK() can elegantly handle ties, and discuss the trade-offs between returning all tied users versus a single user based on business needs.

1. Clarify requirements and schema

Ask about the table structure (e.g., users, follows) and whether ties should return multiple users or a single one. Confirm if follower count is precomputed or needs aggregation.

2. Choose an approach

Decide between using a subquery with MAX() or a window function like RANK(). Consider performance and readability.

3. Write the query

Construct the SQL, ensuring correct grouping and ordering. For ties, use RANK() or DENSE_RANK() and filter for rank = 1.

4. Explain tie handling

Describe how your query returns all tied users (if using RANK) or how you might select one (e.g., by earliest user_id).

5. Discuss edge cases and optimizations

Mention handling of users with zero followers, nulls, and indexing strategies for performance.

Key Points to Mention

  • Use of GROUP BY and COUNT() to compute follower counts per user.
  • Subquery with MAX() to find the highest follower count.
  • Window functions (RANK, DENSE_RANK) for tie handling.
  • Difference between RANK and DENSE_RANK in tie scenarios.
  • Business decision: return all tied users vs. a single user.
  • Indexing on foreign keys to optimize join and aggregation performance.

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

Q4

What indexes would speed up each of these three queries, and why?

System DesignTechnical Trade-offs
Author's notes

This went longer than I thought.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

For each query, analyze the WHERE, JOIN, and ORDER BY clauses to determine which columns are used for filtering, joining, and sorting. Then propose indexes that cover those columns in the optimal order, considering selectivity and the possibility of composite or covering indexes. Explain how each index improves performance by reducing the number of rows scanned or avoiding sorting.

Pro tip: Always consider the trade-offs: indexes speed up reads but slow down writes and consume storage. Mention that you'd verify with EXPLAIN and monitor index usage to avoid over-indexing.

1. Analyze the query

Identify the filtering conditions (WHERE), join conditions, and sorting/grouping (ORDER BY, GROUP BY) in the query.

2. Determine optimal index columns

Choose columns for the index based on equality conditions first, then range conditions, and finally sorting columns. Consider composite indexes for multiple conditions.

3. Consider index type and order

Decide between B-tree, hash, or other index types. For composite indexes, order columns by selectivity and query patterns.

4. Evaluate covering index possibility

Check if the index can include all columns needed by the query (including SELECT) to avoid table lookups.

5. Discuss trade-offs and verification

Mention the impact on write performance and storage, and how you would use EXPLAIN to confirm index usage.

Key Points to Mention

  • Selectivity of columns: high selectivity columns are better for indexing.
  • Composite index column order: equality before range, and most selective first.
  • Covering indexes: include all columns needed by the query to avoid lookups.
  • Index types: B-tree for range queries, hash for equality, etc.
  • Trade-offs: write overhead, storage, and maintenance.
  • Verification with EXPLAIN and monitoring index usage.

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