SQL & PL/SQL

I have an sql that does some counts by hour for the last 24 hours. I wanted to change this to use for the last 4 hours but instead of 24 buckets I wanted 16 buckets of 15 minutes each.

For the last 4 hours in hour buckets seem to work fine. But for the last 4 hours in 16 buckets, doesn't work fine as it seems to give me the totals for the 1 bucket and repeats the same for the rest of the 15. Could some look let me know what I'm doing wrong here, thanks.

create table test(dat date, ct1 number, ct2 number);

declare
begin

FOR i IN 1 .. 16
LOOP
insert into test values((sysdate - 4/24)+i*1/96, i, i*2);
commit;
END LOOP;
end;
/

with dt_minmax as (SELECT trunc(sysdate-4/24,'hh24') AS min_dt,
               trunc(sysdate,'hh24') AS max_dt
          FROM DUAL),
     dense_days AS
     (SELECT     min_dt + LEVEL / 24 AS dt, min_dt, max_dt, level lvl
            FROM dt_minmax
      CONNECT BY LEVEL <=  4)
SELECT   dt AS period,
         SUM (s.ct1 + s.ct2) AS BYTES,
         COUNT (TRUNC (s.dat, 'HH24')) AS activity
    FROM dense_days dd LEFT OUTER JOIN test s ON dd.dt = TRUNC (s.dat, 'HH24')
GROUP BY dd.dt
ORDER BY dd.dt;

PERIOD                    BYTES   ACTIVITY
-------------------- ---------- ----------
15-Dec-2007 23:00:00         66          4
16-Dec-2007 00:00:00        114          4
16-Dec-2007 01:00:00        162          4
16-Dec-2007 02:00:00         48          1

-------------

with dt_minmax as (SELECT trunc(sysdate-4/24,'hh24') AS min_dt,
               trunc(sysdate,'hh24') AS max_dt
          FROM DUAL),
     dense_days AS
     (SELECT     min_dt + LEVEL / 96 AS dt, min_dt, max_dt, level lvl
            FROM dt_minmax
      CONNECT BY LEVEL <=  16)
SELECT   dt AS period,
         SUM (s.ct1 + s.ct2) AS BYTES,
         COUNT (TRUNC (s.dat, 'HH24')+lvl/96) AS activity
    FROM dense_days dd LEFT OUTER JOIN test s ON dd.dt = (TRUNC (s.dat, 'HH24')+lvl/96)
GROUP BY dd.dt
ORDER BY dd.dt;

PERIOD                    BYTES   ACTIVITY
-------------------- ---------- ----------
15-Dec-2007 22:15:00         18          3
15-Dec-2007 22:30:00         18          3
15-Dec-2007 22:45:00         18          3
15-Dec-2007 23:00:00         18          3
15-Dec-2007 23:15:00         18          3
15-Dec-2007 23:30:00         18          3
15-Dec-2007 23:45:00         18          3
16-Dec-2007 00:00:00         18          3
16-Dec-2007 00:15:00         18          3
16-Dec-2007 00:30:00         18          3
16-Dec-2007 00:45:00         18          3
16-Dec-2007 01:00:00         18          3
16-Dec-2007 01:15:00         18          3
16-Dec-2007 01:30:00         18          3
16-Dec-2007 01:45:00         18          3
16-Dec-2007 02:00:00         18          3

16 rows selected.