--Query 03: "Map number of lossy minutes in a day by latitude/longitude"
-- Lossy Minutes are defined as (LossCount > 2 and PctLoss > 1)

--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.

--LossyMinCount: Compute the number of lossy minutes for each nodepairs.

--NOTE: MinuteNonLossCount and MinuteProbeCount are done seperately because I 
-- couldnt find a way to do it as a table efficiently.

WITH \
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.Delay != -1 AND \
	t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) \
GROUP BY	t.NodePairID, (t.start/60) \
), \
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) \
), \
LossyMinCount(NodePairID, NumLossMin) as \
( \
SELECT	nlc.NodePairID, \
	count(*) as NumLossMin \
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 \
GROUP BY	nlc.NodePairID \
) \
SELECT	lmc.NodePairID as "PathSpec", \
	src.Lat as "Src Lat", \
	src.Long as "Src Long", \
	dest.Lat as "Dest Lat", \
	dest.Long as "Dest Long", \
	lmc.NumLossMin as "Num Loss Min" \
FROM	LossyMinCount lmc, Surveyor.Nodes src, Surveyor.Nodes dest, \
	Surveyor.NodePairs np \
WHERE	lmc.NodePairId = np.Id AND \
	np.SrcNodeId = src.NodeId AND \
	np.DestNodeId = dest.NodeId \
ORDER BY	lmc.NodePairID
