Return Second Highest Date

Problem Statement

Given a sales table with the following structure:

We want to create a query that returns the most recent sale date and the second highest sale date for each sales person.

Solution

To solve this problem, we can use the rank() function in SQL to assign ranks to each sale date within each group of sales persons. We then use these ranks to select the required dates.

Here is an example query that achieves this:

SELECT 
    max(recent_ID_sale) AS ID_sale,
    sales_person,
    max(recent_sale) AS recent_sale,
    coalesce(max(last_sale), max(recent_sale)) AS last_sale
FROM (
    SELECT 
        sales_person,
        CASE WHEN rank() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) = 1 THEN sale_date END AS recent_sale,
        CASE WHEN rank() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) = 2 THEN sale_date END AS last_sale,
        CASE WHEN rank() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) = 1 THEN ID_sale END AS recent_ID_sale
    FROM t
) t
GROUP BY sales_person
ORDER BY recent_sale DESC;

How it Works

Let’s break down the query:

Step 1: Assigning Ranks to Sale Dates

CASE WHEN rank() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) = 1 THEN sale_date END AS recent_sale,
CASE WHEN rank() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) = 2 THEN sale_date END AS last_sale,

Here, we use the rank() function to assign a rank to each sale date within each group of sales persons. The PARTITION BY clause groups the rows by sales person, and the ORDER BY clause orders the rows by sale date in descending order (newest dates first). We then use these ranks to select the required dates.

Step 2: Selecting Required Dates

max(recent_ID_sale) AS ID_sale,
sales_person,
max(recent_sale) AS recent_sale,
coalesce(max(last_sale), max(recent_sale)) AS last_sale

We use the max() function to select the most recent sale date (recent_sale) and the second highest sale date (last_sale). If there are no sales dates for a particular sales person, we use the COALESCE function to return NULL.

Step 3: Grouping and Ordering

GROUP BY sales_person
ORDER BY recent_sale DESC;

We group the results by sales person using the GROUP BY clause, and then order the results by the most recent sale date (recent_sale) in descending order.

Example Use Case

Suppose we have a table with the following data:

Running the query above will return the following result:

This result shows the most recent sale date and the second highest sale date for each sales person.

Conclusion

In this article, we discussed how to use the rank() function in SQL to return the most recent sale date and the second highest sale date for each sales person. We also explained how to group and order the results using the GROUP BY and ORDER BY clauses. By following these steps, you can write effective queries to analyze sales data and make informed decisions.

Last modified on 2023-11-27