Sample Table

================================================================
id | user_id  | certificate_id  | is_retraining  | created_on
================================================================
1  | 1        |   1             | false          |  2021-01-01 
2  | 1        |   2             | false          |  2021-01-02 
3  | 2        |   1             | false          |  2021-01-03 
4  | 2        |   2             | false          |  2021-03-03 
5  | 3        |   1             | true           |  2021-10-10 
6  | 2        |   2             | true           |  2021-10-10

Above sample table consists Users info who completed certifications. An user can also go for retraining but he/she should have completed a course before retaking it.

User Id: 2 is retrained on certificate 2, he has a record in past (Completed same certificate earlier), but User id: 3 has no certificates in past but he directly choose retraining.

How can we identify users who went for retraining without having a certification (Particular certificate) in past?

Ideally im looking for below structure for all retrainings?

=============================================
user_id  | certificate_id  | has_past_records
==============================================
2        |   2             | true
3        |   1             | false 

CodePudding user response：

You could just use LAG() to find out the previous record's value.

If the previous record doesn't exist, LAG() will return NULL by default.

So, the following code identifies three possibilities...

1. Has never trained before
2. Is re-training following a previous re-training
3. Is re-training following a previous initial-training

WITH
  history AS
(
  SELECT
    *,
    LAG(is_retraining) OVER (PARTITION BY user_id, certificate_id
                                 ORDER BY created_on
                            )
                              AS previous_training_status
  FROM
    your_table
)
SELECT
  user_id,
  certificate_id,
  CASE
    WHEN previous_training_status IS NULL THEN 'never_previously_trained'
    WHEN previous_training_status = true  THEN 'previously_retrained'
                                          ELSE 'previously_trained'
  END
FROM
  history
WHERE
  is_retraining = true