Demo entry 6632451

asdf

   

Submitted by anonymous on Jul 25, 2017 at 16:52
Language: SQL. Code size: 2.4 kB.

WITH MAX_SCORE_EXERCISE AS 
(SELECT context_id AS exercise_id, sum(weight) as max_reachable_points
FROM files
WHERE role = 'teacher_defined_test'
AND context_id IN (<list of exercises>)
AND context_type = 'Exercise'
GROUP BY context_id),

WITH WORKING_TIME AS 
(SELECT user_id,
                   id,
                   exercise_id,
                   max(score) AS max_score,
                   (created_at - lag(created_at) OVER (PARTITION BY user_id, exercise_id
                                                       ORDER BY created_at)) AS working_time
           FROM submissions
           WHERE exercise_id IN (<list of exercises>) AND user_type = 'ExternalUser'
           GROUP BY user_id, id, exercise_id),

-- filter for rows containing 5 points
TIME_MAX_SCORE AS
(SELECT *
FROM WORKING_TIME W1
WHERE EXISTS (select * from MAX_SCORE_EXERCISE where exercise_id = W1.exercise_id and W1.max_score = max_reachable_points) = 5),

-- find row containing the first time 5 points
FIRST_TIME_MAX_SCORE AS
( SELECT id,USER_id,exercise_id,max_score,working_time, rn
  FROM (
    SELECT id,USER_id,exercise_id,max_score,working_time,
        ROW_NUMBER() OVER(PARTITION BY user_id, exercise_id ORDER BY id ASC) AS rn
    FROM TIME_MAX_SCORE) T
 WHERE rn = 1),

TIMES_UNTIL_5_POINTS AS (
    SELECT W.id, W.user_id, W.exercise_id, W.max_score, W.working_time, M.id AS reachedmax_at
    FROM WORKING_TIME W, FIRST_TIME_MAX_SCORE M
    WHERE W.user_id = M.user_id AND W.exercise_id = M.exercise_id AND W.id <= M.id),

-- if user never makes it to 5 points, take all times
ALL_WORKING_TIMES_UNTIL_MAX AS
((SELECT id, user_id, exercise_id, max_score, working_time FROM TIMES_UNTIL_5_POINTS)
UNION ALL
(SELECT id, user_id, exercise_id, max_score, working_time FROM WORKING_TIME W1
 WHERE NOT EXISTS (SELECT 1 FROM FIRST_TIME_MAX_SCORE F WHERE F.user_id = W1.user_id AND F.exercise_id = W1.exercise_id))), 

FILTERED_TIMES_UNTIL_MAX AS
(
SELECT user_id,exercise_id, max_score, CASE WHEN working_time >= '0:10:00' THEN '0' ELSE working_time END AS working_time_new
FROM ALL_WORKING_TIMES_UNTIL_MAX
)
    SELECT e.external_id AS external_user_id, f.user_id, exercise_id, MAX(max_score) AS max_score, sum(working_time_new) AS working_time
    FROM FILTERED_TIMES_UNTIL_MAX f, EXTERNAL_USERS e
    WHERE f.user_id = e.id GROUP BY e.external_id, f.user_id, exercise_id

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).