In snowflake$$s web interface, Create a worksheet that selects data from the medical labs table

DXRX$\_$DATA$\_$PROD.MEDICAL$\_$LABS.PATIENT$\_$PROCEDURES. From this data table we would like to select columns:

$$DV$\_$PATIENT$\_$ID

$$TEST$\_$ORDER$\_$ID

$$ACCESSION$\_$NUMBER

$$REFERRING$\_$PROVIDER$\_$NPI$\_$NUMBER

$$ORDERING$\_$PROVIDER$\_$NPI$\_$NUMBER

$$TEST$\_$ORDERED$\_$DATE

$$TEST$\_$REPORTED$\_$DATE

$$PERFORMING$\_$ORGANIZATION$\_$NAME

$$PANEL$\_$NAME

$$TEST$\_$NAME

$$TEST$\_$CODE

$$RESULT$\_$NAME

$$RESULT$\_$CODE

$$RESULT$\_$VALUE

$$RESULT$\_$COMMENTS.

$1.$We want to filter for rows that contain HER$2$or HER$-2$in the results fields for tests reported

in $2023.$This filter infers that the patients have been tested for the biomarker HER$2.$In cases

where there is no test report date, please make use of the ordered date. This filter should be

used in the further activities. Create a new column that selects test$\_$order$\_$id as default, if

missing then uses the accession number and name the column as TEST$\_$ID$.$

The solution for this question is :

$$WITH her$2\_$patients AS$($

$$ SELECT

$$ DV$\_$PATIENT$\_$ID$,$TEST$\_$ORDER$\_$ID$,$ACCESSION$\_$NUMBER,REFERRING$\_$PROVIDER$\_$NPI$\_$NUMBER,

$$ ORDERING$\_$PROVIDER$\_$NPI$\_$NUMBER,TEST$\_$ORDERED$\_$DATE,TEST$\_$REPORT$\_$DATE,

$$ PERFORMING$\_$ORGANIZATION$\_$NAME,PANEL$\_$NAME,TEST$\_$NAME,TEST$\_$CODE,

$$ RESULT$\_$NAME,RESULT$\_$CODE,RESULT$\_$VALUE,RESULT$\_$COMMENTS,

$$ COALESCE $($TEST$\_$ORDER$\_$ID$,$ACCESSION$\_$NUMBER$)$AS TEST$\_$ID$,$

$$ COALESCE $($TEST$\_$ORDERED$\_$DATE,TEST$\_$REPORT$\_$DATE$)$AS TEST$\_$DATE

FROM DXRX$\_$DATA$\_$PROD.MEDICAL$\_$LABS.PATIENT$\_$PROCEDURES

WHERE

$($

$$ REGEXP$\_$LIKE$($RESULT$\_$NAME, $\text{'}.*$HER$-?2.*|.*$ERBB$-?2.*\text{'},\text{'}$i$\text{'})$OR

$$ REGEXP$\_$LIKE$($RESULT$\_$VALUE, $\text{'}.*$HER$-?2.*|.*$ERBB$-?2.*\text{'},\text{'}$i$\text{'})$OR

$$ REGEXP$\_$LIKE$($RESULT$\_$COMMENTS, $\text{'}.*$HER$-?2.*|.*$ERBB$-?2.*\text{'},\text{'}$i$\text{'})$

$)$

$$ AND

$($

$$ TEST$\_$DATE LIKE $\text{'}2023\%\text{'}$

$)$

$)$

$$ SELECT $*$

$$ FROM her$2\_$patients;

From the data retrieved in exercise $1,$you will have the same test results reported over

multiple dates and sometimes performing organisation names. This duplication happens in a

system were the same result maybe captured multiple times and seems to cluster within

~$35$days periods. How would we collapse this data to show $1$record where the results are

the same? Please explain the approach in bullet points and show in SQL$.$Please show $3$

examples of before and after your deduplication process for individual patients results.

a$)$The question is done on Snowflake SQL Worksheet. So the SQL query should be compatible to it$.$

b$)$Please explain how the question works and also the logic behind the question?

c$)$Please give me a solution and the explanation for this question as well.