with defaultEngCompetency as ( SELECT round(AVG(Engagement)) Engagement, sum(EngagmentQuestionsAnswered) EngagmentQuestionsAnswered, sum(EngagmentQuestionsSent) En
gagmentQuestionsSent  , round(AVG(Competency)) Competency, sum(CompetencyQuestionsAnswered) CompetencyQuestionsAnswered, sum(CompetencyQuestionsCorrect) CompetencyQuestionsCorrect  , roun
d(AVG(NellyFactor)) NellyFactor from (select t.C_ID, SUM(1) as QuestionSent, SUM(if(t.sentDate >= '2022-10-07' and t.sentDate <= '2022-11-06', 1, 0)) as EngagmentQuestionsSent, SUM(if(t.s
entDate >= '2022-10-07' and t.sentDate <= '2022-11-06', t.isAnswered, 0)) as EngagmentQuestionsAnswered, if (SUM(if(t.sentDate >= '2022-10-07' and t.sentDate <= '2022-11-06', 1, 0)) = 0,
null, round(100 * SUM(if(t.sentDate >= '2022-10-07' and t.sentDate <= '2022-11-06', t.isAnswered, 0)) / SUM(if(t.sentDate >= '2022-10-07' and t.sentDate <= '2022-11-06', 1, 0)))) as Engag
ement, SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-10-07' and t.answerDate <= '2022-11-06', 1, 0)) as CompetencyQuestionsAnswered, SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-
10-07' and t.answerDate <= '2022-11-06', t.isCorrect, 0)) as CompetencyQuestionsCorrect, if (SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-10-07' and t.answerDate <= '2022-11-06', 1,
0)) = 0,null,round(100 * SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-10-07' and t.answerDate <= '2022-11-06', t.isCorrect, 0)) / SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-10
-07' and t.answerDate <= '2022-11-06', 1, 0)))) as Competency, round(if (SUM(if(t.sentDate >= '2022-10-07' and t.sentDate <= '2022-11-06', 1, 0)) = 0, null, round(100 * SUM(if(t.sentDate
>= '2022-10-07' and t.sentDate <= '2022-11-06', t.isAnswered, 0)) / SUM(if(t.sentDate >= '2022-10-07' and t.sentDate <= '2022-11-06', 1, 0)))) * if (SUM(if(t.isAnswered = 1 and t.answerDa
te >= '2022-10-07' and t.answerDate <= '2022-11-06', 1, 0)) = 0,null,round(100 * SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-10-07' and t.answerDate <= '2022-11-06', t.isCorrect, 0)
) / SUM(if(t.isAnswered = 1 and t.answerDate >= '2022-10-07' and t.answerDate <= '2022-11-06', 1, 0)))) / 100) NellyFactor  FROM edf_colstore.vinci_cs_tracking_t t  inner join edf_colstor
e.pl_cs_contact_t pcct on t.c_id = pcct.c_id and pcct.orgunit_id in (2,29,30,32,34,3782,3783,3793,3891,4006,4046,4108,4152,4153,5888,9454,9455,9598,9902,10648,18020,19589,21320,21321,2132
2,21323,21324,21325,40159,40305,43932,43936,43937,46410,46422,49614,49615,50055,50915,50916,50917,50918,50919,51867,53424,65070,66418,67182,69413,71720,21320) inner join edf_colstore.pl_c
s_question_t pqt on t.question_id = pqt.id  WHERE t.accountId = 58773 AND ((t.sentDate >= '2022-10-07' AND t.sentDate <= '2022-11-06') OR (t.answerDate >= '2022-10-07' AND t.answerDate <=
 '2022-11-06')) and t.updateControl = 0  and pcct.orgunit_id in (select team_id from edf_query.vinci_user_filter_team_t  where userFilterIndex_id = 803) GROUP BY t.C_ID) a )
                                select
                                        Engagement as pEngagement,
                                        EngagmentQuestionsAnswered pEngagmentQuestionsAnswered,
                                        EngagmentQuestionsSent as pEngagmentQuestionsSent,
                                        Competency as pCompetency,
                                        CompetencyQuestionsAnswered as pCompetencyQuestionsAnswered,
                                        CompetencyQuestionsCorrect as pCompetencyQuestionsCorrect,
                                        NellyFactor as pNellyFactor
                                from
                                        defaultEngCompetency