Calculating percentage from two tables

Question

I have two tables:

• First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table.
• Second one contains interactions of mentors with mentees on different dates, so mentos and mentees may appear multiple times.

I'm trying to create a join between those two tables where the result would be:

'mentor_id'|'# of people'|'# of distinct interactions'  

This way I would know whom did the mentor advice during a given period, against how many they are supposed to as %.

What I've done..

SELECT INTER.mentor_id, COUNT(DISTINCT INTER.mentee_id), COUNT(f.mentee_id)
FROM INTER WITH (NOLOCK)
INNER JOIN  
    (SELECT mt.mentee_id, mt.mentor_id
     FROM mentee_table mt WITH (NOLOCK)
    ) as f 
ON f.mentor_id = INTER.mentor_id
WHERE (//period)
GROUP BY INTER.mentor_id

The problem with this is that when viewing the result without any groupings or aggregates, I receive duplicates from the subquery, since there are more records in INTER.

Schema's

mentee_table

|mentee_id|mentor_id
|1        |3
|2        |3
|3        |5

INTER

|mentee_id|mentor_id
|1        |3
|1        |3
|1        |3
|2        |3
|3        |5
|3        |5

In the end I will just use the counts to calculate percentage, such as

COUNT(DISTINCT INTER.mentee_id)*100/COUNT(f.mentee_id) which means the INTER table, when distinct, can only have <= # of people

Thanks

Edit

I ended up doing this query to also get mentors who never had any interactions. I had to use DISTINCT on both counts..

SELECT mt.mentor_id, COUNT(DISTINCT mt.mentee_id), COUNT(DISTINCT INTER.mentee_id)
FROM mentee_table mt WITH (NOLOCK)
   LEFT JOIN INTER WITH (NOLOCK) 
   ON INTER.mentor_id = mt.mentor_id AND INTER.mentee_id = mt.mentee_id
GROUP BY mt.mentor_id
ORDER_BY mt.mentor_id

Answer 1

I see what your problem is you are duplicating the interactions because you are basically just trying to join those two tables together in your query. What you want to do is a correlated subquery to get the answer of how many interactions each mentor has had.

Something like this.

select a.mentor_id, count(distinct(a.mentee_id)) as UniqueMenteeCt,b.InterCt
from mentee_table a inner join
(select a.mentor_id, count(a.mentee_id) as InterCt
 from INTER a 
 group by a.mentor_id
)b on a.mentor_id = b.mentor_id
group by a.mentor_id,InterCt

Answer 2

To get this result...

'mentor_id'|'# of people'|'# of distinct interactions'  

I think this is what you need...

WITH X as (
  SELECT mentor_id, count(*) AS [Num_of_mentees] FROM mentee_table GROUP BY mentor_id
  ),
Y as (
  SELECT mentor_id, count(*) AS [Num_of_interactions] FROM inter GROUP BY mentor_id
  )
SELECT DISTINCT(m.mentor_id) -- Ideally you should use a Mentors table instead of this.
  , X.Num_of_mentees
  , Y.Num_of_interactions
FROM mentee_table m
JOIN X on X.mentor_id = m.mentor_id
JOIN Y on Y.mentor_id = m.mentor_id

Here is the link to the SQL Fiddle.