Dynamic/Customized Week Number

Let us say there is a table which stores data for student’s attendance for classes. The student only need to attend each class once weekly. Hinging on the days_diff column, we decide which weeks the student should get credited for. As usual, each week has 7 days and the end of the week (in days) is divisible by 7. There is a different case for Week 3 in my scenario, whenever the week_3 is attended, the week ends at that attendance date and week_4 is started the next day.

I am using postgreSQL 11.4!

select * from students_data

student_name	class_subject	registration_date	attendance_date	days_diff	week_num
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

Desired Week Breakdown:

student    days_in_week      week_number
Frank         1-7               week_1
Frank         8-14              week_2
Frank        15-17              week_3     <-----week_3 shakes up the week breakdown
Frank        18-24              week_4
Frank        25-31              week_5........
Danny         1-7               week_1
Danny         8-14              week_2
Danny        15-19              week_3     <----week_3 shakes up the week breakdown
Danny        20-26              week_4
Danny        27-33              week_5........

Requested: new_week_num column

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_7
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

Answer

Solution 1 : cte and JOIN

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

Solution 2 : LATERAL JOIN

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

Solution 3 : window function

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)

test in dbfiddle