365 Days of Daily Coding: Day 123

I still have 33 puzzles to go for until I can complete my advanced sql challenge. I am looking forward to completing all the puzzles and start working on Serious SQL by Danny Ma. I think from then on I am pretty much done with building my foundational knowledge although I would like to brush up on some of the theoeritical principles of database management system and perhaps start a project that I had in mind. I also have a data viz project pending. I encountered several obstacles so I think documenting them like I had done with my daily coding practices is going to help me make good progresses.

Puzzle 7:

I tried the solution in the live oracle environment here.

WITH temp AS (
Select 1001 AS CandidateId, 'Geologist'     AS Description FROM Dual Union All
Select 1001 AS CandidateId, 'Astrogator'    AS Description FROM Dual Union All
Select 1001 AS CandidateId, 'Biochemist'    AS Description FROM Dual Union All
Select 1001 AS CandidateId, 'Technician'    AS Description FROM Dual Union All
Select 2002 AS CandidateId, 'Surgeon'       AS Description FROM Dual Union All
Select 2002 AS CandidateId, 'Machinist'     AS Description FROM Dual Union All
Select 3003 AS CandidateId, 'Cryologist'    AS Description FROM Dual Union All
Select 4004 AS CandidateId, 'Selenologist'  AS Description FROM Dual
)
SELECT CandidateID FROM (
SELECT  CandidateID, ROW_NUMBER() OVER(PARTITION BY CandidateID ORDER BY Description) AS rown
FROM (
SELECT CandidateID, Description FROM temp
WHERE Description IN ('Geologist', 'Astrogator', 'Technician'))) WHERE rown =3