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.

I can give some snippet of a possible solution :

Q$10$ AS $($SELECT DV$\_$PATIENT$\_$ID$,$ TEST$\_$CODE, TEST$\_$NAME, ORDERING PROVIDER$\_$NPI$\_$NUMBER, PERFORMING ORGANIZATION$\_$NAME, RESULT$\_$CODE, RESULT$\_$NAME,

RESULT$\_$VALUE, RESULT$\_$COMMENTS, TEST$\_$DATE,

$--$ Add a column to calculate the date difference in days using LAG and DATEDIFF

DATEDIFF $($day$,$ LAG$($TEST$\_$DATE$)$ OVER $($PARTITION BY DV$\_$PATIENT$\_$ID$,$ TEST$\_$CODE, TEST$\_$NAME, ORDERING PROVIDER$\_$NPI$\_$NUMBER, RESULT$\_$CODE,

RESULT$\_$NAME, RESULT$\_$VALUE, RESULT$\_$COMMENTS ORDER BY TEST$\_$DATE$),$ TEST$\_$DATE$)$ AS DATE$\_$DIFF

FROM her$2\_$tests$)$

$-$Filter out the rows where the date difference is less than $35$ days

SELECT FROM Q$10$

WHERE DATE$\_$DIFF IS NULL OR DATE$\_$DIFF $>=35$

ORDER BY DV$\_$PATIENT$\_$ID$,$ TEST$\_$CODE, TEST$\_$NAME, ORDERING PROVIDER$\_$NPI$\_$NUMBER, PERFORMING ORGANIZATION$\_$NAME, RESULT$\_$CODE, RESULT$\_$NAME,

RESULT$\_$VALUE, RESULT$\_$COMMENTS, TEST$\_$DATE;

explanation:

LAG function to lag the test date over the partition by clause on on all the columns which make up the

duplicate values

CHECK for date difference $>=35,$ or NULL for the first case where no

$209\mathrm{.}6$ k to $96\mathrm{.}8$k rows after dedupliucation based on columns: DV$\_$PATIENT$\_$ID$,$ TEST$\_$CODE, TEST$\_$NAME,

ORDERING PROVIDER$\_$NPI$\_$NUMBER, RESULT$\_$CODE, RESULT$\_$NAME, RESULT$\_$VALUE,

RESULT$\_$COMMENTS.