SELECT b.id "userid" , COUNT(*) "count" FROM
(SELECT r,
id,
(CASE
WHEN ((SELECT DA
from (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC) te1
WHERE te1.r = a.r+1) -
nvl((SELECT DA
from (SELECT rownum r, test11.*
FROM test11
ORDER BY da DESC) te2
WHERE te2.r = a.r ),
sysdate) > 1) THEN
0
ELSE
1
END) cu
FROM (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC)a ) b
WHERE b.cu = 1 AND b.r > ( SELECT MAX(c.r) FROM (SELECT r,
id,
(CASE
WHEN ((SELECT DA
from (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC) te1
WHERE te1.r = a.r+1) -
nvl((SELECT DA
from (SELECT rownum r, test11.*
FROM test11
ORDER BY da DESC) te2
WHERE te2.r = a.r ),
sysdate) > 1) THEN
0
ELSE
1
END) cu
FROM (SELECT rownum r, test11.* FROM test11 ORDER BY da DESC)a ) c where c.id = b.id GROUP BY c.cu HAVING c.cu = 0 ) GROUP BY b.id