coverage percentage question solved through a complex sql query...?

Question

Ok, i've been trying to solve this for about 2 hours now... Please advise:

Tables:

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

The lvl can basically go from 0 to 3.

I'm trying to get this particular stat: "What is the percentage of apps that is covered by at least two people having a skill of 2 or higher?"

Thanks a lot

Answer 1

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 that. For example, in MySQL:

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

Answer 2

Untested

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

I'm not sure if this is any better or worse than tvanfosson's answer, but here it is 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

Answer 4

The logic is: percentage = 100 * ( number of apps of interest ) / ( total number of apps )

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 )

The convert to float is needed so sql doesn't just do integer arithmetic.

Answer 5

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 it finds the second skilled person for each app.

Efficient if you have a few app's but lots of person's.