Query to find the third highest salary

Normally this question is asked check the analytical skills . This is the most commonly asked question the interviews. 

select * from( select ename, sal, dense_rank() over(order by sal desc)r from Employee) where r=3;

Oracle SQL Queries based on scenarios

Scenario 1:

The table Sales has the columns cust_id, prod_id, date_sold and qty_sold and the primary key is (cust_id, prod_id, date_sold). Date_sold has datatype DATE. Qty_sold has datatype INTEGER and is a not null column. Cust_id represents a customer and prod_id represents a product sold to the customer. Date_sold is the date on which the sale occurred. This table is populated for each customer, product and calendar date (in column date_sold) combination for all calendar dates in years 2014 and 2015. If there were no sales for a customer and product on a day, then the qty_sold is populated with 0.

Using an analytical function, write a select statement on table Sales to list all rows with date_sold in year 2015 displaying columns cust_id, prod_id, date_sold, qty_sold and the prev_qty_sold. The prev_qty_sold is computed as the quantity sold immediately prior to the date_sold of the current row for a customer and product combination. 

See example below for one customer and one product

Date_Sold Qty_Sold Prev_Qty_Sold
01/01/2015 50 
02/01/2015 40 50
03/02/2015 30 40


Solution:

select cust_id,
prod_id,
date_sold,
qty_sold,
lag(qty_sold, 1, 0) over(partition by cust_id, prod_id, date_sold order by cust_id, prod_id, date_sold) prv_qty_sold
from sales
where to_char(date_sold, 'yyyy') = '2015'
order by cust_id, prod_id, date_sold;



Scenario 2:

Table Sales has the columns cust_id, prod_id, date_sold and qty_sold and the primary key is (cust_id, prod_id, date_sold). Date_sold is of DATE datatype. Using an analytical function, write a query to list the rows with the latest date_sold for each combination of cust_id and prod_id.

Solution :

SELECT CUST_ID,
PROD_ID,
MAX(DATE_SOLD) OVER(PARTITION BY CUST_ID, PROD_ID ORDER BY CUST_ID, PROD_ID, DATE_SOLD DESC) MAX_DATE_SOLD
FROM SALES;

Oracle SQL - LEAD and LAG - Analytical Functions

Introduction

Both LAG and LEAD functions have the same usage, as shown below.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

value_expression :- Can be a column or a built-in function, except for other analytic functions.
offset :- The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default :- The value returned if the offset is outside the scope of the window. The default value is NULL.


Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno,
       ename,
       job,
       sal
FROM   emp
ORDER BY sal;

SQL>

LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

SQL>

LEAD

The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.

SELECT empno,
       ename,
       job,
       sal,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM   emp;

SQL>