Oracle SQL - Single table record Count by ID, Grouped by Week, including Nulls

Question

I am an experienced systems DBA, but it's been many years since I've dealt w the Applications/SQL side of things, so please pardon my ignorance here. My skills are a little rusty.

The Problem:

I have a single table that contains a list of Student ID's and the date each student submitted an assignment.

Table: STU_TEST_RESULTS

STUDENT_ID  RECEIVED_DATE
----------  -------------
0030        01/10/2022
0030        01/10/2022
0121        01/11/2022
0121        01/06/2022
0127        01/04/2022
0438        01/04/2022
0438        01/11/2022

( Note: This is a 3rd party product, I have no say in the table design. )

My Attempted Solution:

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')
;

Current Results:

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

Desired Results:

C_ID   C_YEAR  C_WEEK  C_COUNT
----   ------  ------  -------
0030   2022    01      0 ***
0030   2022    02      2
0121   2022    01      1
0121   2022    02      1
0127   2022    01      1
0127   2022    02      0 ***
0438   2022    01      1
0438   2022    02      1

I need to get those empty/null/blank/zero rows in the result set somehow too.

I feel like there needs to be some sort of self-join going on here, but don't know how to make that mesh with the "group by" clause.

Answer 1

From Oracle 12, you can use:

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 TO_CHAR(c.week, 'IYYY') AS c_year,
       TO_CHAR(c.week, 'IW')   AS c_week,
       student_id,
       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
       TO_CHAR(c.week, 'IYYY'),
       TO_CHAR(c.week, 'IW'),
       student_id;

db<>fiddle here

However, in Oracle 11g, recursive queries are bugged and although the syntax above is supported it won't generate the expected rows and you need to iterate differently:

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;

Which, for the sample data:

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;

Outputs:

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

db<>fiddle here