Common Question Types — Mastering DBMS Interview… | OneNoughtOne

0/278

Database Management SystemsInterview Strategies

Mastering DBMS Interview Strategies

LevelAdvanced

Duration75 mins

TopicInterview Strategies

1 / 5

Common Question Types

Navigating the DBMS Interview Landscape

Technical interviews for roles involving databases—whether you're applying as a Software Engineer, Backend Developer, Data Engineer, DBA, or Solutions Architect—invariably probe your understanding of Database Management Systems. Unlike algorithm interviews that focus on narrow problem-solving patterns, DBMS interviews span a remarkably broad spectrum: from theoretical foundations and SQL proficiency to system design and real-world troubleshooting scenarios. Understanding what types of questions you'll encounter is the first step toward mastering these interviews. This knowledge transforms an overwhelming domain into a structured preparation plan, allowing you to allocate time efficiently and identify gaps in your expertise.

What You Will Master

By the end of this page, you will have a comprehensive taxonomy of DBMS interview question categories, understand what each category tests, see real examples from industry interviews, and know how different companies and roles emphasize different areas. This foundation enables strategic, targeted preparation.

The Question Type Spectrum

DBMS interview questions exist on a spectrum from purely theoretical to entirely practical, with significant variation based on company culture, role seniority, and team focus. Understanding this spectrum helps you calibrate expectations and preparation depth. The Four Major Categories: At the highest level, DBMS questions fall into four interconnected categories, each testing different competencies and increasingly valued as you advance in seniority:

DBMS Interview Question Categories Overview
Category	What It Tests	Typical Format	Seniority Focus
Conceptual/Theoretical	Foundational understanding of DBMS principles	Q&A, whiteboard explanations	All levels (heavier for junior)
SQL & Query Writing	Practical ability to write correct, efficient queries	Live coding, take-home, whiteboard	All levels (fundamental skill)
Design & Modeling	Schema design, normalization, system architecture	Whiteboard design sessions	Mid to Senior levels
Troubleshooting & Operations	Real-world problem diagnosis, performance tuning	Scenario-based discussions	Senior/Staff+ levels

Strategic Insight

Junior candidates face more conceptual and SQL questions—interviewers are validating foundational knowledge. Senior candidates encounter more design and troubleshooting scenarios—interviewers are testing judgment, experience, and the ability to navigate ambiguity. Staff+ and Principal-level interviews often blend all categories into complex, open-ended discussions.

Conceptual and Theoretical Questions

Conceptual questions probe your understanding of why databases work the way they do, not just how to use them. These questions reveal whether you've internalized foundational principles or merely memorized syntax and procedures. Why They Matter: Engineers who understand theory make better design decisions, debug problems faster, and communicate more effectively with peers. A candidate who can explain transaction isolation levels conceptually can reason about novel concurrency scenarios; one who only memorized definitions cannot.

Common Conceptual Question Areas

•ACID Properties — What are they? Why do they matter? Can you explain scenarios where each property prevents specific problems? How does eventual consistency differ?
•Normalization Theory — What are functional dependencies? Explain normal forms (1NF through 5NF). When is denormalization appropriate? What trade-offs exist?
•Transaction Management — What is a transaction? Explain isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable). What is a phantom read?
•Concurrency Control — How does locking work? Explain optimistic vs. pessimistic concurrency. What is MVCC (Multi-Version Concurrency Control)?
•Indexing Fundamentals — What is an index? Explain B-tree vs. B+-tree. When do indexes hurt performance? What is an index-only scan?
•Storage and Architecture — How does a database store data physically? What are pages, extents, tablespaces? How do buffer pools work?
•Query Processing — Explain the stages of query execution. What is a query plan? How does a cost-based optimizer work?
•CAP Theorem — Explain Consistency, Availability, and Partition Tolerance. Why can't you have all three? Give examples of CP vs. AP systems.
•Replication and Sharding — What is master-slave replication? What is sharding? How do you choose a shard key?

Example Interview Questions (Real Industry Examples):

Sample Conceptual Interview Questions
Question	What It Tests	Good Answer Includes
"Explain the difference between a clustered and non-clustered index."	Index architecture understanding	Storage differences, performance implications, when to use each, real examples
"What happens when a transaction violates Repeatable Read?"	Isolation level depth	Phantom read explanation, MVCC awareness, practical implications
"Why would you denormalize a schema?"	Pragmatic design thinking	Trade-offs (read speed vs. write complexity), specific use cases, how to mitigate downsides
"Explain the CAP theorem with a real-world example."	Distributed systems fundamentals	Clear definitions, real systems (Cassandra vs. PostgreSQL), partition scenarios
"How does ACID differ from BASE?"	Modern database paradigms	Definitions, appropriate use cases, eventual consistency mechanics

Depth Over Breadth

Interviewers often drill deeper after your initial answer. If you mention MVCC, expect follow-up questions about how it works, its garbage collection implications, and when it fails. Prepare to go at least two or three levels deep on any concept you mention.

SQL and Query Writing Questions

SQL proficiency is the most universally tested skill in DBMS interviews. Regardless of your role—backend engineer, data analyst, DBA, or platform architect—you will face queries to write, debug, or optimize. These questions range from straightforward SELECT statements to complex analytical queries involving multiple joins, subqueries, window functions, and CTEs (Common Table Expressions). Subcategories of SQL Questions:

SQL Question Subcategories

•Basic CRUD Operations — Write INSERT, UPDATE, DELETE, and SELECT statements. Handle NULL values correctly. Use appropriate WHERE clauses.
•Multi-Table Joins — INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, CROSS JOIN, self-joins. Know when each is appropriate and predict result set sizes.
•Aggregations and Grouping — GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX. Handle edge cases (empty groups, NULLs in aggregations).
•Subqueries and Derived Tables — Correlated vs. non-correlated subqueries. EXISTS vs. IN performance implications. Scalar subqueries.
•Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, running totals, moving averages. Partition and ordering.
•CTEs (Common Table Expressions) — WITH clause for readability, recursive CTEs for hierarchical data traversal.
•Set Operations — UNION, UNION ALL, INTERSECT, EXCEPT. When to use each and performance implications.
•Data Modification — Complex UPDATE statements with JOINs, INSERT...SELECT patterns, UPSERT (MERGE) operations.
•Query Optimization — Analyzing EXPLAIN plans, index usage, avoiding full table scans, query rewriting for performance.

Classic SQL Interview Patterns: Certain query patterns appear repeatedly across interviews. Mastering these patterns provides templates for solving novel variations:

Common SQL Interview Patterns
Pattern	Example Problem	Key Techniques
Top-N per Group	"Find the top 3 highest-paid employees per department"	Window functions (ROW_NUMBER), subqueries, LATERAL joins
Running Totals	"Calculate cumulative sales by date"	Window SUM with ordering, self-joins (older approach)
Gap Detection	"Find missing sequence numbers in orders"	Self-joins, LAG/LEAD, GENERATE_SERIES (PostgreSQL)
Consecutive Events	"Find users who logged in 3+ consecutive days"	Self-joins with date arithmetic, LAG/LEAD, gap-and-island technique
Duplicate Detection	"Find duplicate email addresses"	GROUP BY/HAVING COUNT > 1, window functions, self-joins
Hierarchical Queries	"Find all employees reporting to a manager (any level)"	Recursive CTEs, adjacency list traversal
Pivoting Data	"Convert rows to columns for monthly sales"	CASE/WHEN aggregation, PIVOT (SQL Server), CROSSTAB (PostgreSQL)
Second Highest	"Find the second highest salary"	LIMIT/OFFSET, DENSE_RANK window function, subquery with MAX

top_n_per_group.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Classic Pattern: Top-N Per Group
-- Problem: Find the top 3 highest-paid employees in each department
 
-- Approach 1: Window Function (Most Common, Most Readable)
WITH RankedEmployees AS (
    SELECT 
        employee_id,
        employee_name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) as salary_rank
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department_id,
    salary
FROM RankedEmployees
WHERE salary_rank <= 3
ORDER BY department_id, salary_rank;
 
-- Approach 2: Correlated Subquery (Works on older databases)
SELECT e1.*
FROM employees e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
      AND e2.salary > e1.salary
) < 3
ORDER BY e1.department_id, e1.salary DESC;

Common Pitfalls in SQL Interviews

Forgetting NULL handling (NULL in comparisons, aggregations, joins). 2) Confusing RANK vs DENSE_RANK vs ROW_NUMBER. 3) Missing edge cases (empty tables, duplicate values). 4) Writing syntactically correct but inefficient queries (nested subqueries where joins work). 5) Not considering index usage. Always articulate your assumptions and edge case handling.

Design and Modeling Questions

Design questions assess your ability to translate real-world requirements into effective database schemas and architectures. Unlike SQL questions that have "correct" answers, design questions are inherently open-ended—there are multiple valid approaches, and interviewers evaluate your reasoning process, trade-off analysis, and ability to iterate based on additional constraints. Two Major Subcategories:

Schema Design Questions

•Design the data model for Twitter/X
•Design a database for an e-commerce platform
•Model a ride-sharing application like Uber
•Design the schema for a booking system (hotels, flights)
•Create a data model for a messaging application
•Design tables for a content management system
•Model a multi-tenant SaaS application

System Architecture Questions

•How would you scale the database for 10x traffic?
•Design a caching strategy for read-heavy workloads
•How would you implement global replication?
•Design a sharding strategy for user data
•Architect a system for real-time analytics
•Design a database switching/migration strategy
•Plan for disaster recovery and high availability

What Interviewers Evaluate: Requirements Clarification — Do you ask clarifying questions before diving in? What's the scale (users, transactions/second)? Read-heavy or write-heavy? Consistency requirements? Logical Design — Entity identification, relationship modeling, attribute selection. Do you recognize many-to-many relationships? Handle polymorphism? Normalization Judgment — Do you apply normalization principles? More importantly, do you know when to denormalize for performance? Index Strategy — Can you identify which queries will run and what indexes they need? Do you consider composite indexes? Scaling Considerations — How does your design handle 10x, 100x growth? Where are the bottlenecks? Trade-off Articulation — Can you explain why you chose one approach over another? What did you sacrifice?

The Schema Design Framework

Follow this framework in design interviews: 1) Clarify requirements and constraints (scale, access patterns, consistency needs). 2) Identify core entities and relationships. 3) Draft initial schema (tables, columns, types). 4) Define primary keys and foreign keys. 5) Propose indexes based on query patterns. 6) Discuss scaling strategy (sharding, replication). 7) Address trade-offs and alternatives. Verbalizing this process demonstrates structured thinking.

Sample Design Question Evaluation Criteria
Criterion	Junior Expectation	Senior Expectation	Staff+ Expectation
Requirements Gathering	Accepts problem as stated	Asks clarifying questions	Challenges assumptions, identifies implicit requirements
Entity Modeling	Identifies obvious entities	Handles edge cases, polymorphism	Considers future evolution, extensibility
Normalization	Applies basic normal forms	Knows when to denormalize	Quantifies trade-offs with data estimates
Indexing	Suggests obvious indexes	Proposes composite indexes, covering indexes	Considers index maintenance cost, bloom filters
Scaling	Aware of scaling needs	Proposes sharding/replication	Designs for global distribution, considers CAP trade-offs

Troubleshooting and Operational Questions

Experienced engineers face scenario-based questions that simulate real production problems. These questions test not just knowledge, but judgment under uncertainty —how you diagnose problems, what data you gather, and how you prioritize solutions when multiple issues might be contributing. Common Troubleshooting Scenarios:

Performance Troubleshooting Scenarios

•"A query that used to take 100ms now takes 10 seconds. What happened?" — Tests systematic debugging: check execution plan changes, statistics staleness, data growth, lock contention, index fragmentation, resource contention.
•"The database CPU is pegged at 100%. How do you investigate?" — Tests operational knowledge: identify active queries, check for runaway processes, analyze wait states, review connection pools, examine query plans.
•"Users report intermittent timeouts. How do you diagnose?" — Tests holistic thinking: network issues, connection pool exhaustion, lock waits, replication lag, garbage collection pauses.
•"Disk space is filling up unexpectedly. Why?" — Tests storage understanding: transaction log growth, temporary tables, index bloat, LOB storage, archive/backup policies.
•"Replica lag is increasing. What's wrong?" — Tests replication knowledge: write volume vs. replica capacity, network bandwidth, long-running transactions, bloated replication slots.

Operational/Situational Scenarios

•"How would you perform a zero-downtime schema migration?" — Tests deployment expertise: online DDL, dual-write patterns, ghost tables, application-level migration.
•"You need to add a column to a billion-row table. What's your approach?" — Tests large-scale operations: pt-online-schema-change, gh-ost, batching, index considerations.
•"Design a backup and recovery strategy for a critical database." — Tests operational maturity: RPO/RTO, full vs. incremental, point-in-time recovery, testing restores.
•"How do you handle a failed primary in a replicated setup?" — Tests failover knowledge: automatic vs. manual failover, split-brain prevention, data loss implications.
•"A runaway DELETE is deleting production data. What do you do?" — Tests incident response: kill query, prevent further damage, assess data loss, recovery options.

Experience Matters Here

Troubleshooting questions favor candidates with real operational experience. If you haven't managed production databases, study incident reports, post-mortems, and database blogs that discuss real outages. Understanding common failure modes—even theoretically—provides a foundation for structured diagnosis.

Behavioral and Experience Questions

Don't overlook behavioral questions in DBMS interviews. While technical skills get you through the door, behavioral questions assess how you apply those skills in real team contexts—handling disagreements, making decisions under pressure, learning from failures, and collaborating effectively. Common Behavioral Question Patterns:

DBMS-Specific Behavioral Questions
Question Pattern	What It Assesses	Strong Response Includes
"Tell me about a database performance issue you solved."	Diagnostic ability, systematic thinking	Specific metrics, investigation steps, quantified improvement
"Describe a time you made a schema design decision you later regretted."	Self-awareness, learning orientation	What went wrong, why, what you learned, how you'd do it differently
"How did you handle disagreement about database technology choice?"	Collaboration, influence skills	How you presented data, considered alternatives, reached resolution
"Tell me about a time you had to balance performance and correctness."	Trade-off judgment	Context, options considered, decision rationale, outcome
"Describe your approach to learning a new database technology."	Growth mindset, learning strategy	Specific example, structured approach, practical application

The STAR Framework

Structure behavioral responses using STAR: S ituation (context and constraints), T ask (your specific responsibility), A ction (what you did, step by step), R esult (quantified outcome, lessons learned). This framework ensures complete, compelling answers that demonstrate both competence and reflection.

Company and Role Variations

Interview emphasis varies significantly based on company type and role level. Calibrating your preparation to your target companies maximizes ROI on study time. Company Type Patterns:

Interview Focus by Company Type
Company Type	Primary Focus Areas	Secondary Focus	Notable Characteristics
FAANG / Big Tech	System design, SQL proficiency, scalability	Algorithms, conceptual depth	Multiple rounds, high bar, focus on scale thinking
Startups	Practical SQL, schema design, rapid learning	Full-stack considerations	Fewer rounds, pragmatic focus, breadth valued
Enterprise (Banks, Insurance)	SQL expertise, data integrity, procedures	Compliance, audit trails	Detailed SQL, stored procedures, legacy systems
Data Companies / Analytics	Advanced SQL, data modeling, ETL	Data quality, warehousing	Complex queries, analytical functions, data pipelines
Cloud Providers	Database internals, distributed systems	Service design, customer scenarios	Deep technical, architecture discussions
Database Vendors	Internals, implementation details, theory	Open source contributions	Deep dive on specific database systems

Role Level Variations:

Focus by Seniority Level

•Junior / Entry-Level — Heavy emphasis on SQL fundamentals, basic conceptual questions, correctness over optimization. Interviewers seek trainable candidates with solid foundations.
•Mid-Level — Balanced mix of SQL complexity, design questions, and some troubleshooting. Expected to work independently on well-scoped problems.
•Senior — Design-heavy interviews with emphasis on trade-offs, scaling, and real-world experience. Expected to mentor juniors and make architectural decisions.
•Staff / Principal — Open-ended system design, cross-team considerations, technology strategy. Expected to drive technical direction and handle ambiguity.
•DBA-Specific — Heavy operational focus: backup/recovery, performance tuning, high availability, monitoring, security. Vendor-specific expertise often required.

Research Your Target

Before interviewing, research the specific company's interview process through Glassdoor, Blind, and LinkedIn. Many companies publish blog posts about their interview philosophy. This intelligence helps you weight your preparation appropriately.

Database Management SystemsInterview Strategies

Mastering DBMS Interview Strategies

LevelAdvanced

Duration75 mins

TopicInterview Strategies

1 / 5

Common Question Types

Navigating the DBMS Interview Landscape

Technical interviews for roles involving databases—whether you're applying as a Software Engineer, Backend Developer, Data Engineer, DBA, or Solutions Architect—invariably probe your understanding of Database Management Systems. Unlike algorithm interviews that focus on narrow problem-solving patterns, DBMS interviews span a remarkably broad spectrum: from theoretical foundations and SQL proficiency to system design and real-world troubleshooting scenarios. Understanding what types of questions you'll encounter is the first step toward mastering these interviews. This knowledge transforms an overwhelming domain into a structured preparation plan, allowing you to allocate time efficiently and identify gaps in your expertise.

What You Will Master

By the end of this page, you will have a comprehensive taxonomy of DBMS interview question categories, understand what each category tests, see real examples from industry interviews, and know how different companies and roles emphasize different areas. This foundation enables strategic, targeted preparation.

The Question Type Spectrum

DBMS interview questions exist on a spectrum from purely theoretical to entirely practical, with significant variation based on company culture, role seniority, and team focus. Understanding this spectrum helps you calibrate expectations and preparation depth. The Four Major Categories: At the highest level, DBMS questions fall into four interconnected categories, each testing different competencies and increasingly valued as you advance in seniority:

DBMS Interview Question Categories Overview
Category	What It Tests	Typical Format	Seniority Focus
Conceptual/Theoretical	Foundational understanding of DBMS principles	Q&A, whiteboard explanations	All levels (heavier for junior)
SQL & Query Writing	Practical ability to write correct, efficient queries	Live coding, take-home, whiteboard	All levels (fundamental skill)
Design & Modeling	Schema design, normalization, system architecture	Whiteboard design sessions	Mid to Senior levels
Troubleshooting & Operations	Real-world problem diagnosis, performance tuning	Scenario-based discussions	Senior/Staff+ levels

Strategic Insight

Junior candidates face more conceptual and SQL questions—interviewers are validating foundational knowledge. Senior candidates encounter more design and troubleshooting scenarios—interviewers are testing judgment, experience, and the ability to navigate ambiguity. Staff+ and Principal-level interviews often blend all categories into complex, open-ended discussions.

Conceptual and Theoretical Questions

Conceptual questions probe your understanding of why databases work the way they do, not just how to use them. These questions reveal whether you've internalized foundational principles or merely memorized syntax and procedures. Why They Matter: Engineers who understand theory make better design decisions, debug problems faster, and communicate more effectively with peers. A candidate who can explain transaction isolation levels conceptually can reason about novel concurrency scenarios; one who only memorized definitions cannot.

Common Conceptual Question Areas

•ACID Properties — What are they? Why do they matter? Can you explain scenarios where each property prevents specific problems? How does eventual consistency differ?
•Normalization Theory — What are functional dependencies? Explain normal forms (1NF through 5NF). When is denormalization appropriate? What trade-offs exist?
•Transaction Management — What is a transaction? Explain isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable). What is a phantom read?
•Concurrency Control — How does locking work? Explain optimistic vs. pessimistic concurrency. What is MVCC (Multi-Version Concurrency Control)?
•Indexing Fundamentals — What is an index? Explain B-tree vs. B+-tree. When do indexes hurt performance? What is an index-only scan?
•Storage and Architecture — How does a database store data physically? What are pages, extents, tablespaces? How do buffer pools work?
•Query Processing — Explain the stages of query execution. What is a query plan? How does a cost-based optimizer work?
•CAP Theorem — Explain Consistency, Availability, and Partition Tolerance. Why can't you have all three? Give examples of CP vs. AP systems.
•Replication and Sharding — What is master-slave replication? What is sharding? How do you choose a shard key?

Example Interview Questions (Real Industry Examples):

Sample Conceptual Interview Questions
Question	What It Tests	Good Answer Includes
"Explain the difference between a clustered and non-clustered index."	Index architecture understanding	Storage differences, performance implications, when to use each, real examples
"What happens when a transaction violates Repeatable Read?"	Isolation level depth	Phantom read explanation, MVCC awareness, practical implications
"Why would you denormalize a schema?"	Pragmatic design thinking	Trade-offs (read speed vs. write complexity), specific use cases, how to mitigate downsides
"Explain the CAP theorem with a real-world example."	Distributed systems fundamentals	Clear definitions, real systems (Cassandra vs. PostgreSQL), partition scenarios
"How does ACID differ from BASE?"	Modern database paradigms	Definitions, appropriate use cases, eventual consistency mechanics

Depth Over Breadth

Interviewers often drill deeper after your initial answer. If you mention MVCC, expect follow-up questions about how it works, its garbage collection implications, and when it fails. Prepare to go at least two or three levels deep on any concept you mention.

SQL and Query Writing Questions

SQL proficiency is the most universally tested skill in DBMS interviews. Regardless of your role—backend engineer, data analyst, DBA, or platform architect—you will face queries to write, debug, or optimize. These questions range from straightforward SELECT statements to complex analytical queries involving multiple joins, subqueries, window functions, and CTEs (Common Table Expressions). Subcategories of SQL Questions:

SQL Question Subcategories

•Basic CRUD Operations — Write INSERT, UPDATE, DELETE, and SELECT statements. Handle NULL values correctly. Use appropriate WHERE clauses.
•Multi-Table Joins — INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, CROSS JOIN, self-joins. Know when each is appropriate and predict result set sizes.
•Aggregations and Grouping — GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX. Handle edge cases (empty groups, NULLs in aggregations).
•Subqueries and Derived Tables — Correlated vs. non-correlated subqueries. EXISTS vs. IN performance implications. Scalar subqueries.
•Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, running totals, moving averages. Partition and ordering.
•CTEs (Common Table Expressions) — WITH clause for readability, recursive CTEs for hierarchical data traversal.
•Set Operations — UNION, UNION ALL, INTERSECT, EXCEPT. When to use each and performance implications.
•Data Modification — Complex UPDATE statements with JOINs, INSERT...SELECT patterns, UPSERT (MERGE) operations.
•Query Optimization — Analyzing EXPLAIN plans, index usage, avoiding full table scans, query rewriting for performance.

Classic SQL Interview Patterns: Certain query patterns appear repeatedly across interviews. Mastering these patterns provides templates for solving novel variations:

Common SQL Interview Patterns
Pattern	Example Problem	Key Techniques
Top-N per Group	"Find the top 3 highest-paid employees per department"	Window functions (ROW_NUMBER), subqueries, LATERAL joins
Running Totals	"Calculate cumulative sales by date"	Window SUM with ordering, self-joins (older approach)
Gap Detection	"Find missing sequence numbers in orders"	Self-joins, LAG/LEAD, GENERATE_SERIES (PostgreSQL)
Consecutive Events	"Find users who logged in 3+ consecutive days"	Self-joins with date arithmetic, LAG/LEAD, gap-and-island technique
Duplicate Detection	"Find duplicate email addresses"	GROUP BY/HAVING COUNT > 1, window functions, self-joins
Hierarchical Queries	"Find all employees reporting to a manager (any level)"	Recursive CTEs, adjacency list traversal
Pivoting Data	"Convert rows to columns for monthly sales"	CASE/WHEN aggregation, PIVOT (SQL Server), CROSSTAB (PostgreSQL)
Second Highest	"Find the second highest salary"	LIMIT/OFFSET, DENSE_RANK window function, subquery with MAX

top_n_per_group.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Classic Pattern: Top-N Per Group
-- Problem: Find the top 3 highest-paid employees in each department
 
-- Approach 1: Window Function (Most Common, Most Readable)
WITH RankedEmployees AS (
    SELECT 
        employee_id,
        employee_name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) as salary_rank
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department_id,
    salary
FROM RankedEmployees
WHERE salary_rank <= 3
ORDER BY department_id, salary_rank;
 
-- Approach 2: Correlated Subquery (Works on older databases)
SELECT e1.*
FROM employees e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
      AND e2.salary > e1.salary
) < 3
ORDER BY e1.department_id, e1.salary DESC;

Common Pitfalls in SQL Interviews

Forgetting NULL handling (NULL in comparisons, aggregations, joins). 2) Confusing RANK vs DENSE_RANK vs ROW_NUMBER. 3) Missing edge cases (empty tables, duplicate values). 4) Writing syntactically correct but inefficient queries (nested subqueries where joins work). 5) Not considering index usage. Always articulate your assumptions and edge case handling.

Design and Modeling Questions

Design questions assess your ability to translate real-world requirements into effective database schemas and architectures. Unlike SQL questions that have "correct" answers, design questions are inherently open-ended—there are multiple valid approaches, and interviewers evaluate your reasoning process, trade-off analysis, and ability to iterate based on additional constraints. Two Major Subcategories:

Schema Design Questions

•Design the data model for Twitter/X
•Design a database for an e-commerce platform
•Model a ride-sharing application like Uber
•Design the schema for a booking system (hotels, flights)
•Create a data model for a messaging application
•Design tables for a content management system
•Model a multi-tenant SaaS application

System Architecture Questions

•How would you scale the database for 10x traffic?
•Design a caching strategy for read-heavy workloads
•How would you implement global replication?
•Design a sharding strategy for user data
•Architect a system for real-time analytics
•Design a database switching/migration strategy
•Plan for disaster recovery and high availability

What Interviewers Evaluate: Requirements Clarification — Do you ask clarifying questions before diving in? What's the scale (users, transactions/second)? Read-heavy or write-heavy? Consistency requirements? Logical Design — Entity identification, relationship modeling, attribute selection. Do you recognize many-to-many relationships? Handle polymorphism? Normalization Judgment — Do you apply normalization principles? More importantly, do you know when to denormalize for performance? Index Strategy — Can you identify which queries will run and what indexes they need? Do you consider composite indexes? Scaling Considerations — How does your design handle 10x, 100x growth? Where are the bottlenecks? Trade-off Articulation — Can you explain why you chose one approach over another? What did you sacrifice?

The Schema Design Framework

Follow this framework in design interviews: 1) Clarify requirements and constraints (scale, access patterns, consistency needs). 2) Identify core entities and relationships. 3) Draft initial schema (tables, columns, types). 4) Define primary keys and foreign keys. 5) Propose indexes based on query patterns. 6) Discuss scaling strategy (sharding, replication). 7) Address trade-offs and alternatives. Verbalizing this process demonstrates structured thinking.

Sample Design Question Evaluation Criteria
Criterion	Junior Expectation	Senior Expectation	Staff+ Expectation
Requirements Gathering	Accepts problem as stated	Asks clarifying questions	Challenges assumptions, identifies implicit requirements
Entity Modeling	Identifies obvious entities	Handles edge cases, polymorphism	Considers future evolution, extensibility
Normalization	Applies basic normal forms	Knows when to denormalize	Quantifies trade-offs with data estimates
Indexing	Suggests obvious indexes	Proposes composite indexes, covering indexes	Considers index maintenance cost, bloom filters
Scaling	Aware of scaling needs	Proposes sharding/replication	Designs for global distribution, considers CAP trade-offs

Troubleshooting and Operational Questions

Experienced engineers face scenario-based questions that simulate real production problems. These questions test not just knowledge, but judgment under uncertainty —how you diagnose problems, what data you gather, and how you prioritize solutions when multiple issues might be contributing. Common Troubleshooting Scenarios:

Performance Troubleshooting Scenarios

•"A query that used to take 100ms now takes 10 seconds. What happened?" — Tests systematic debugging: check execution plan changes, statistics staleness, data growth, lock contention, index fragmentation, resource contention.
•"The database CPU is pegged at 100%. How do you investigate?" — Tests operational knowledge: identify active queries, check for runaway processes, analyze wait states, review connection pools, examine query plans.
•"Users report intermittent timeouts. How do you diagnose?" — Tests holistic thinking: network issues, connection pool exhaustion, lock waits, replication lag, garbage collection pauses.
•"Disk space is filling up unexpectedly. Why?" — Tests storage understanding: transaction log growth, temporary tables, index bloat, LOB storage, archive/backup policies.
•"Replica lag is increasing. What's wrong?" — Tests replication knowledge: write volume vs. replica capacity, network bandwidth, long-running transactions, bloated replication slots.

Operational/Situational Scenarios

•"How would you perform a zero-downtime schema migration?" — Tests deployment expertise: online DDL, dual-write patterns, ghost tables, application-level migration.
•"You need to add a column to a billion-row table. What's your approach?" — Tests large-scale operations: pt-online-schema-change, gh-ost, batching, index considerations.
•"Design a backup and recovery strategy for a critical database." — Tests operational maturity: RPO/RTO, full vs. incremental, point-in-time recovery, testing restores.
•"How do you handle a failed primary in a replicated setup?" — Tests failover knowledge: automatic vs. manual failover, split-brain prevention, data loss implications.
•"A runaway DELETE is deleting production data. What do you do?" — Tests incident response: kill query, prevent further damage, assess data loss, recovery options.

Experience Matters Here

Troubleshooting questions favor candidates with real operational experience. If you haven't managed production databases, study incident reports, post-mortems, and database blogs that discuss real outages. Understanding common failure modes—even theoretically—provides a foundation for structured diagnosis.

Behavioral and Experience Questions

Don't overlook behavioral questions in DBMS interviews. While technical skills get you through the door, behavioral questions assess how you apply those skills in real team contexts—handling disagreements, making decisions under pressure, learning from failures, and collaborating effectively. Common Behavioral Question Patterns:

DBMS-Specific Behavioral Questions
Question Pattern	What It Assesses	Strong Response Includes
"Tell me about a database performance issue you solved."	Diagnostic ability, systematic thinking	Specific metrics, investigation steps, quantified improvement
"Describe a time you made a schema design decision you later regretted."	Self-awareness, learning orientation	What went wrong, why, what you learned, how you'd do it differently
"How did you handle disagreement about database technology choice?"	Collaboration, influence skills	How you presented data, considered alternatives, reached resolution
"Tell me about a time you had to balance performance and correctness."	Trade-off judgment	Context, options considered, decision rationale, outcome
"Describe your approach to learning a new database technology."	Growth mindset, learning strategy	Specific example, structured approach, practical application

The STAR Framework

Structure behavioral responses using STAR: S ituation (context and constraints), T ask (your specific responsibility), A ction (what you did, step by step), R esult (quantified outcome, lessons learned). This framework ensures complete, compelling answers that demonstrate both competence and reflection.

Company and Role Variations

Interview emphasis varies significantly based on company type and role level. Calibrating your preparation to your target companies maximizes ROI on study time. Company Type Patterns:

Interview Focus by Company Type
Company Type	Primary Focus Areas	Secondary Focus	Notable Characteristics
FAANG / Big Tech	System design, SQL proficiency, scalability	Algorithms, conceptual depth	Multiple rounds, high bar, focus on scale thinking
Startups	Practical SQL, schema design, rapid learning	Full-stack considerations	Fewer rounds, pragmatic focus, breadth valued
Enterprise (Banks, Insurance)	SQL expertise, data integrity, procedures	Compliance, audit trails	Detailed SQL, stored procedures, legacy systems
Data Companies / Analytics	Advanced SQL, data modeling, ETL	Data quality, warehousing	Complex queries, analytical functions, data pipelines
Cloud Providers	Database internals, distributed systems	Service design, customer scenarios	Deep technical, architecture discussions
Database Vendors	Internals, implementation details, theory	Open source contributions	Deep dive on specific database systems

Role Level Variations:

Focus by Seniority Level

•Junior / Entry-Level — Heavy emphasis on SQL fundamentals, basic conceptual questions, correctness over optimization. Interviewers seek trainable candidates with solid foundations.
•Mid-Level — Balanced mix of SQL complexity, design questions, and some troubleshooting. Expected to work independently on well-scoped problems.
•Senior — Design-heavy interviews with emphasis on trade-offs, scaling, and real-world experience. Expected to mentor juniors and make architectural decisions.
•Staff / Principal — Open-ended system design, cross-team considerations, technology strategy. Expected to drive technical direction and handle ambiguity.
•DBA-Specific — Heavy operational focus: backup/recovery, performance tuning, high availability, monitoring, security. Vendor-specific expertise often required.

Research Your Target

Before interviewing, research the specific company's interview process through Glassdoor, Blind, and LinkedIn. Many companies publish blog posts about their interview philosophy. This intelligence helps you weight your preparation appropriately.

Common Question Types

Navigating the DBMS Interview Landscape

The Question Type Spectrum

Conceptual and Theoretical Questions

SQL and Query Writing Questions

Design and Modeling Questions

Troubleshooting and Operational Questions

Behavioral and Experience Questions

Company and Role Variations

Focus by Seniority Level

There’s more to understand.

Common Question Types

Navigating the DBMS Interview Landscape

The Question Type Spectrum

Conceptual and Theoretical Questions

SQL and Query Writing Questions

Design and Modeling Questions

Troubleshooting and Operational Questions

Behavioral and Experience Questions

Company and Role Variations

Focus by Seniority Level

There’s more to understand.