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;