Postgresql Postgres 13.8 如何在对数据执行窗口操作时返回所有行

请建议如何在Postgres 13.8中实现以下要求，同时返回原始数据的所有行:

CREATE TABLE exam_assessment (
    subject_name varchar(50),
    chapter_name varchar(50),
    chapter_status varchar(50),
    student_id varchar(50),
    marked_status varchar(50));

INSERT INTO exam_assessment 
(subject_name,chapter_name,chapter_status,student_id,marked_status) VALUES
('Science','Chapter1','COMPLETED','6998104_1','Marked as Complete'),
('Science','Chapter2','COMPLETED','6998104_1','Completed'),
('Science','Chapter3','IN_PROGRESS','6998104_1','IN_PROGRESS'),
('Science','Chapter1','COMPLETED','6998103_1','Marked as Complete'),
('Science','Chapter2','COMPLETED','6998103_1','Marked as Complete'),
('Science','Chapter3','COMPLETED','6998103_1','Marked as Complete'),
('Science','Chapter1','COMPLETED','6998102_1','Completed'),
('Science','Chapter2','COMPLETED','6998102_1','Marked as Complete'),
('Science','Chapter3','COMPLETED','6998102_1','Marked as Complete');

Data看起来像这样:

objective是为了获得学生的整体科目状态:

• 如果任一章节为IN_PROGRESS，则表示主体状态为IN PROGRESS.
• 如果所有章节都是Marked as Completed，则主题状态应该是Marked as Completed.
• 如果所有章节都是Marked as Completed和Completed的组合，则意味着主体状态应该是Completed.

Examples:

• 学生6998IN_PROGRESS_1有3章中的marked_status章，IN_PROGRESS-SUBJECT_STATUS应该显示为IN_PROGRESS.
• 学生6998SUBJECT_STATUS_1在所有章节中都有marked_status，因为Marked as Complete-SUBJECT_STATUS应该显示Marked as Complete.
• 学生6998COMPLETED_1的3章chapter_status都是COMPLETED，但有些marked_status是Marked as Complete，有些Completed-SUBJECT_STATUS栏应该是Completed.

Expected result:个