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?