So we have a data set called Cases.

This data set has many records with same case id(duplicates) but different service frequency, for example, yearly, monthly, quarterly.

Case table data

Case_Id    Service_Frequency
123        Monthly
123        Yearly
345        Quarterly

So here comes the requirement, calculate the percentage of # of cases with different frequency against total # of unique cases.

By referring above table, the result should be grouped by service fequency

Service_Frequency   #_of_case  #total_uniqie_case  Percentage
Monthly             1          2                   50%
Quarterly           1          2                   50%
Yearly              1          2                   50%

By using compare table, I can successfully get # of case with each Service frequency, but I couldn't find way to work out sum of unique cases by using running total or sliding windowing function, Compare table in Einstein:

Service_frequency    #_of_cases        Total_count
Monthly              1                 3
Quarterly            1                 3
Yearly               1                 3

Total count windowing function: (sum(sum(#_of_cases)) over ([..] partition by all ) as 'Total_count')

Apparently, 3 is not I am working for, I need the number of total unique case based on case id, which 2. Is there a way to achieve this in compare table?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.