← Snap Interview Insights

Snap·Backend Engineer·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

SQL-heavy technical screen for a backend role at Snapchat. The interviewer walked through a file-access data model with several tables and had me write queries on the spot, no running them, just reasoning out loud. Felt more like a data modeling conversation than a pure coding test.

Questions Asked (2)

Q1

Given a multi-table schema (users, files, groups, ACLs), write a SQL query that joins two or three of those tables to answer a specific access-related question, and walk through your reasoning without executing it.

Data ModelingTechnical Trade-offs
Author's notes

The verbal framing threw me a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the exact access question (e.g., 'Which users can access file X?') and the schema relationships, then write a clean SQL query joining the necessary tables (e.g., users, files, ACLs) using appropriate join types. Walk through your reasoning step-by-step, explaining each join condition, filter, and how the query answers the question, while noting any assumptions or trade-offs.

Pro tip: Explicitly state your assumptions about the schema (e.g., ACLs map users/groups to files with permissions) and mention how you'd handle edge cases like group membership or permission inheritance, showing you think beyond the basic query.

1. Clarify the access question and schema

Restate the specific access question (e.g., 'Which users have read access to file X?') and confirm the table structures and relationships (e.g., users.id, files.id, acls.user_id, acls.file_id, acls.permission, groups.id, group_members).

2. Identify required tables and join paths

Determine which tables are needed (e.g., users, files, acls) and how they connect (e.g., acls.user_id = users.id, acls.file_id = files.id). If groups are involved, include group_members and groups.

3. Write the SQL query with clear joins and filters

Construct the query using explicit JOIN syntax (INNER, LEFT) and WHERE clauses to filter by the specific file and permission. Use table aliases for readability.

4. Walk through the query logic and reasoning

Explain each part: why you chose certain joins, how filters narrow results, and how the query answers the access question. Mention any assumptions (e.g., direct user ACLs only, no group inheritance).

5. Discuss trade-offs and edge cases

Address performance considerations (indexes on foreign keys), alternative approaches (e.g., using EXISTS), and edge cases like group-based permissions, permission hierarchies, or users with no ACLs.

Key Points to Mention

  • Schema relationships and foreign keys (e.g., acls.user_id references users.id, acls.file_id references files.id)
  • Join types (INNER vs LEFT) and their impact on results (e.g., LEFT JOIN to include users without ACLs)
  • Filtering by specific file and permission (e.g., WHERE files.name = 'X' AND acls.permission = 'read')
  • Handling group-based access (joining through group_members and groups if needed)
  • Performance considerations: indexing on join columns, avoiding SELECT *, and query execution plan
  • Edge cases: users with multiple permissions, permission inheritance, and NULL handling

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

Q2

For the same schema, write a query using GROUP BY and aggregation, and explain how deduplication affects your result.

Data ModelingAlgorithms & Data Structures
Author's notes

Felt more comfortable here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, restate the schema and clarify the business question the query answers. Then write a GROUP BY query with appropriate aggregations, and explicitly discuss how duplicate rows (e.g., from joins or event retries) can inflate counts and how to deduplicate before aggregation.

Pro tip: Mention that deduplication should happen before aggregation, and consider using a subquery or CTE with DISTINCT or ROW_NUMBER() to ensure accurate results. Also, note that deduplication can change the grain of the data, so verify that the aggregation still aligns with the business question.

1. Clarify schema and goal

Restate the schema and identify the entity to aggregate (e.g., users, events) and the metric (e.g., count, sum). Confirm the grain of the result.

2. Write baseline GROUP BY query

Write a simple query with GROUP BY and aggregation functions (e.g., COUNT, SUM) without deduplication. Explain what it computes.

3. Identify deduplication need

Explain sources of duplicates (e.g., joins, multiple events per user) and how they affect the aggregation (e.g., overcounting).

4. Apply deduplication

Show how to deduplicate using DISTINCT, GROUP BY, or window functions (e.g., ROW_NUMBER() OVER (PARTITION BY ...)) before aggregation.

5. Compare results and discuss trade-offs

Contrast the deduplicated result with the baseline, and discuss performance implications and correctness.

Key Points to Mention

  • GROUP BY clause and aggregation functions (COUNT, SUM, AVG, etc.)
  • Sources of duplicates: joins, event retries, multiple records per entity
  • Deduplication techniques: DISTINCT, GROUP BY, ROW_NUMBER() with PARTITION BY
  • Impact of deduplication on aggregation results (e.g., overcounting vs. accurate counts)
  • Performance considerations: deduplication can be expensive, so use appropriate indexes or pre-aggregation
  • Importance of aligning the query grain with the business question

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