Interview Q&A

Short answer: Database design patterns are general solutions to recurring problems that arise in database schema design. Some common patterns include:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The CAP theorem (Consistency, Availability, Partition Tolerance) states that in a distributed database system, it is impossible to simultaneously guarantee all three of the following:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Eventual consistency is a consistency model used in distributed systems where updates to data will propagate and eventually become consistent across all nodes, but not necessarily immediately.

Explain a bit more

How it works: In an eventually consistent system, updates are made to a node and eventually, that update will be propagated to all other nodes. It allows for temporary inconsistencies, but ensures that the system will converge to a consistent state over time. Use cases: Suitable for systems that can tolerate a delay in consistency, like NoSQL databases (Cassandra, DynamoDB) and systems dealing with high availability and massive scale.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Anchor member selects the root manager; recursive member joins employees whose ManagerId equals the CTE EmpId. UNION ALL combines them. Watch MAXRECURSION.

Sample solution

WITH Hierarchy AS (
    SELECT EmpId, ManagerId, Name, 0 AS Lvl
    FROM Employees
    WHERE ManagerId IS NULL   -- root
    UNION ALL
    SELECT e.EmpId, e.ManagerId, e.Name, h.Lvl + 1
    FROM Employees e
    INNER JOIN Hierarchy h ON e.ManagerId = h.EmpId
)
SELECT * FROM Hierarchy
ORDER BY Lvl, Name
OPTION (MAXRECURSION 100);

Edge cases to mention

• Cycles in hierarchy
• Multiple roots
• Deep trees hitting recursion limit

Say “anchor + recursive member + termination” — that structure is the interview checklist.

Short answer: APPLY invokes a table-valued expression per outer row. CROSS APPLY is like INNER JOIN (outer row must produce rows). OUTER APPLY is like LEFT JOIN (keeps outer rows with NULLs). Useful for TOP N per group and TVFs.

Sample solution

-- Top 2 orders per customer
SELECT c.CustomerId, c.Name, o.OrderId, o.Amount
FROM Customers c
CROSS APPLY (
    SELECT TOP (2) OrderId, Amount
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    ORDER BY Amount DESC
) o;

TOP N per group via CROSS APPLY is a classic SQL Server interview flex.

Short answer: MERGE matches source to target ON a key, then WHEN MATCHED UPDATE, WHEN NOT MATCHED INSERT. Discuss concurrency and that some teams prefer separate UPDATE/INSERT.

Sample solution

MERGE INTO Employees AS t
USING (SELECT @EmpId AS EmpId, @Name AS Name, @Salary AS Salary) AS s
ON t.EmpId = s.EmpId
WHEN MATCHED THEN
    UPDATE SET Name = s.Name, Salary = s.Salary
WHEN NOT MATCHED THEN
    INSERT (EmpId, Name, Salary) VALUES (s.EmpId, s.Name, s.Salary);

Know that MERGE has historically had race/edge-case caveats — saying so shows maturity.

Short answer: Gaps-and-islands: ROW_NUMBER() ordered by date, subtract from date to form an island key, then GROUP BY user + island key and count days.

Sample solution

WITH d AS (
    SELECT UserId, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate), LoginDate) AS grp
    FROM UserLogins
)
SELECT UserId, MIN(LoginDate) AS StartDate, MAX(LoginDate) AS EndDate, COUNT(*) AS Days
FROM d
GROUP BY UserId, grp
HAVING COUNT(*) >= 3;

Naming “gaps and islands” immediately signals senior SQL fluency.

Short answer: Talk about DMVs: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_os_waiting_tasks, and reading plans. For query writing interviews, also discuss indexes, sargability, and avoiding SELECT *.

Sample solution

SELECT r.session_id, r.status, r.command, r.wait_type, r.blocking_session_id,
       t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;

Even app developers score points naming DMVs and “parameter sniffing” briefly.

Short answer: Filter orders to the year, group by customer, and HAVING COUNT(DISTINCT month) = 12.

Sample solution

SELECT CustomerId
FROM Orders
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
GROUP BY CustomerId
HAVING COUNT(DISTINCT DATEPART(MONTH, OrderDate)) = 12;

COUNT(DISTINCT month) is the key — COUNT(*) alone is wrong if multiple orders/month.

Short answer: Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (), or pick middle ROW_NUMBER values for odd/even counts.

Sample solution

SELECT DISTINCT
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER () AS MedianSalary
FROM Employees;

PERCENTILE_CONT is continuous; PERCENTILE_DISC picks an actual data value.

Short answer: ROLLUP creates subtotals and a grand total. GROUPING SETS lists exact aggregation levels. GROUPING(col) detects total rows (NULLs that mean “all”).

Sample solution

SELECT DepartmentId, JobTitle, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY ROLLUP (DepartmentId, JobTitle);

Use GROUPING() to distinguish real NULL dimension values from rollup NULLs.

Short answer: A covering index includes all columns a query needs (key + INCLUDE), enabling an Index Seek/Scan without Key Lookup. In plans, look for absence of Key Lookup and lower estimated cost.

Offer: CREATE INDEX ... INCLUDE (ColA, ColB) as the practical answer.

Short answer: Use AVG(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Sample solution

SELECT OrderDate, Amount,
       AVG(Amount * 1.0) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS MovingAvg7
FROM DailySales;

ROWS vs RANGE matters when OrderDate has duplicates.

Short answer: Two ranges [a,b] and [c,d] overlap when a <= d AND c <= b (for closed intervals). Self-join bookings/events with that predicate and exclude the same Id.

Sample solution

SELECT a.Id AS Id1, b.Id AS Id2
FROM Bookings a
INNER JOIN Bookings b ON a.Id < b.Id
    AND a.StartDate <= b.EndDate
    AND b.StartDate <= a.EndDate;

Clarify inclusive/exclusive end times — hotel checkout logic often uses half-open intervals.