Postgres 14

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

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


with table_a (exam_month, category, "# exempt students", "mean score (excl. exempt students")
as (
 values 
  ('2022-01-01 12:00:00'::timestamp, 'A', 2, 61.8),
  ('2022-02-01 12:00:00'::timestamp, 'A', 1, 63.1),
  ('2022-03-01 12:00:00'::timestamp, 'A', 2, 61.1),
  ('2022-01-01 12:00:00'::timestamp, 'B', 4, 73.8),
  ('2022-02-01 12:00:00'::timestamp, 'B', 3, 71.3),
  ('2022-03-01 12:00:00'::timestamp, 'B', 2, 72.5)
), table_b (student_id, exam_timestamp, category, score, student_status)
as (
 values
  (1, '2022-01-10 09:30:00'::timestamp, 'A', 13.1, 'exempt'),
  (2, '2022-01-12 12:00:00'::timestamp, 'A', 63.7, 'examined'),
  (3, '2022-01-14 09:00:00'::timestamp, 'A', 59.2, 'examined'),
  (4, '2022-01-16 14:30:00'::timestamp, 'A', 62.8, 'examined'),
  (5, '2022-01-18 12:30:00'::timestamp, 'A', 41.2, 'exempt'),
  (6, '2022-01-20 12:00:00'::timestamp, 'A', 61.5, 'examined')
)
select b.student_id, b.exam_timestamp, b.category, 
       case 
         when b.student_status = 'exempt' then a."mean score (excl. exempt students"
         else b.score
       end as score,
       b.student_status
  from table_b b
       join table_a a
         on a.category = b.category
        and date_trunc('month', b.exam_timestamp)::date = a.exam_month::date;



student_id
exam_timestamp
category
score
student_status


1
2022-01-10 09:30:00
A
61.8
exempt

2
2022-01-12 12:00:00
A
63.7
examined

3
2022-01-14 09:00:00
A
59.2
examined

4
2022-01-16 14:30:00
A
62.8
examined

5
2022-01-18 12:30:00
A
61.8
exempt

6
2022-01-20 12:00:00
A
61.5
examined


with table_b (student_id, exam_timestamp, category, score, student_status)
as (
 values
  (1, '2022-01-10 09:30:00'::timestamp, 'A', 13.1, 'exempt'),
  (2, '2022-01-12 12:00:00'::timestamp, 'A', 63.7, 'examined'),
  (3, '2022-01-14 09:00:00'::timestamp, 'A', 59.2, 'examined'),
  (4, '2022-01-16 14:30:00'::timestamp, 'A', 62.8, 'examined'),
  (5, '2022-01-18 12:30:00'::timestamp, 'A', 41.2, 'exempt'),
  (6, '2022-01-20 12:00:00'::timestamp, 'A', 61.5, 'examined')
)
select b.student_id, b.exam_timestamp, b.category, 
       (case 
         when b.student_status = 'exempt' 
           then sum(case
                      when b.student_status = 'exempt' then 0
                      else b.score
                    end) over w
                / 
                sum((b.student_status = 'examined')::int) over w
         else b.score
       end)::numeric(4,1) as score,
       b.student_status
  from table_b b
window w as (partition by date_trunc('month', b.exam_timestamp), category)
;


student_id
exam_timestamp
category
score
student_status


1
2022-01-10 09:30:00
A
61.8
exempt

2
2022-01-12 12:00:00
A
63.7
examined

3
2022-01-14 09:00:00
A
59.2
examined

4
2022-01-16 14:30:00
A
62.8
examined

5
2022-01-18 12:30:00
A
61.8
exempt

6
2022-01-20 12:00:00
A
61.5
examined

student_id	exam_timestamp	category	score	student_status
1	2022-01-10 09:30:00	A	61.8	exempt
2	2022-01-12 12:00:00	A	63.7	examined
3	2022-01-14 09:00:00	A	59.2	examined
4	2022-01-16 14:30:00	A	62.8	examined
5	2022-01-18 12:30:00	A	61.8	exempt
6	2022-01-20 12:00:00	A	61.5	examined