pigsty | customizable spooler for the unified2 file format | Runtime Evironment library

I’m writing a query against a table of fictional insurance clams called CLAIMS, using RANDOMLY GENERATED FICTIONAL NAMES AND DATA.

There are 5 distinct categories in the column called PRIMARY_DX: Alcoholism, Anxiety Disorder, Depression, Psychosis, Substance Use Disorder

The other main columns are PATIENT_ID and CLAIM_PAID_AMT

I want to sum up the CLAIM_PAID_AMT per PATIENT per PRIMARY_DX and list only the top 5 patients who have the highest sum per PRIMARY_DX

The only way I could think to do this was with two Common Table Expressions, where in CTE1 I partition by PRIMARY_DX and PATIENT_ID and SUM the CLAIM_PAID_AMT for each PATIENT.

Then in CTE2 I use a ROW_NUMBER function on CTE1, to partition by PRIMARY_DX and sort by the TotalClaims DESC and select the top 5 from each PRIMARY_DX.

I’ve been writing SQL for less than 2 years and was wondering if this could be accomplished in one CTE or perhaps with some form of Cross Apply?

I’m including my code and the output below.