How to use COUNT () OVER(Partition) along with where clause

Question

I want to get the count of claims for each NPI with filter condition. The sample query which I've is:

create or replace table Table2 as
select NPI, LAST_NAME, FIRST_NAME, ADDRESS, City, State, Zip, SPECIALTY_DESCRIPTION,
COUNT(DISTINCT CLAIM_ID) OVER (PARTITION BY NPI) AS TOTAL_CLAIMS,
COUNT(DISTINCT CLAIM_ID) OVER (PARTITION BY NPI) where APPROVAL_FLAG = 'APPROVED'  as APPROVED_CLAIMS,
COUNT(DISTINCT CLAIM_ID) OVER (PARTITION BY NPI) where APPROVAL_FLAG <> 'APPROVED'  as REJECTED_CLAIMS,
from Table1 ;

Please help me understand what is the correct way of doing this. Thank you!

Answer 1

The pattern is called conditional aggregation:

select NPI, LAST_NAME, FIRST_NAME, ADDRESS, City, State,
       Zip, SPECIALTY_DESCRIPTION,
COUNT(DISTINCT CLAIM_ID) OVER (PARTITION BY NPI) AS TOTAL_CLAIMS,
COUNT(DISTINCT CASE WHEN APPROVAL_FLAG = 'APPROVED' THEN CLAIM_ID END) 
      OVER (PARTITION BY NPI)  as APPROVED_CLAIMS,
COUNT(DISTINCT CASE WHEN APPROVAL_FLAG <> 'APPROVED' THEN  CLAIM_ID END) 
      OVER (PARTITION BY NPI) as REJECTED_CLAIMS
from Table1 ;