--Query 10: "Plot number of unique PathSpecs congested each minute yesterday"

--MinuteNonLossCount: Find the number of probes in the qualified time that 
are
-- valid and group them by NodePairId, mins since the start of the day.

--MinuteProbeCount: Find the total number of probes in the qualified time and
-- group them by NodePairId, mins since the start of the day.

--MinuteInfo: Joins MinuteNonLossCount, MinuteProbeCount together to produce
-- a table of NodePairIds that have Total number of probes and valid probes.

WITH \
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) \
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 \
GROUP BY        t.NodePairID, (t.start/60) \
), \
MinuteInfo(NodePairID, Time, Probes, NonLoss) as \
( \
SELECT	nlc.NodePairID, \
	nlc.Time, \
	pc.Probes, \
	nlc.NonLoss \
FROM	MinuteProbeCount pc, MinuteNonLossCount nlc \
WHERE	nlc.NodePairID = pc.NodePairID AND \
	nlc.Time = pc.Time \
) \
SELECT  nlc.Time as "Mintues Since Midnight", \
        count(Distinct nlc.NodePairID) as "Num of congested PathSpec" \
FROM    MinuteNonLossCount nlc, MinuteProbeCount pc \
WHERE	nlc.NodePairID = pc.NodePairID AND \
	nlc.Time = pc.Time AND \
        (pc.Probes - nlc.NonLoss) > 2 AND \
        ((nlc.NonLoss*100)/pc.Probes + 1) <= 99 \
--	((nlc.NonLoss*100)/pc.Probes + 1) <= 98 \
GROUP BY        nlc.Time
