--Query 08: "Show map with loss percent yesterday used to color line segments"
-- For each src_dest pair, show % loss  between them in a given day and
-- show the long, lat for each src, dest.

--TotalProbes: Count the number of probes for a given day for all NodePairId

--ProbesLost: Count the number of probes lost in a given day for all NodepairId


WITH \
-- A Table of nodepairs with total probes in a day
TotalProbes(NodePairID, TotalProbes) as \
( \
SELECT	t.NodePairID as NodePairID, \
	count(*) as TotalProbes \
FROM	Surveyor.Times t \
WHERE	t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) \
GROUP BY	t.NodePairID \
), \
-- A Table of nodepairs and the num of probes lost in a day
ProbesLost(NodePairID, LossCount) as \
( \
SELECT	t.NodePairID as NodePairID, \
	count(*) as LossCount \
FROM	Surveyor.Times t \
WHERE	t.Delay = -1 AND \
	t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) \
GROUP BY	t.NodePairID \
) \
SELECT	tp.NodePairID as "PathSpec", \
        src.Lat as "Src Lat", src.Long as "Src Long", \
        dest.Lat as "Dest Lat", dest.Long as "Dest Long", \
	((pl.LossCount * 100) / tp.TotalProbes) as "Loss %" \ 
FROM    Surveyor.NodePairs np, Surveyor.Nodes src, Surveyor.Nodes dest, \
        TotalProbes tp, ProbesLost pl \
WHERE   tp.NodePairID = np.Id AND \
        np.SrcNodeId = src.NodeId AND \
        np.DestNodeId = dest.NodeId AND \
	tp.NodePairID = pl.NodePairID
	