Top 5 Advanced SQL Interview Questions and Answers | Frequently Asked SQL interview questions

Main ideas and lessons (by question)

1) “Top N” queries with variations (critical: data granularity + ties handling)

The speaker emphasizes that “top N” answers depend on:

• The level of uniqueness in the underlying table(s)
• The wording of the question (e.g., “within each department/category”)

Covered variations

• Overall top N (no partitioning)
• Top N per department/category (requires window functions)
• Top N products by sales when the raw table has multiple rows per product (requires aggregation first)
• Tie behavior differs by ranking function:
  • ROW_NUMBER
  • RANK
  • DENSE_RANK

Methodology / logic (step-by-step SQL approach)

• A. Overall top 2 highest salaried employees (no duplicates in employee table)

  • Sort by salary descending and take the first N.
  • Conceptually:
    • ORDER BY salary DESC
    • Use TOP N / LIMIT N (database dependent)
  • Key point: if granularity already matches the “entity” (one row per employee), no window function is needed.

• B. Top 2 employees within each department (requires partitioning)

  • Use a window function partitioned by department.
  • Conceptually:
    • ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC)
    • Then filter:
      • WHERE RN <= N

• C. Handling ties (important follow-up)

  • The speaker recommends asking the interviewer what they expect when salaries tie:
    • Should tied rows produce more results (distinct ranks) or the same rank (potentially fewer/more depending on filter logic)?
  • Ranking differences:
    • ROW_NUMBER: ties are broken by distinct numbers ⇒ typically returns exactly N rows per partition.
    • RANK: ties share the same rank ⇒ filtering can return more than N rows in a partition.
    • DENSE_RANK: ties share the same rank without gaps (mentioned as an alternative).
  • Example consequence:
    • For “top 2” using RANK, ties can yield 3 rows in a partition if multiple tied rows fall into the “top rank boundary.”

• D. Top 5 products by sales (must aggregate because orders table has multiple rows per product)

  • Warning: TOP 5 ... ORDER BY sales DESC directly on the orders table is wrong because it ranks by order-row level, not product level.
  • Correct approach:
    1. Aggregate sales per ProductID:
       • GROUP BY ProductID
       • SUM(Sales) AS TotalSales
    2. Compute top 5 products using TotalSales descending.

• E. Top 5 products within each category

  • Similar to department partitioning, but partition key becomes Category.
  • Correct approach:
    1. Aggregate per (Category, ProductID)
    2. Apply:
       • ROW_NUMBER() OVER (PARTITION BY Category ORDER BY TotalSales DESC)
    3. Filter where rank/row number <= 5.

Core lesson for this section

• Always determine the granularity implied by the question:
  • “within each department/category” ⇒ use PARTITION BY
  • “top products by sales” ⇒ if base table has multiple rows per product ⇒ aggregate first
  • Ties ⇒ choose the right ranking function and align behavior with what the interviewer expects

---

2) Year-over-year (YoY) growth using LAG (optionally partitioning by category)

The second topic uses the window function LAG to compare current year sales with previous year sales.

YoY growth formula (conceptual)

[ \text{YoY \%} = \frac{(\text{current} - \text{previous})}{\text{previous}} \times 100 ]

• For the first year, previous-year sales don’t exist ⇒ treat growth as 0
  • Conceptually handled using LAG’s default (or equivalent COALESCE-like logic).

Methodology / logic shown

• A. Aggregate sales to year level first

  • From orders data:
    • YEAR(OrderDate) as OrderYear
    • SUM(Sales) grouped by year

• B. Create previous year sales using LAG

  • On the year-aggregated result:
    • LAG(Sales, 1) OVER (ORDER BY OrderYear)
  • Handle missing previous year (first year) using LAG default/equivalent.

• C. Compute YoY growth percentage

  • Use:
    • (Sales - PreviousSales) / PreviousSales * 100

• D. Variation: YoY growth within each category

  • Aggregate by (Category, Year)
  • Use:
    • LAG with:
      • PARTITION BY Category
      • ORDER BY Year

• E. Another variation mentioned

  • “Current month sales > previous month sales”
  • Key granularity insight:
    • aggregate/compare at product + month/year level
    • use LAG to get previous month sales
    • then filter where current > previous

---

3) Running totals: cumulative sum vs rolling window sums (calendar/partition/order matters)

This topic covers advanced windowing:

• Cumulative sum / running total (all previous periods)
• Rolling window sum (e.g., last 3 months) using window frames like ROWS BETWEEN ...

Methodology / logic shown

• A. Cumulative sales year-wise

  • Aggregate sales by year first: GROUP BY Year
  • Then compute:
    • SUM(Sales) OVER (ORDER BY Year)
  • Optional:
    • partition by category if needed

• B. Cumulative sales by category

  • Use:
    • PARTITION BY Category
    • ORDER BY Year

• C. Rolling 3-month sales (requires month-level granularity)

  • Aggregate to month level (and optionally category).
  • Then compute rolling sum using the window frame:
    • Include current month:
      • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    • Exclude current month:
      • ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING

Core lesson

• Rolling windows depend on the exact frame clause and whether you include the current row.

---

4) Pivoting rows into columns using CASE statements

The fourth question transforms category-wise rows into category-wise columns.

Example intent

• For each year, show sales in separate columns:
  • FurnitureSales, OfficeSuppliesSales, TechnologySales, etc.

Methodology / logic shown

• A. Aggregate sales by year and category

  • GROUP BY Year, Category
  • SUM(Sales)

• B. Pivot using CASE expressions

  • One column per category:
    • SUM(CASE WHEN Category = 'Furniture' THEN Sales ELSE 0 END) AS FurnitureSales
  • Repeat for other categories (e.g., Office Supplies, Technology, etc.).

Variation note

• The structure stays the same; only the CASE conditions change to match the requested categories.

---

5) Join types and row counts (theoretical concept; detailed explanation deferred)

The final question focuses on output row counts for:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

The speaker notes that a separate detailed video explains this and instructs viewers to check it.

Core lesson (high level)

• Join type determines which rows are preserved when keys match or don’t match between tables.

---

Speakers / sources featured

• Speaker: The host/presenter (intro and agenda statements)
• Source: Auto-generated subtitles from a single YouTube video titled “Top 5 Advanced SQL Interview Questions and Answers | Frequently Asked SQL interview questions” (No external written sources cited.)