365 Days of Daily Coding: Day 136

As I hear the bird singing, I feel extremely grateful today. May I always be reminded that the only thing guranteed in life is today and may I have the courage and the blessings to pursue and fulfil my passion, live my authentic self every second, every minute and every hour and live to my fullest potential.

Puzzle #21 : Average Monthly Sales

I tried the solution in the live oracle environment here.

WITH temp AS (
Select 'Ord145332' As OrderID, 1001 AS CustomerID, '1/1/2018' AS OrderDate, 100 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord657895' As OrderID, 1001 AS CustomerID, '1/1/2018' AS OrderDate, 150 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord887612' As OrderID, 1001 AS CustomerID, '1/1/2018' AS OrderDate, 75 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord654374' As OrderID, 1001 AS CustomerID, '2/1/2018' AS OrderDate, 100 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord345362' As OrderID, 1001 AS CustomerID, '3/1/2018' AS OrderDate, 100 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord912376' As OrderID, 2002 AS CustomerID, '2/1/2018' AS OrderDate, 75 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord543219' As OrderID, 2002 AS CustomerID, '2/1/2018' AS OrderDate, 150 AS Amount, 'TX' AS State FROM Dual Union All
Select 'Ord156357' As OrderID, 3003 AS CustomerID, '1/1/2018' AS OrderDate, 100 AS Amount, 'IA' AS State FROM Dual Union All
Select 'Ord956541' As OrderID, 3003 AS CustomerID, '2/1/2018' AS OrderDate, 100 AS Amount, 'IA' AS State FROM Dual Union All
Select 'Ord856993' As OrderID, 3003 AS CustomerID, '3/1/2018' AS OrderDate, 100 AS Amount, 'IA' AS State FROM Dual Union All
Select 'Ord864573' As OrderID, 4004 AS CustomerID, '4/1/2018' AS OrderDate, 100 AS Amount, 'IA' AS State FROM Dual Union All
Select 'Ord654525' As OrderID, 4004 AS CustomerID, '5/1/2018' AS OrderDate, 50 AS Amount, 'IA' AS State FROM Dual Union All
Select 'Ord987654' As OrderID, 4004 AS CustomerID, '5/1/2018' AS OrderDate, 100 AS Amount, 'IA' AS State FROM Dual
), temp2 AS (
SELECT CustomerID, EXTRACT(month FROM to_date(OrderDate, 'DD/MM/YYYY')) AS Months, State,
ROUND(AVG(AMOUNT)) AS Avg
fROM temp
GROUP BY CustomerID,EXTRACT(month FROM to_date(OrderDate, 'DD/MM/YYYY')), State)

SELECT DISTINCT State FROM temp2
WHERE STATE NOT IN (
SELECT State FROM temp2
WHERE Avg < 100);