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.
|mentee_id|mentor_id |1 |3 |2 |3 |3 |5
|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
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