Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (key, some_id, "DATE", class) AS
SELECT 1, 1, DATE '2000-01-01', 2 FROM DUAL UNION ALL
SELECT 1, 2, DATE '2000-01-01', 2 FROM DUAL UNION ALL
SELECT 2, 1, DATE '1999-01-01', 3 FROM DUAL;
3 rows affected

CREATE TABLE table2 (parameter_key, class_code) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 1, 3 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL;
4 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT t2.parameter_key AS key,
       t1."DATE",
       t2.class_code AS class,
       count(t1.class) as cnt 
FROM   table2 t2
       LEFT OUTER JOIN table1 t1
       PARTITION BY (t1."DATE")
       ON (t1.class = t2.class_code AND t1.key = t2.parameter_key)
WHERE  t2.parameter_key = 1
GROUP BY
       t2.parameter_key,
       t1."DATE",
       t2.class_code


KEY
DATE
CLASS
CNT


1
1999-01-01 00:00:00
1
0

1
1999-01-01 00:00:00
2
0

1
1999-01-01 00:00:00
3
0

1
2000-01-01 00:00:00
1
0

1
2000-01-01 00:00:00
2
2

1
2000-01-01 00:00:00
3
0


SELECT t1.key,
       t1."DATE",
       t2.class_code AS class,
       count(t1.class) as cnt 
FROM   table2 t2
       LEFT OUTER JOIN table1 t1
       PARTITION BY (t1.key, t1."DATE")
       ON (t1.class = t2.class_code)
WHERE  t2.parameter_key = 1
GROUP BY
       t1.key,
       t1."DATE",
       t2.class_code


KEY
DATE
CLASS
CNT


1
2000-01-01 00:00:00
1
0

1
2000-01-01 00:00:00
2
2

1
2000-01-01 00:00:00
3
0

2
1999-01-01 00:00:00
1
0

2
1999-01-01 00:00:00
2
0

2
1999-01-01 00:00:00
3
1

KEY	DATE	CLASS	CNT
1	1999-01-01 00:00:00	1	0
1	1999-01-01 00:00:00	2	0
1	1999-01-01 00:00:00	3	0
1	2000-01-01 00:00:00	1	0
1	2000-01-01 00:00:00	2	2
1	2000-01-01 00:00:00	3	0