Week 10 - SQL Chips and Chunks Newsletter

1. SQL Chip: Understanding RANK() and DENSE_RANK() RANK() and DENSE_RANK() are essential SQL window functions used to assign rankings to rows based on a specific order. They are commonly used to rank employees by salary, customers by purchases, or products by sales.

• RANK() assigns a rank to each row, but if there are duplicate values, it skips the next rank(s).

• DENSE_RANK() also assigns a rank but does not skip numbers when duplicates exist.

Example: Ranking employees by salary in descending order within each department.

SELECT 
    department, 
    name, 
    salary, 
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_position, 
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_position
FROM employees;

This query assigns ranks to employees within their department based on salary.

2. SQL Chunk: Differences Between RANK() and DENSE_RANK() A key distinction between RANK() and DENSE_RANK() is how they handle ties.

Example:

• RANK() skips from 1 to 3 when there is a tie.

• DENSE_RANK() continues with the next available rank.

3. SQL Challenge: Finding the Top 3 Earners in Each Department Challenge: Write a query to retrieve the top 3 earners in each department using RANK().

Hint: Use PARTITION BY department and filter using a common table expression (CTE).

WITH RankedSalaries AS (
    SELECT 
        department, 
        name, 
        salary, 
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_position
    FROM employees
)
SELECT 
    department, 
    name, 
    salary
FROM RankedSalaries
WHERE rank_position <= 3;

This query ranks employees by salary within their departments and filters to include only the top 3 earners in each group.

4. Common SQL Mistake: Misusing RANK() Instead of DENSE_RANK() A common mistake is using RANK() when DENSE_RANK() is more appropriate. If you need consecutive rankings without gaps, use DENSE_RANK().

Incorrect:

SELECT 
    department, 
    name, 
    salary, 
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_position
FROM employees;

Correct:

SELECT 
    department, 
    name, 
    salary, 
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_position
FROM employees;

Use RANK() when you want to maintain rank gaps for ties, and DENSE_RANK() when you need a continuous sequence.

5. SQL in Practice: Finding the Best-Selling Products Per Category RANK() and DENSE_RANK() are useful for ranking sales performance. Here’s an example of ranking the top-selling products within each category:

SELECT 
    category, 
    product_name, 
    SUM(sales_amount) AS total_sales, 
    RANK() OVER (PARTITION BY category ORDER BY SUM(sales_amount) DESC) AS sales_rank
FROM sales
GROUP BY category, product_name
HAVING SUM(sales_amount) > 0
ORDER BY category, sales_rank;

This query ranks products within their categories based on total sales.

6. SQL Resource: SQLBolt - Interactive SQL Lessons For those looking to sharpen their SQL skills, SQLBolt offers free, interactive lessons covering ranking functions like RANK(), DENSE_RANK(), and ROW_NUMBER().

SQLBolt provides a structured approach with exercises, making it an excellent resource for both beginners and experienced users looking to practice SQL concepts hands-on.

Check it out here: SQLBolt