Comparing Employee Salaries to Manager Salaries

1. Introduction: The Core Challenge of Data Granularity in SQL

When facing SQL interview questions or writing production analytics queries, the single most critical concept to master is data granularity. Granularity refers to the level of detail represented by a single row in a table. Misunderstanding granularity is the #1 reason candidates write incorrect queries that pass syntax checks but return logically flawed data.

• Granularity Awareness: Before writing a single line of SQL, you must ask: "What does one row in this table represent?"
• Employee Table Granularity: In an employee table, one row typically represents a unique employee_id. There are no duplicate employee IDs.
• Transactional Table Granularity: In an orders or sales table, one row represents a single line item in an order. A single product can appear across hundreds of different orders, resulting in multiple rows per product.

2. Row Number vs. Dense Rank: Handling Ties

When filtering for top results within groups (e.g., top employees per department or top products per category), window functions like ROW_NUMBER() and DENSE_RANK() behave differently when ties occur.

Understanding the Differences

• ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition, regardless of whether values are identical. If two employees share the exact same top salary, one arbitrarily gets rank 1 and the other gets 2.
• DENSE_RANK(): Assigns the same rank to rows with equal values. If two employees share the highest salary, both receive rank 1, and the next distinct salary receives rank 2.

Interview Tip

Note: There is rarely a universally "correct" answer between ROW_NUMBER() and DENSE_RANK(). When an interviewer asks for "top $N$ employees," always clarify how they want ties handled (e.g., "If three employees tie for the second-highest salary, should we return all three or strictly two?").

3. Finding Top Employees by Department (No Aggregation Required)

When the base table's granularity matches the entity you want to evaluate (e.g., finding top salaries in an employees table where each row is a unique employee), you do not need to pre-aggregate. You can apply window functions directly.

Naive vs. Correct Implementation

-- INCORRECT APPROACH: Trying to filter window functions directly in WHERE
SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
FROM employees
WHERE rn <= 2; -- Error: Window functions cannot be used directly in WHERE clause

-- CORRECT APPROACH: Using a Common Table Expression (CTE) or Subquery
WITH RankedEmployees AS (
    SELECT 
        employee_id,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * 
FROM RankedEmployees 
WHERE rn <= 2;

4. The Trap of Order-Level vs. Product-Level Granularity

When switching from an employees table to an orders table, the granularity shifts from entity-level to transactional-level.

Real-Life Analogy: Counting Book Sales vs. Book Titles

Imagine you manage a bookstore. If you want to find your top 5 best-selling book titles, you cannot simply sort the raw checkout receipt logs by sales amount and pick the top 5 rows. Why? Because Book A was sold on receipt #101, receipt #205, and receipt #312. It appears across multiple rows. If you sort raw rows by sales, you are looking at individual transaction sizes, not total product performance.

The Wrong Approach (Granularity Mismatch)

-- WRONG: This checks sales at the individual order/transaction level, 
-- not aggregated across products!
SELECT TOP 5 product_id, category, sales
FROM orders
ORDER BY sales DESC;

5. Step-by-Step Evolution: Pre-Aggregating Sales Data

To correctly find top products by sales, you must first collapse the transactional data to the product level using an aggregate function (SUM), and then apply your ranking logic.

Step 1: Aggregate at the Product Level (CTE)

WITH ProductSales AS (
    SELECT 
        product_id,
        category,
        SUM(sales) AS total_sales
    FROM orders
    GROUP BY product_id, category
)

• Why this works: This reduces 1,862 raw transaction rows down to distinct product rows, ensuring each product has exactly one summary row with its total sales.

Step 2: Apply Ranking on Top of Aggregated Data

, RankedProducts AS (
    SELECT 
        product_id,
        category,
        total_sales,
        ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS rn
    FROM ProductSales
)
SELECT product_id, category, total_sales
FROM RankedProducts
WHERE rn <= 5;

6. Partitioning by Category: Finding Top Products Within Each Category

Interviewers frequently elevate this question by adding a grouping dimension: "Find the top 5 products by sales within each category." This is structurally identical to finding top salaried employees within each department.

Complete Query Implementation

WITH ProductSales AS (
    -- Step 1: Aggregate sales at the product and category level
    SELECT 
        category,
        product_id,
        SUM(sales) AS total_sales
    FROM orders
    GROUP BY category, product_id
),
RankedCategoryProducts AS (
    -- Step 2: Partition by category and rank products by sales
    SELECT 
        category,
        product_id,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rn
    FROM ProductSales
)
-- Step 3: Filter for the top 5 within every category
SELECT 
    category,
    product_id,
    total_sales,
    rn
FROM RankedCategoryProducts
WHERE rn <= 5;

Execution Flow Walkthrough

orders table (Raw Transactions)
   |
   | GROUP BY category, product_id
   v
ProductSales CTE (1 row per unique Product)
   |
   | PARTITION BY category ORDER BY total_sales DESC
   v
RankedCategoryProducts CTE (Rank assigned per category)
   |
   | WHERE rn <= 5
   v
Final Result Set (Top 5 products for Furniture, Office Supplies, Technology)

7. Summary Table: Granularity & Window Function Strategy

Summary and Key Takeaways

• Granularity is King: Always determine whether your base table has one row per entity (like employees) or multiple rows per entity (like orders).
• Never Skip Aggregation for Transactions: If an entity appears across multiple transaction rows, you must GROUP BY and aggregate (e.g., SUM(sales)) before attempting to rank them.
• Window Functions Cannot Live in WHERE: Direct filtering of window function outputs (rn <= 2) in the WHERE clause results in a syntax error. Always wrap your window function in a CTE or subquery.
• Clarify Tie-Breaking Rules: Understand the difference between ROW_NUMBER() (sequential, arbitrary tie-breaking) and DENSE_RANK() (preserves ties, no gaps). Always ask the interviewer which behavior they expect.
• Partitioning Mirrors Grouping: Using PARTITION BY category in a window function is the analytical equivalent of grouping by category while retaining row-level detail for ranking.
• Production Query Hygiene: Structure complex multi-step queries using Common Table Expressions (WITH clauses) to keep your logic modular, readable, and easy to debug.