--Query 06: "Plot delays by time of day, overlaying many days (for one
-- PathSpec for one month)".

--An extension of query 01 where a month's worth of times data for a PathSpec
-- is collected and then analysed.

WITH \
--Get times data for the entire month for a pathspec
RevisedDelay(Time, Delay, RID, Total) as \
( \
SELECT  ((t.start/60) - (954547200/60)) as Time, \
	nullif(t.Delay, -1) as Delay, \
        t.Rid as RID, \
        count(*) over (partition by t.start/60) as Total \
FROM    Surveyor.Times t \
WHERE   t.start BETWEEN (954547200) AND (957139200 - 1) AND \
        t.NodePairId = 1 \
), \
RankedTimes(Time, Delay, RID, Rank, Total) as \
( \
SELECT  rd.Time, \
        rd.Delay, \
        rd.Rid, \
        row_number() over (partition by rd.Time order by rd.Delay asc) \
        as Rank, \
        rd.Total \
FROM    RevisedDelay rd \
), \
MinuteSummary(Mins_Since_Midnight, Min_Delay, Pct50, Pct90, \
	LossCount, ProbeCount) as \
( \
SELECT 	rt.time as Mins_Since_Midnight, \
        min(rt.delay) as Min_Delay, \
        p1.delay as Pct50, \
        p2.delay as Pct90, \
        (SELECT count(*) \
        FROM    RankedTimes q \
        WHERE   rt.time = q.time AND \
                q.delay is null \
        ) as LossCount, \
        count(*) as ProbeCount \
FROM    RankedTimes rt, RankedTimes p1, RankedTimes p2 \
WHERE   p1.rank = ((50 * p1.Total)/100 + 1) AND \
        p1.time = rt.time AND \
        p2.rank = ((90 * p2.Total)/100 + 1) AND \
        p2.time = rt.time \
GROUP BY        rt.time, p1.delay, p2.delay \
) \
SELECT	(ms.Mins_Since_Midnight/1440) as "Day of Month", \
	ms.Mins_Since_Midnight - ((ms.Mins_Since_Midnight/1440) * 1440) \
	as "Mins Since Midnight", \
        ms.Min_Delay as "Min Delay", \
        ms.Pct50 as "50%ile", \
        ms.Pct90 as "90%ile" \
FROM    MinuteSummary ms \
ORDER BY        ms.Mins_Since_Midnight
