Snapchat·Backend Engineer·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL-heavy technical screen for a backend role at Snapchat. The interviewer gave me a multi-table schema upfront and then fired off a few verbal requests one by one, expecting me to write queries on the spot and talk through the logic without actually running anything.

Questions Asked (3)

Given a schema with users, files, groups, and ACL tables from a file-access system, write a SQL query that joins two or more of these tables to answer a specific access-related question, and walk through your reasoning without executing it.

Data ModelingTechnical Trade-offs

Author's notes

The part that tripped me up was deciding between INNER and LEFT JOIN before I fully understood what NULLs in the ACL table would mean for the result.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the access question and the schema relationships (users, groups, files, ACLs). Then write a SQL query that joins the necessary tables, explaining each join condition and filter. Walk through the logic step-by-step, highlighting assumptions and potential edge cases.

Pro tip: Before writing SQL, restate the access question in plain English and confirm the grain of each table. This shows you think about data semantics, not just syntax, and prevents incorrect joins.

1. Clarify the access question

Restate the question in your own words and identify what entities are involved (e.g., which user, which file, what permission). Confirm whether it's about direct access, group-based access, or both.

2. Map schema relationships

Describe how the tables relate: users to groups (membership), groups to ACLs, users to ACLs, files to ACLs. Identify primary and foreign keys and the grain of each table.

3. Design the join path

Choose the minimal set of joins needed to answer the question. Explain why you include or exclude certain tables (e.g., join users to groups to ACLs to files for group-based access).

4. Write the SQL query

Construct the query with explicit JOINs, WHERE conditions for the specific user/file, and any necessary DISTINCT or aggregation. Use clear aliases and comment on key parts.

5. Walk through and validate

Explain the query's logic step-by-step, including filter order and potential NULLs. Discuss edge cases like multiple permissions, missing group memberships, or duplicate rows.

Key Points to Mention

Table grains and cardinality (e.g., one user can belong to many groups, one file can have many ACL entries).
Join types (INNER vs LEFT) and their impact on results, especially for optional group memberships.
Handling permissions: whether ACLs store allow/deny and how to combine them (e.g., OR conditions, precedence).
Performance considerations: indexing on foreign keys, avoiding unnecessary joins, and using EXISTS vs JOIN for large datasets.
Edge cases: users with no groups, files with no ACLs, duplicate ACL entries, and permission inheritance.
Security implications: ensuring the query doesn't leak access or miss deny rules.

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

Write a GROUP BY query with aggregation across multiple tables from the same schema, and explain how you'd handle deduplication if a join produces duplicate rows.

Data ModelingAlgorithms & Data Structures

Author's notes

Knew the mechanics fine but stumbled explaining why a LEFT JOIN before a GROUP BY could inflate counts.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the specific aggregation needed, then write a clear SQL query that joins the necessary tables and uses GROUP BY with aggregate functions. Finally, explain how you would detect and handle duplicate rows caused by the join, such as using DISTINCT or pre-aggregating before joining.

Pro tip: Mention that you would first check the cardinality of the join to understand duplication, and consider using a subquery or CTE to deduplicate before aggregating to avoid incorrect results and improve performance.

1. Clarify requirements and schema

Ask clarifying questions about the tables involved, the desired aggregation, and the join conditions. Confirm the expected output and any constraints.

2. Write the base query

Construct a SQL query that joins the tables on the appropriate keys, groups by the desired dimensions, and applies aggregate functions like SUM, COUNT, or AVG.

3. Identify and handle duplicates

Explain how duplicates can arise from one-to-many or many-to-many joins. Discuss options like using DISTINCT, GROUP BY with subqueries, or window functions to deduplicate before aggregation.

4. Optimize and validate

Suggest ways to optimize the query, such as indexing join keys or using CTEs. Mention validating results by comparing with expected counts or using EXPLAIN to check the plan.

Key Points to Mention

Understanding of JOIN types and their impact on row multiplication
Use of GROUP BY with aggregate functions like SUM, COUNT, AVG
Deduplication techniques: DISTINCT, subqueries, CTEs, window functions (ROW_NUMBER)
Importance of checking cardinality and join keys to avoid duplicates
Performance considerations: indexing, query plan analysis, avoiding unnecessary joins
Real-world example from Snapchat's domain, such as aggregating user engagement metrics across tables

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

For a given verbal data request, explain how your SQL query maps back to the underlying data model and why the join type you chose is correct for that specific access pattern.

Data ModelingSystem Design

Author's notes

More conceptual than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by restating the verbal request in your own words to confirm the business question and required output. Then walk through the data model entities and relationships, explaining how the SQL query traverses them. Finally, justify the join type by linking it to the access pattern and expected result set (e.g., preserving all users vs. only matching rows).

Pro tip: Always clarify the grain of the result and whether unmatched rows should be preserved—this shows you think about data semantics, not just syntax. Mention that you validate join choices with row counts and null checks before finalizing.

1. Clarify the request and output grain

Restate the verbal request to confirm the business question, required dimensions, measures, and the expected grain of the result set.

2. Map entities and relationships

Identify the core tables in the data model, their primary/foreign keys, and the cardinality of relationships (1:1, 1:N, N:M) relevant to the request.

3. Translate to SQL and choose join types

Write the query, selecting join types (INNER, LEFT, etc.) based on whether you need to preserve unmatched rows and the access pattern (e.g., filtering, aggregation).

4. Validate with edge cases and performance

Check for fan-out, nulls, and cardinality issues; consider indexing and query plan to ensure the join is efficient for the access pattern.

5. Summarize and justify

Concisely explain why the chosen join type is correct for the specific access pattern and how it aligns with the data model.

Key Points to Mention

Cardinality of relationships (1:1, 1:N, N:M) and how it affects join results
Difference between INNER, LEFT, RIGHT, FULL OUTER joins and when to use each
Impact of join type on result set size and null handling
Access pattern: whether the query is for reporting, real-time lookup, or aggregation
Performance considerations: indexing, join order, and avoiding unnecessary joins
Data model concepts: normalization, foreign keys, and entity-relationship diagrams

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