SQL Server Interview Topic 9: Advanced Query Patterns

This topic checks whether you can write SQL for real report and dashboard requirements. These questions usually come after the interviewer is confident about your basics.

🎯 Interview Goal

You should be able to explain ranking, running totals, conditional aggregation, CTEs, temp tables, and report query design in simple project language.

Q69. How do you rank rows in SQL Server?

Quick interview answer:

Use window functions like ROW_NUMBER, RANK, and DENSE_RANK. ROW_NUMBER gives a unique number to every row. RANK and DENSE_RANK handle ties differently, which is useful for ranking students by marks.

Study in detail: Window Functions - This article explains row numbering and ranking with examples.

Q70. What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

Quick interview answer:

ROW_NUMBER always gives unique numbers. RANK gives the same rank for ties but leaves gaps. DENSE_RANK gives the same rank for ties without gaps. Use the one that matches the report requirement.

Study in detail: Window Functions - This lesson shows how ranking results differ.

Q71. How do you calculate running totals?

Quick interview answer:

A running total can be calculated using a window function with SUM and OVER. It helps when reports need cumulative values, like total fees collected day by day.

Study in detail: Window Functions - This article explains calculations across related rows.

Q72. What is conditional aggregation?

Quick interview answer:

Conditional aggregation means using SUM or COUNT with CASE to calculate different totals in one query. For example, one report can show pass count and fail count class-wise.

Study in detail: Math Functions and CASE and GROUP BY and HAVING - These lessons explain CASE and grouped reports.

Q73. When do you use a CTE instead of a subquery?

Quick interview answer:

Use a CTE when it makes a query easier to read, especially when the logic has multiple steps. A subquery is fine for small simple conditions. For complex reports, a named CTE is usually clearer.

Study in detail: CTEs and Subqueries - These articles compare reusable query parts.

Q74. What is a temporary table?

Quick interview answer:

A temporary table stores intermediate data for a short time during a session or procedure. It can help when a large operation needs multiple steps. But it should be used only when it makes the query easier or faster.

Study in detail: Advanced Patterns - This article explains practical SQL patterns used in real work.

Q75. How do you write a report query step by step?

Quick interview answer:

First identify the final columns, then find the required tables, joins, filters, and grouping. Test each part with sample data before adding more complexity. This avoids wrong totals and missing records.

Study in detail: Real-World SMS Scenarios and Complex Business Logic - These lessons show report-building scenarios.

Practice Before Next Topic

Explain or write queries for these tasks:

1. Rank students by marks.
2. Show class-wise pass and fail counts.
3. Calculate daily running fee collection.
4. Use a CTE to simplify a report query.
5. Build a dashboard query step by step.

💡 Beginner Interview Tip

For advanced query questions, explain the problem first. Then explain which SQL feature solves it.