Suggestion on calculation


#1

Hi,

I am trying to get the adherence to the target metric from my data in Metabase, and I would like to know if anyone can suggest the best way to do it.

This is the scenario: I have a set of countries and cities. Each city has a score from 1 to 10. The question is: for each country, what is the adherence to the target? The target is 8.

The answer should say what is the percentage of cities in a given country that had score 8 or more.

Thanks!


#2

Hi @fabiolanza
Without knowing your structure, it’s a bit difficult, but maybe something like this (for MariaDB/MySQL, might need to adjust it slightly for other SQL types):

SELECT `countries`.`name`, (100 / NULLIF(COUNT(*),0) * SUM(IF(`cities`.`rating`>=8,1,0))) AS country_rating
FROM `countries`
LEFT JOIN `cities` ON `cities`.`country`=`countries`.`name`
GROUP BY `countries`.`name`

#3

Hi @flamber, first of all thanks for getting to my questions, I see that it’s not the first one that you supporting.

I used the analogy of country and city because it was something easy and similar. However, in order to give you more details let me share the real structure, as I tried to do as you said and did not have success.

The view has the following fields:

TTR is the metric (time to resolution). I need to know the adherence to the TTR target for each DL (distribution list), grouped by year/month of CLOSE DATE and DL_COUNTRY_CODE.

This is why I have so far, but it’s not working as TOTAL is returning with the same values as WITHIN_TARGET - please see the query below:

> SELECT v1."DL", v1."DL_COUNTRY_CODE", EXTRACT(YEAR FROM v1."CLOSE_DATE") AS "YEAR", EXTRACT(MONTH FROM v1."CLOSE_DATE") AS "MONTH", COUNT(v1."DL") as "TOTAL", COUNT(v2."DL") as "WITHIN_TARGET", (COUNT(v2."DL") / COUNT(v1."DL")) AS "ADHERENCE"
> FROM eus_l2_metrics_view v1, 
> 	(SELECT "DL", EXTRACT(YEAR FROM "CLOSE_DATE") AS "YEAR", EXTRACT(MONTH FROM "CLOSE_DATE") AS "MONTH", COUNT("DL") 
> 	FROM eus_l2_metrics_view 
> 	WHERE "TTR" <= '2.5' AND "TTR" IS NOT NULL 
> 	GROUP BY "DL", "DL_COUNTRY_CODE", "YEAR", "MONTH") v2
> WHERE v1."DL" = v2."DL"
> GROUP BY v1."DL", EXTRACT(YEAR FROM v1."CLOSE_DATE"), v1."DL_COUNTRY_CODE", EXTRACT(MONTH FROM v1."CLOSE_DATE")
> ORDER BY EXTRACT(YEAR FROM v1."CLOSE_DATE"), EXTRACT(MONTH FROM v1."CLOSE_DATE"), v1."DL_COUNTRY_CODE", v1."DL";

I am rusted on my SQL skills. Any help is much appreciated. Thanks!


#4

@fabiolanza
What database is this, Postgres?
Okay, that’s very different from the original question. And since I have no idea what the data looks like, then I can only guess:

SELECT v1."DL", v1."DL_COUNTRY_CODE", EXTRACT(YEAR FROM v1."CLOSE_DATE") AS "YEAR", EXTRACT(MONTH FROM v1."CLOSE_DATE") AS "MONTH", COUNT(v1."DL") as "TOTAL", v2."WITHIN_TARGET", (v2."WITHIN_TARGET" / COUNT(v1."DL")) AS "ADHERENCE"
FROM eus_l2_metrics_view v1
LEFT JOIN
  (SELECT "DL", "DL_COUNTRY_CODE", EXTRACT(YEAR FROM "CLOSE_DATE") AS "YEAR", EXTRACT(MONTH FROM "CLOSE_DATE") AS "MONTH", COUNT("DL") AS "WITHIN_TARGET"
    FROM eus_l2_metrics_view
    WHERE "TTR" <= '2.5' AND "TTR" IS NOT NULL AND 
    GROUP BY "DL", "DL_COUNTRY_CODE", "YEAR", "MONTH"
	) v2 ON v2."DL" = v1."DL" AND v2."DL_COUNTRY_CODE" = v1."DL_COUNTRY_CODE" AND v2."YEAR" = v1."YEAR" AND v2."MONTH" = v1."MONTH"
GROUP BY v1."DL", v1."DL_COUNTRY_CODE", v1."YEAR", v1."MONTH"
ORDER BY v1."DL", v1."DL_COUNTRY_CODE", v1."YEAR", v1."MONTH"

Otherwise, you might find better help on dba.stackoverflow.com or similar forums.