By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE STU_TEST_RESULTS (STUDENT_ID, RECEIVED_DATE) AS
SELECT '0030', DATE '2022-01-10' FROM DUAL UNION ALL
SELECT '0030', DATE '2022-01-10' FROM DUAL UNION ALL
SELECT '0121', DATE '2022-01-11' FROM DUAL UNION ALL
SELECT '0121', DATE '2022-01-06' FROM DUAL UNION ALL
SELECT '0127', DATE '2022-01-04' FROM DUAL UNION ALL
SELECT '0438', DATE '2022-01-04' FROM DUAL UNION ALL
SELECT '0438', DATE '2022-01-11' FROM DUAL;
7 rows affected
SELECT STUDENT_ID as c_ID,
to_char(RECEIVED_DATE,'IYYY') as c_YEAR,
to_char(RECEIVED_DATE,'IW') as c_WEEK,
count(*) as c_Count
FROM STU_TEST_RESULTS
GROUP BY
STUDENT_ID,
to_char(RECEIVED_DATE,'IYYY'),
to_char(RECEIVED_DATE,'IW')
ORDER BY
STUDENT_ID,
to_char(RECEIVED_DATE,'IYYY'),
to_char(RECEIVED_DATE,'IW');
| C_ID | C_YEAR | C_WEEK | C_COUNT |
|---|---|---|---|
| 0030 | 2022 | 02 | 2 |
| 0121 | 2022 | 01 | 1 |
| 0121 | 2022 | 02 | 1 |
| 0127 | 2022 | 01 | 1 |
| 0438 | 2022 | 01 | 1 |
| 0438 | 2022 | 02 | 1 |
WITH calendar (week, max_week) AS (
SELECT MIN(TRUNC(received_date, 'IW')),
MAX(TRUNC(received_date, 'IW'))
FROM stu_test_results
UNION ALL
SELECT week + INTERVAL '7' DAY,
max_week
FROM calendar
WHERE week < max_week
)
SELECT student_id,
TO_CHAR(c.week, 'IYYY') AS c_year,
TO_CHAR(c.week, 'IW') AS c_week,
COUNT(s.received_date) AS c_count
FROM calendar c
LEFT OUTER JOIN stu_test_results s
PARTITION BY (s.student_id)
ON (c.week <= s.received_date AND s.received_date < c.week + INTERVAL '7' DAY)
GROUP BY
student_id,
c.week
ORDER BY
student_id,
c.week;
| STUDENT_ID | C_YEAR | C_WEEK | C_COUNT |
|---|---|---|---|
| 0030 | 2022 | 01 | 0 |
| 0121 | 2022 | 01 | 1 |
| 0127 | 2022 | 01 | 1 |
| 0438 | 2022 | 01 | 1 |
WITH calendar (week) AS (
SELECT min_week + INTERVAL '7' DAY * (LEVEL - 1)
FROM (
SELECT TRUNC(MIN(received_date), 'IW') AS min_week,
TRUNC(MAX(received_date), 'IW') AS max_week
FROM stu_test_results
)
CONNECT BY LEVEL - 1 <= (max_week - min_week)/7
)
SELECT student_id,
TO_CHAR(c.week, 'IYYY') AS c_year,
TO_CHAR(c.week, 'IW') AS c_week,
c.week,
COUNT(s.received_date) AS c_count
FROM calendar c
LEFT OUTER JOIN stu_test_results s
PARTITION BY (s.student_id)
ON (c.week <= s.received_date AND s.received_date < c.week + INTERVAL '7' DAY)
GROUP BY
student_id,
c.week
ORDER BY
student_id,
c.week;
| STUDENT_ID | C_YEAR | C_WEEK | WEEK | C_COUNT |
|---|---|---|---|---|
| 0030 | 2022 | 01 | 03-JAN-22 | 0 |
| 0030 | 2022 | 02 | 10-JAN-22 | 2 |
| 0121 | 2022 | 01 | 03-JAN-22 | 1 |
| 0121 | 2022 | 02 | 10-JAN-22 | 1 |
| 0127 | 2022 | 01 | 03-JAN-22 | 1 |
| 0127 | 2022 | 02 | 10-JAN-22 | 0 |
| 0438 | 2022 | 01 | 03-JAN-22 | 1 |
| 0438 | 2022 | 02 | 10-JAN-22 | 1 |