I have a queryset that pulls data that looks like this:
| condition | review | insert_datetime | outcome |
|---|---|---|---|
| C1 | 1 | 05/29/2024 | 2 |
| C1 | 1 | 05/30/2024 | 1 |
| C1 | 2 | 05/29/2024 | 2 |
| C2 | 3 | 05/29/2024 | 2 |
| C2 | 4 | 05/29/2024 | 3 |
| C2 | 4 | 05/30/2024 | 0 |
I need to filter this queryset following this logic:
- For each review, find the record with latest insert_datetime
- Start with the records from step 1 and for each condition find the record with the lowest outcome.
| condition | review | insert_datetime | outcome | Step1 | Step2 |
|---|---|---|---|---|---|
| C1 | 1 | 05/29/2024 | 2 | ||
| C1 | 1 | 05/30/2024 | 1 | PICK | PICK |
| C1 | 2 | 05/29/2024 | 2 | PICK | |
| C2 | 3 | 05/29/2024 | 2 | PICK | |
| C2 | 4 | 05/29/2024 | 3 | ||
| C2 | 4 | 05/30/2024 | 0 | PICK | PICK |
The final queryset should contain just the two records picked in step 2.
I tried chaining window functions, but did not get expected results. I would appreciate help.
Thank you.