Percentage of coverage using a complex sql query ...?

Question

Ok, I tried to solve this problem in about 2 hours ... Please report:

Tables:

PROFILE [id (int), name (varchar), ...] SKILL [id (int), id_profile (int), id_app (int), lvl (int), ...] APP [id (int), ...] 

Lvl can basically go from 0 to 3.

I am trying to get this specific stat: "What is the percentage of applications that are reached by at least two people who have skill 2 or higher?"

thanks a lot

Answer 1

 SELECT AVG(covered) FROM ( SELECT CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS covered FROM app a LEFT JOIN skill s ON (s.id_app = a.id AND s.lvl >= 2) GROUP BY a.id ) 

More efficient way for MySQL :

 SELECT AVG ( IFNULL ( ( SELECT 1 FROM skill s WHERE s.id_app = a.id AND s.lvl >= 2 LIMIT 1, 1 ), 0 ) ) FROM app a 

This will stop counting as soon as he finds a second qualified person for each app .

Effective if you have several app , but many person .

Answer 2

Not verified

 select convert(float,count(*)) / (select count(*) from app) as percentage from ( select count(*) as number from skill where lvl >= 2 group by id_app ) t where t.number >= 2 

Answer 3

Logic: percentage = 100 * (number of interesting applications) / (total number of applications)

 select 'percentage' = -- 100 times ( cast( 100 as float ) * -- number of apps of interest ( select count(id_app) from ( select id_app, count(*) as skilled_count from skill where lvl >= 2 group by id_app having count(*) >= 2 ) app_counts ) -- divided by total number of apps / ( select count(*) from app ) 

Converting to float is required, so sql doesn't just do integer arithmetic.

Answer 4

 SELECT SUM( CASE lvl WHEN 3 THEN 1 WHEN 2 THEN 1 ELSE 0 END ) / SUM(1) FROM SKILL 

If your database has an if / then function instead of CASE , use it. For example, in MySQL:

 SELECT SUM( IF( lvl >= 2, 1, 0 ) ) / SUM(1) FROM SKILL 

Answer 5

I'm not sure if this is better or worse than tvanfosson's answer, but here anyway:

 SELECT convert(float, count(*)) / (Select COUNT(id) FROM APP) AS percentage FROM APP INNER JOIN SKILL ON APP.id = SKILL.id WHERE ( SELECT COUNT(id) FROM SKILL AS Skill2 WHERE Skill2.id_app = APP.id and lvl >= 2 ) >= 2