365 Days of Daily Coding: Day 135

I woke up today feeling much energised and light headed. It was as if all the pressure points in my head was released and I felt much relaxed and at ease. Perhaps, its because I have been thinking a lot of things and its stressing me out.

Puzzle #20: Price Points

I tried the solution in the oracle environment here.

WITH temp As (
Select 1001 As ProductID, 1.99 AS UnitPrice, '1/01/2018' AS EffectiveDate FROM Dual Union All
Select 1001 As ProductID, 2.99 AS UnitPrice, '4/15/2018' AS EffectiveDate FROM Dual Union All
Select 1001 As ProductID, 3.99 AS UnitPrice, '6/8/2018' AS EffectiveDate FROM Dual Union All
Select 2002 As ProductID, 1.99 AS UnitPrice, '4/17/2018' AS EffectiveDate FROM Dual Union All
Select 2002 As ProductID, 2.99 AS UnitPrice, '5/19/2018' AS EffectiveDate FROM Dual
)

SELECT ProductID, UnitPrice, EffectiveDate
FROM(
SELECT  ProductID, UnitPrice, EffectiveDate, 
RANK() OVER (PARTITION BY ProductID ORDER BY EffectiveDate DESC) AS rnk
From temp)
WHERE rnk = 1;