Postgres 14

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table your_table (student_name text, class_subject text,registration_date date,attendance_date date,days_diff integer,week_num text, constraint pk primary key (student_name, class_subject,registration_date,attendance_date)) ;

insert into your_table values
('Frank',	'Chemistry',	'2021-10-15',	'2021-10-18',	3,	'week_1'),
('Frank',	'Chemistry',	'2021-10-15',	'2021-10-24',	9,	'week_2'),
('Frank',	'Chemistry',	'2021-10-15',	'2021-11-01',	17,	'week_3'),
('Frank',	'Chemistry',	'2021-10-15',	'2021-11-10',	26,	'week_4'),
('Frank',	'Chemistry',	'2021-10-15',	'2021-11-20',	36,	'week_6'),
('Danny',	'Physics',	'2021-10-20',	'2021-10-25',	5,	'week_1'),
('Danny',	'Physics',	'2021-10-20',	'2021-11-02',	13,	'week_2'),
('Danny',	'Physics',	'2021-10-20',	'2021-11-08',	19,	'week_3'),
('Danny',	'Physics',	'2021-10-20',	'2021-11-23',	34,	'week_5'),
('Danny',	'Physics',	'2021-10-20',	'2021-11-30',	41,	'week_6')
10 rows affected

    WITH list AS
    (
    SELECT student_name, class_subject, registration_date, days_diff
      FROM your_table
     WHERE week_num = 'week_3'
    )
    SELECT t.*
         , CASE 
             WHEN t.days_diff <= l.days_diff
             THEN t.week_num
             ELSE 'week_' || (3 + ceil((t.days_diff - l.days_diff)/7.0)) :: text
           END AS new_week_num
      FROM your_table AS t
      LEFT JOIN list AS l
        ON t.student_name = l.student_name
       AND t.class_subject = l.class_subject
       AND t.registration_date = l.registration_date


student_name
class_subject
registration_date
attendance_date
days_diff
week_num
new_week_num


Frank
Chemistry
2021-10-15
2021-10-18
3
week_1
week_1

Frank
Chemistry
2021-10-15
2021-10-24
9
week_2
week_2

Frank
Chemistry
2021-10-15
2021-11-01
17
week_3
week_3

Frank
Chemistry
2021-10-15
2021-11-10
26
week_4
week_5

Frank
Chemistry
2021-10-15
2021-11-20
36
week_6
week_6

Danny
Physics
2021-10-20
2021-10-25
5
week_1
week_1

Danny
Physics
2021-10-20
2021-11-02
13
week_2
week_2

Danny
Physics
2021-10-20
2021-11-08
19
week_3
week_3

Danny
Physics
2021-10-20
2021-11-23
34
week_5
week_6

Danny
Physics
2021-10-20
2021-11-30
41
week_6
week_7


    SELECT t.*
         , CASE 
             WHEN t.days_diff <= l.days_diff
             THEN t.week_num
             ELSE 'week_' || (3 + ceil((t.days_diff - l.days_diff)/7.0)) :: text
           END AS new_week_num
      FROM your_table AS t
     CROSS JOIN LATERAL
         ( SELECT l.days_diff
             FROM your_table AS l
            WHERE l.student_name = t.student_name
             AND l.class_subject = t.class_subject
             AND l.registration_date = t.registration_date
             AND l.week_num = 'week_3'
           LIMIT 1
         ) AS l


student_name
class_subject
registration_date
attendance_date
days_diff
week_num
new_week_num


Frank
Chemistry
2021-10-15
2021-10-18
3
week_1
week_1

Frank
Chemistry
2021-10-15
2021-10-24
9
week_2
week_2

Frank
Chemistry
2021-10-15
2021-11-01
17
week_3
week_3

Frank
Chemistry
2021-10-15
2021-11-10
26
week_4
week_5

Frank
Chemistry
2021-10-15
2021-11-20
36
week_6
week_6

Danny
Physics
2021-10-20
2021-10-25
5
week_1
week_1

Danny
Physics
2021-10-20
2021-11-02
13
week_2
week_2

Danny
Physics
2021-10-20
2021-11-08
19
week_3
week_3

Danny
Physics
2021-10-20
2021-11-23
34
week_5
week_6

Danny
Physics
2021-10-20
2021-11-30
41
week_6
week_7


    SELECT t.*
         , CASE 
             WHEN days_diff <= (array_agg(days_diff) FILTER (WHERE week_num = 'week_3') OVER w)[1]
             THEN week_num
             ELSE 'week_' || (3 + ceil((t.days_diff - (array_agg(days_diff) FILTER (WHERE week_num = 'week_3') OVER w)[1])/7.0)) :: text
           END AS new_week_num
      FROM your_table AS t
    WINDOW w AS (PARTITION BY student_name, class_subject, registration_date ORDER BY days_diff ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)


student_name
class_subject
registration_date
attendance_date
days_diff
week_num
new_week_num


Danny
Physics
2021-10-20
2021-10-25
5
week_1
week_1

Danny
Physics
2021-10-20
2021-11-02
13
week_2
week_2

Danny
Physics
2021-10-20
2021-11-08
19
week_3
week_3

Danny
Physics
2021-10-20
2021-11-23
34
week_5
week_6

Danny
Physics
2021-10-20
2021-11-30
41
week_6
week_7

Frank
Chemistry
2021-10-15
2021-10-18
3
week_1
week_1

Frank
Chemistry
2021-10-15
2021-10-24
9
week_2
week_2

Frank
Chemistry
2021-10-15
2021-11-01
17
week_3
week_3

Frank
Chemistry
2021-10-15
2021-11-10
26
week_4
week_5

Frank
Chemistry
2021-10-15
2021-11-20
36
week_6
week_6

student_name	class_subject	registration_date	attendance_date	days_diff	week_num	new_week_num
Frank	Chemistry	2021-10-15	2021-10-18	3	week_1	week_1
Frank	Chemistry	2021-10-15	2021-10-24	9	week_2	week_2
Frank	Chemistry	2021-10-15	2021-11-01	17	week_3	week_3
Frank	Chemistry	2021-10-15	2021-11-10	26	week_4	week_5
Frank	Chemistry	2021-10-15	2021-11-20	36	week_6	week_6
Danny	Physics	2021-10-20	2021-10-25	5	week_1	week_1
Danny	Physics	2021-10-20	2021-11-02	13	week_2	week_2
Danny	Physics	2021-10-20	2021-11-08	19	week_3	week_3
Danny	Physics	2021-10-20	2021-11-23	34	week_5	week_6
Danny	Physics	2021-10-20	2021-11-30	41	week_6	week_7