SQLTeam.com | Weblogs | Forums

SQL Query question

Can you please help me to write a query for the following question? See attached Tables list diagram too.

Who are the top 10 engaged members by name?

a. Consider that being on a committee has a higher significance than taking a course by a factor of 3 (1 committee participation is worth 3 course enrollments)
b. Members are considered engaged for both active and past committee engagements.

how would you define "ENGAGED" ???

Members can be engaged with two forms: courses, and committee participation.

hi

can a member be in both ..

courses, and committee participation.????

Yes. Thank you for looking into this.

hi

hope this helps :slight_smile: ....

; with cte as 
(
select person_id,count(*) * 3 as cnt from person_comittee 
union all 
select person_id,count(*)   as cnt from person_course
) , cte_sum as 
(
select person_id , sum(cnt) as sm_cnt from cte group by person_id 
)
select top 10 * from cte_sum order by sm_cnt desc

Thank you so much.