vnuLTumz

1DROP TEMPORARY TABLE IF EXISTS tempCoursesAnalytics;
2CREATE TEMPORARY TABLE IF NOT EXISTS tempCoursesAnalytics AS 
3(
4    SELECT
5    course.COURSE_ID,
6    course.CRUID,
7    course.NAME,
8    course.COURSE_ID_TS,
9    c1.CUID,
10    c1.GUID,
11    pc.persona_count PERSONA_COUNT,
12    COUNT(distinct c.CARD_ID) AS CARD_COUNT,
13    course.STATETIME,
14    COUNT(DISTINCT (CASE WHEN c.SCOREABLE = 0 THEN c.CARD_ID END)) AS SURVEY_CARDS,
15    SUM(IF(c.SCOREABLE = 0 AND a.ISCORRECT= 1, 1,0)) AS TOTAL_SURVEY,
16    SUM(IF(a.ISCORRECT = 1, 1,0)) AS CORRECT,
17    SUM(IF(a.ISCORRECT = 0, 1,0)) AS INCORRECT,
18    count(a.ANSWER_ID) AS TOTAL_ANSWERS,
19    course.PERSONA_ID
20    FROM Courses as course
21    JOIN Cards AS c ON course.COURSE_ID = c.COURSE_ID
22    LEFT JOIN Answers as a ON a.CARD_ID=c.CARD_ID
23    INNER JOIN  (SELECT COURSE_ID, COUNT(pc1.persona_id) AS persona_count FROM Personas_Courses pc1 GROUP BY COURSE_ID) pc ON pc.COURSE_ID = course.COURSE_ID
24    INNER JOIN ( SELECT ca1.COURSE_ID, ca1.cuid, ga1.GUID FROM Cards ca1
25                INNER JOIN Games ga1 ON ca1.GAME_ID = ga1.GAME_ID
26                WHERE ga1.seq = 1 and ca1.seq = 1
27                ) c1 ON c1.COURSE_ID = c.COURSE_ID
28    WHERE course.INSTITUTION_ID=par_INSTITUTION_ID
29    AND c.TYPE != 'SLIDE'
30    AND c.IS_ASKED = 1
31    group by course.COURSE_ID
32    order by course.STATETIME DESC
33    LIMIT 10
34);
35
36SELECT 
37c.COURSE_ID ,
38CRUID,
39NAME,
40COURSE_ID_TS,
41CUID as FIRSTCARD,
42GUID as FIRSTGAME,
43PERSONA_COUNT,
44CARD_COUNT,
45SURVEY_CARDS,
46STATETIME,
47CORRECT-TOTAL_SURVEY AS CORRECT,
48INCORRECT,
49(PERSONA_COUNT*CARD_COUNT)-(CORRECT-TOTAL_SURVEY) - INCORRECT - (SURVEY_CARDS * PERSONA_COUNT) AS NOTANSWERED,
50(PERSONA_COUNT*CARD_COUNT)-SURVEY_CARDS*PERSONA_COUNT as TOTAL,
51c.PERSONA_ID,
52TOTAL_ROWS
53    FROM tempCoursesAnalytics as c
54    order by STATETIME DESC