365 Days of Daily Coding: Day 128

I had a slow start today. I was supposed to attend a friend’s wedding. Unfortunately, my visa is up for renewal and I do not have my passport with me. That means my movement is limited until I can get hold of my passport. Polices in Malaysia are notorious when they catch foreigners without their passport. You would have to fork up ringgits to escape going to jail. Hence, I chose not to go although I feel very bad for letting down my friend.

Puzzle 12: Average Days

I tried the solution in the live oracle environment here.

WITH temp AS (
Select 'Alpha' AS Workflow, '6/1/2018' AS ExecutionDate FROM Dual Union All
Select 'Alpha' AS Workflow, '6/14/2018' AS ExecutionDate FROM Dual Union All
Select 'Alpha' AS Workflow, '6/15/2018' AS ExecutionDate FROM Dual Union All
Select 'Bravo' AS Workflow, '6/1/2018' AS ExecutionDate FROM Dual Union All
Select 'Bravo' AS Workflow, '6/2/2018' AS ExecutionDate FROM Dual Union All
Select 'Bravo'  AS Workflow, '6/19/2018' AS ExecutionDate FROM Dual Union All
Select 'Charlie' AS Workflow, '6/1/2018' AS ExecutionDate FROM Dual Union All
Select 'Charlie' AS Workflow, '6/15/2018' AS ExecutionDate FROM Dual Union All
Select 'Charlie' AS Workflow, '6/30/2018' AS ExecutionDate FROM Dual
)
Select Workflow, TRUNC(AVG(Diff)) AS Average_Days
FROM (
SELECT Workflow, LeadDate - ExecDate AS Diff 
FROM (
SELECT Workflow, ExecDate, LEAD(ExecDate) OVER (PARTITION BY Workflow ORDER BY ExecDate) AS LeadDate
FROM (
SELECT Workflow, TO_DATE(ExecutionDate, 'MM/DD/YYYY') AS ExecDate
FROM temp)))
GROUP BY Workflow;