365 Days of Daily Coding: Day 138

I have been in delimma whether to continue working on my next data viz projects or learn alteryx. I am torn apart but my heart says data viz project so it is. As mentioned in my previous post, I will be using adobe illustrator this time around however, I will need to complete my work in 7 days cause the free trial is only valid for those number of days. The subscription fee is also quite expensive considering I will not be using it so frequently so it does not make sense. Otherwise, for my training I will perhaps opt for a monthly subscription and stop once I am satisfied.

Puzzle #23: Divide in Half

I tried the solution in the live oracle environment here.

WITH temp AS 
(SELECT 1001 AS PlayerID, 2343 AS Score FROM DUAL UNION ALL
SELECT 2002 AS PlayerID, 9432 AS Score FROM DUAL UNION ALL
SELECT 3003 AS PlayerID, 6548 AS Score FROM DUAL UNION ALL
SELECT 4004 AS PlayerID, 1054 AS Score FROM DUAL UNION ALL
SELECT 5005 AS PlayerID, 6832 AS Score FROM DUAL),
temp2 AS (Select PlayerID, Score, 
DENSE_RANK() OVER (ORDER BY Score) AS Rnk from temp),
temp3 AS (SELECT MAX(Rnk) Max_rnk FROM temp2)

SELECT PlayerID, Score, Rnk,
CASE WHEN Rnk < (Max_rnk/2) THEN 2 ELSE 1 END AS Half_Score 
FROM (
SELECT * FROM temp2 CROSS JOIN temp3
ORDER BY Rnk)