--Query 13: "Plot SlowMinute against LossyMinute for a PathSpec for a day"

WITH \
-- for getting slow mins
PathStats(NodePairId, Time, DayMin, AvgDelay) as \
( \
SELECT  t.NodePairId as NodePairId, \
	((t.start/60) - (954633600/60)) as Time, \
	min(t.delay) as DayMin, \
	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 \
), \
MinuteProbeCount(NodePairID, Time, Probes) as \
( \
SELECT  t.NodePairID as NodePairID, \
        ((t.start/60) - (954633600/60)) as Time, \
        count(*) as Probes \
FROM    Surveyor.Times t \
WHERE   t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) AND \
	t.NodePairId = 1 \
GROUP BY        t.NodePairID, (t.start/60) \
), \
MinuteNonLossCount(NodePairID, Time, NonLoss) as \
( \
SELECT  t.NodePairID as NodePairID, \
        ((t.start/60) - (954633600/60)) as Time, \
        count(*) as NonLoss \
FROM    Surveyor.Times t \
WHERE   t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) AND \
        t.Delay != -1 AND \
	t.NodePairId = 1 \
GROUP BY        t.NodePairID, (t.start/60) \
), \
-- Lossy Minute
LossyMinute(NodePairId, Time) as \
( \
SELECT	nlc.NodePairID as "NodePairID", nlc.Time \
FROM	MinuteNonLossCount nlc, MinuteProbeCount pc \
WHERE	pc.NodePairId = nlc.NodePairId AND \
	pc.Time = nlc.Time AND \
	(pc.Probes - nlc.NonLoss) > 2 AND \
	((nlc.NonLoss*100)/pc.Probes + 1) <= 99 \
), \
-- Slow Minute
SlowMinute(NodePairId, Time, slowMin) as \
( \
SELECT	ps.NodePairId as NodePairId, \
	ps.Time as Time, \
	(ps.AvgDelay - dm.DayMin) as slowMin \
FROM	PathStats ps, DayMin dm \
) \
--SELECT	* \
--FROM	SlowMinute \
--ORDER BY	Time
--SELECT * \
--FROM	LossyMinute \
--ORDER BY	Time
SELECT	lm.NodePairId as "PathSpec", \
	lm.Time as "Lossy Minute", \
	sm.SlowMin as "Avg Delay - Min Delay" \
FROM	SlowMinute sm, LossyMinute lm \
WHERE	lm.Time = sm.Time AND \
	lm.NodePairId = sm.NodePairId \
ORDER BY	lm.NodePairId, lm.Time
