--Query 04: "Show table with number of sessions for each src/dest pair"

--SessionCount: Generate a table with the number of sessions in a day for each
-- NodePairId

WITH \
SessionCount(NodePairId, NumSessions) as \
( \
SELECT	t.NodePairId, \
	count (DISTINCT t.RID) \
FROM	Surveyor.Times t \
WHERE	t.RID != 0 \
GROUP BY	t.NodePairID \
) \
SELECT	sc.NodePairId as "NodePairId", \
	src.Name as "Source", \
	dest.Name as "Destination", \
	sc.NumSessions as "Num of Sessions" \
FROM	SessionCount sc, Surveyor.Nodes src, Surveyor.Nodes dest, \
	Surveyor.NodePairs np \
WHERE	sc.NodePairId = np.Id AND \
	np.SrcNodeId = src.NodeId AND \
	np.DestNodeId = dest.NodeId \
ORDER BY	sc.NodePairId
