365 Days of SQL: Day 152

Yay! 3 more puzzles to go and I wrap the advanced SQL puzzle. I am so elated beyond words. I have had lots of things in my mind about what to work on next after this. There’s:

• Continue SQL daily learning but this time I will cover on all the functions from Oracle
• Weekend with Serious SQL with Danny course (since it takes quite sometime to just complete one lesson)
• Weekly Tableau Learning ( it will be sort of daily learning but allowing myself to slack for 1-2 days in a week; it can be overwhelming somedays when done together with SQL daily learning)
• Weekend daily viz (either build vizes in Tableau or using Adobe Illustrator)

Puzzle #37 : Group Criteria Keys

I tried the solution in the live oracle environment here.

WITH temp1 AS (
SELECT 'Ord156795' AS OrderId, 'ACME' AS Distributor, 123 AS Facility, 'ABC' AS Zones, 100 AS AMOUNT FROM DUAL UNION ALL
SELECT 'Ord826109' AS OrderId, 'ACME' AS Distributor, 123 AS Facility, 'ABC' AS Zones, 75 AS AMOUNT FROM DUAL UNION ALL
SELECT 'Ord342876' AS OrderId, 'Direct Parts' AS Distributor,  789 AS Facility, 'XYZ' AS Zones, 150 AS AMOUNT FROM DUAL UNION ALL
SELECT 'Ord994981' AS OrderId, 'Direct Parts' AS Distributor, 789 AS Facility, 'XYZ' AS Zones, 125 AS AMOUNT FROM DUAL 
), temp2 AS ( SELECT ROWNUM AS CriteriaId, Distributor FROM
(SELECT Distinct Distributor FROM temp1
ORDER BY Distributor)
)

SELECT b.CriteriaId, a.OrderId, a.Distributor, a.Facility, a.Zones, a.AMOUNT
FROM temp1 a JOIN 
temp2 b
ON a.Distributor = b.Distributor
ORDER BY b.CriteriaId; 

This could have been easily achieved using DENSE_RANK() OVER (ORDER BY Distributor, Facility, Zones) lol!