365 Days of Daily Coding: Day 91

It’s 11 pm right now and I have spent almost 1.3 hour trying to solve today’s challenge. It was pretty interesting having to come out with a way to find the mode when there are no such functions in Oracle like we have for Mean and Median.

I have also become acutely aware of how time flies so quickly these days. I used to have bunch of items I wanted to do in my to-do list and I would struggle to complete all of them most of the time. These days, I approach them more pragmatically as in I only list the things that are of utmost priority and want to get done that day.

Advanced SQL Challenge

WITH temp1 As (
Select 5 As Val FROM Dual Union All
Select 6 As Val FROM Dual Union All
Select 10 As Val FROM Dual Union All
Select 10 As Val FROM Dual Union All
Select 13 As Val FROM Dual Union All
Select 14 As Val FROM Dual Union All
Select 17 As Val FROM Dual Union All
Select 20 As Val FROM Dual Union All
Select 81 As Val FROM Dual Union All
Select 90 As Val FROM Dual Union All
Select 76 As Val FROM Dual
),
temp2 AS (
SELECT Val AS MODES, COUNT(*) AS cnt
FROM temp1
GROUP BY Val
ORDER BY cnt DESC
FETCH FIRST 1 ROW ONLY), 
temp3 AS (
SELECT ROUND(AVG(Val)) AS MEAN, MEDIAN(Val) AS MEDIAN, MAX(Val) - MIN(Val) AS RANGES
FROM temp1
)
SELECT t2.MODES, t3.MEAN, t3.MEDIAN, t3.RANGES 
FROM temp3 t3 CROSS JOIN temp2 t2

My original query was much longer but after revision I was able to shorten it.