Hi everyone,
I have some data like companyid, journeytype, ratingid and npsRating. With ratingid and npsRating, I usually calculate NPS (net promoting score) in beast mode like this:
(((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END )) - Count(DISTINCT (CASE WHEN (`npsrating` <= 6) THEN `ratingid` END ))) / count(DISTINCT `ratingid`)) * 100)
Now, my manager asked me to calculate NPS for each company, and then take the average over the NPS_percompany for each journeytype.
To solve this I know that I can put a window function and calculate NPS like this:
AVG((((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END )) - Count(DISTINCT (CASE WHEN (`npsrating` <= 6) THEN `ratingid` END ))) / count(DISTINCT `ratingid`)) * 100)) OVER (PARTITION BY `journeytype`)
as long as companyid exist in the groupby, the calculated amount for npe_per_journeytype is correct:
but as I wanna present it in a bar chart, I have to take out the companyId, and when it goes out from groupby, nps will be calculated for each journeytype, not per company and then take the average on journeytype:
What crossed my mind what that to have nested Beast mode as bellow:
AVG(AVG((((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END )) - Count(DISTINCT (CASE WHEN (`npsrating` <= 6) THEN `ratingid` END ))) / count(DISTINCT `ratingid`)) * 100)) OVER (PARTITION BY `companyid`, `journeytype` )) OVER (PARTITION BY `journeytype`)
but it does not work:
I cannot calculate this NPS in sql, because filters should apply on it on the fly, for example, companyType, filterdate, ... should be chose by the user.
Do you have any idea how I can tackle this problem?
