--Query 12: "Create table of high queue length (Avg Delay - DayMin), SlowMin"

--PathStats: Compute the avg delay and the daily minimum for a NodePairID for a
-- given day

WITH \
PathStats(NodePairId, Time, AvgDelay) as \
( \
SELECT  t.NodePairId as NodePairId, \
	((t.start/60) - (954633600/60)) as Time, \
	avg(t.delay) as AvgDelay \
FROM    Surveyor.Times t \
WHERE   t.delay != -1 AND \
        t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) AND \
	t.NodePairId = 1 \
GROUP BY	t.NodePairId, (t.start/60) \
), \
DayMin(NodePairId, DayMin) as \
( \
SELECT	t.NodePairId as NodePairId, \
	min(t.delay) as DayMin \
FROM	Surveyor.Times t \
WHERE	t.delay != -1 AND \
        t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) AND \
	t.NodePairId = 1 \
GROUP BY	t.NodePairId \	
) \
--SELECT	* \
--FROM	PathStats, DayMin \
--WHERE	PathStats.NodePairId = DayMin.NodePairId \
--ORDER BY	Time
SELECT	ps.NodePairId as "PathSpec", \
	ps.Time as "Mins Since Midnight", \
	(ps.AvgDelay - dm.DayMin) as "Avg Delay - DayMin" \
FROM	PathStats ps, DayMin dm \
ORDER BY	ps.NodePairId, ps.Time
