Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()

Explore how to apply the SQL window functions FIRST_VALUE LAST_VALUE and NTH_VALUE to retrieve specific records from ordered sales data. Learn to write queries that rank and analyze sales performance using these functions and understand their syntax, usage, and alternatives. This lesson helps you master practical skills for intermediate SQL interviews.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to select specific values using SQL functions
SELECT SalesID, Month, SalesAmount,
        FIRST_VALUE(SalesAmount) OVER ( ORDER BY SalesID
          ) AS FirstMonthSales,
        LAST_VALUE(SalesAmount) OVER ( ORDER BY SalesID
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastMonthSales,
        NTH_VALUE(SalesAmount, 6) OVER ( ORDER BY SalesID
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS JuneSales
FROM   Sales;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT statement selects the columns SalesID, Month, and SalesAmount. We use AS to set an alias for each calculated column.

  • Lines 3–4: The FIRST_VALUE() function gets the sales from the first recorded SalesAmount based on the order of SalesID.

  • Lines 5–6: The LAST_VALUE() function gets the sales from the last recorded SalesAmount based on the order of SalesID. It considers the entire dataset with the ROWS clause set to UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

  • Lines 7–8: The NTH_VALUE() function retrieves the sales from the 6th recorded SalesAmount based on the order of SalesID. It considers the entire dataset with the ROWS clause set to UNBOUNDED PRECEDING ...