I have events of health records stored as key/value in a SQL table.
For example, the following table events.

PATIENT_ID TIMESTAMP    DOMAIN     KEY           VALUE
1           2021-01-01  biology     Hemoglobin  11
1           2014-02-05  diagnosis   ICD         J32
1           2021-01-05  diagnosis   ICD         J44
2           2021-05-05  biology     Iron        133 

From this table, I would like to select all patients with the following critera in a time window of 1 month :

biology:hemoglobin = '10' AND ( diagnosis:ICD ='J32' OR diagnosis:ICD = 'J44' ) 

Without considering the time window, I think I can convert the query above into the following SQL query :

    SELECT patient_id FROM events WHERE domain='biology' AND key='hemoglobin' AND value = '10' 
    INTERSECT 
    (
    SELECT patient_id FROM events WHERE domain='diagnosis' AND key='ICD' AND value = J32
    UNION 
    SELECT patient_id FROM events WHERE domain='diagnosis' AND key='ICD' AND value = J44
    )

But these conditions must be true only within a time window. I use Duckdb, I’ve seen that there’s a WINDOW function but I don’t know if it’s useful in this case.

Thanks