Label when a New Class Starts-CodePudding

I have this table which records when a person took driving lessons and practiced at home. I have this scenario, whenever the class_practice column has Simulation for the value and input_value column has a Yes for it then Test_1 kicks off for that date. Subsequently, Simulation field is replaced with a Class value in the table. When that Class has a Yes then Test_2 kicks off and then another Yes means Test_3 and so on and so forth, Test_4, Test_5, etc....

How can I achieve this in postgreSQL 11.

select * from driving_table

student_name      class_practice     input_value       class_practice_date      
    Erin            Practice              Yes               2021-12-15      
    Erin            Practice              Yes               2021-12-16
    Erin           Simulation             No                2021-12-16
    Erin            Practice              Yes               2021-12-17
    Erin           Simulation             Yes               2021-12-17
    Erin            Practice              No                2021-12-18
    Erin             Class                No                2021-12-18
    Erin            Practice              No                2021-12-19
    Erin             Class                No                2021-12-19
    Erin            Practice              No                2021-12-20
    Erin             Class                Yes               2021-12-20
    Erin            Practice              No                2021-12-21
    Erin             Class                No                2021-12-21
    Erin            Practice              Yes               2021-12-21
    Erin             Class                No                2021-12-21
    Erin            Practice              No                2021-12-22
    Erin             Class                Yes               2021-12-22
    Erin            Practice              No                2021-12-23
    Erin             Class                No                2021-12-23
    Erin            Practice              No                2021-12-24
    Erin             Class                No                2021-12-24
    Danny           Practice              Yes               2021-12-15
    Danny           Practice              Yes               2021-12-16
    Danny          Simulation             No                2021-12-16
    Danny           Practice              Yes               2021-12-17
    Danny          Simulation             Yes               2021-12-17
    Danny           Practice              No                2021-12-18
    Danny            Class                Yes               2021-12-18
    Danny           Practice              No                2021-12-19 
    Danny            Class                No                2021-12-19
    Danny           Practice              No                2021-12-20
    Danny            Class                Yes               2021-12-20
    Danny           Practice              No                2021-12-21
    Danny            Class                No                2021-12-21
    Danny           Practice              Yes               2021-12-21
    Danny            Class                No                2021-12-21
    Danny           Practice              No                2021-12-22
    Danny            Class                Yes               2021-12-22
    Danny           Practice              No                2021-12-23
    Danny            Class                No                2021-12-23
    Danny           Practice              No                2021-12-24
    Danny            Class                No                2021-12-24

Requested Result:

student_name      class_practice     input_value       class_practice_date     test_simulation    
    Erin            Practice              Yes               2021-12-15               
    Erin            Practice              Yes               2021-12-16
    Erin           Simulation             No                2021-12-16
    Erin            Practice              Yes               2021-12-17            Test_1
    Erin           Simulation             Yes               2021-12-17            Test_1
    Erin            Practice              No                2021-12-18            Test_1
    Erin             Class                No                2021-12-18            Test_1
    Erin            Practice              No                2021-12-19            Test_1
    Erin             Class                No                2021-12-19            Test_1
    Erin            Practice              No                2021-12-20            Test_2         
    Erin             Class                Yes               2021-12-20            Test_2
    Erin            Practice              No                2021-12-21            Test_2
    Erin             Class                No                2021-12-21            Test_2
    Erin            Practice              Yes               2021-12-21            Test_2
    Erin             Class                No                2021-12-21            Test_2
    Erin            Practice              No                2021-12-22            Test_3
    Erin             Class                Yes               2021-12-22            Test_3 
    Erin            Practice              No                2021-12-23            Test_3
    Erin             Class                No                2021-12-23            Test_3
    Erin            Practice              No                2021-12-24            Test_3
    Erin             Class                No                2021-12-24            Test_3
    Danny           Practice              Yes               2021-12-15            
    Danny           Practice              Yes               2021-12-16            
    Danny          Simulation             No                2021-12-16
    Danny           Practice              Yes               2021-12-17            Test_1
    Danny          Simulation             Yes               2021-12-17            Test_1
    Danny           Practice              No                2021-12-18            Test_1
    Danny            Class                No                2021-12-18            Test_1
    Danny           Practice              No                2021-12-19            Test_1
    Danny            Class                No                2021-12-19            Test_1
    Danny           Practice              No                2021-12-20            Test_2
    Danny            Class                Yes               2021-12-20            Test_2
    Danny           Practice              No                2021-12-21            Test_2
    Danny            Class                No                2021-12-21            Test_2
    Danny           Practice              Yes               2021-12-21            Test_2
    Danny            Class                No                2021-12-21            Test_2
    Danny           Practice              No                2021-12-22            Test_3          
    Danny            Class                Yes               2021-12-22            Test_3          
    Danny           Practice              No                2021-12-23            Test_3          
    Danny            Class                No                2021-12-23            Test_3          
    Danny           Practice              No                2021-12-24            Test_3          
    Danny            Class                No                2021-12-24            Test_3

CodePudding user response：

select
    coalesce('Test_' || nullif(
        count(*)
          filter (where class_practice in ('Simulation', 'Class') and input_value = 'Yes')
          over (partition by student_name order by class_practice_date),
        0)::varchar(3), '')
from driving_table