<commit_before>-- BLOCK select_data
SELECT
    format_interval(ai.duration) AS assessment_instance_duration
FROM
    assessment_instances AS ai
WHERE
    ai.id = $assessment_instance_id;

-- BLOCK select_log
WITH event_log AS (
    (
        SELECT
            1 AS event_order,
            'Begin'::TEXT AS event_name,
            'gray3'::TEXT AS event_color,
            ai.date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            NULL::TEXT as qid,
            NULL::INTEGER as question_id,
            NULL::INTEGER as variant_id,
            NULL::INTEGER as variant_number,
            NULL::JSONB as data
        FROM
            assessment_instances AS ai
            LEFT JOIN users AS u ON (u.user_id = ai.auth_user_id)
        WHERE
            ai.id = $assessment_instance_id
    )
    UNION
    (
        SELECT
            2 AS event_order,
            'New variant'::TEXT AS event_name,
            'gray1'::TEXT AS event_color,
            v.date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            q.qid as qid,
            q.id as question_id,
            v.id AS variant_id,
            v.number AS variant_number,
            jsonb_build_object(
                'variant_seed', v.variant_seed,
                'params', v.params,
                'true_answer', v.true_answer,
                'options', v.options
            ) AS data
        FROM
            variants AS v
            JOIN instance_questions AS iq ON (iq.id = v.instance_question_id)
            JOIN assessment_questions AS aq ON (aq.id = iq.assessment_question_id)
            JOIN questions AS q ON (q.id = aq.question_id)
            LEFT JOIN users AS u ON (u.user_id = v.authn_user_id)
        WHERE
            iq.assessment_instance_id = $assessment_instance_id
    )
    UNION
    (
        SELECT
            3 AS event_order,
            'Submission'::TEXT AS event_name,
            'blue3'::TEXT AS event_color,
            s.date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            q.qid,
            q.id AS question_id,
            v.id as variant_id,
            v.number as variant_number,
            jsonb_build_object('submitted_answer', s.submitted_answer) AS data
        FROM
            submissions AS s
            JOIN variants AS v ON (v.id = s.variant_id)
            JOIN instance_questions AS iq ON (iq.id = v.instance_question_id)
            JOIN assessment_questions AS aq ON (aq.id = iq.assessment_question_id)
            JOIN questions AS q ON (q.id = aq.question_id)
            LEFT JOIN users AS u ON (u.user_id = s.auth_user_id)
        WHERE
            iq.assessment_instance_id = $assessment_instance_id
    )
    UNION
    (
        SELECT
            3.5 AS event_order,
            'External grading results'::TEXT AS event_name,
            'blue1'::TEXT AS event_color,
            gj.graded_at AS date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            q.qid,
            q.id AS question_id,
            v.id as variant_id,
            v.number as variant_number,
            to_jsonb(gj.*) AS data
        FROM
            grading_jobs AS gj
            JOIN submissions AS s ON (s.id = gj.submission_id)
            JOIN variants AS v ON (v.id = s.variant_id)
            JOIN instance_questions AS iq ON (iq.id = v.instance_question_id)
            JOIN assessment_questions AS aq ON (aq.id = iq.assessment_question_id)
            JOIN questions AS q ON (q.id = aq.question_id)
            LEFT JOIN users AS u ON (u.user_id = s.auth_user_id)
        WHERE
            iq.assessment_instance_id = $assessment_instance_id
            AND gj.grading_method = 'External'
    )
    UNION
    (<commit_msg>Only show graded jobs in instructor assessment instance<commit_after>-- BLOCK select_data
SELECT
    format_interval(ai.duration) AS assessment_instance_duration
FROM
    assessment_instances AS ai
WHERE
    ai.id = $assessment_instance_id;

-- BLOCK select_log
WITH event_log AS (
    (
        SELECT
            1 AS event_order,
            'Begin'::TEXT AS event_name,
            'gray3'::TEXT AS event_color,
            ai.date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            NULL::TEXT as qid,
            NULL::INTEGER as question_id,
            NULL::INTEGER as variant_id,
            NULL::INTEGER as variant_number,
            NULL::JSONB as data
        FROM
            assessment_instances AS ai
            LEFT JOIN users AS u ON (u.user_id = ai.auth_user_id)
        WHERE
            ai.id = $assessment_instance_id
    )
    UNION
    (
        SELECT
            2 AS event_order,
            'New variant'::TEXT AS event_name,
            'gray1'::TEXT AS event_color,
            v.date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            q.qid as qid,
            q.id as question_id,
            v.id AS variant_id,
            v.number AS variant_number,
            jsonb_build_object(
                'variant_seed', v.variant_seed,
                'params', v.params,
                'true_answer', v.true_answer,
                'options', v.options
            ) AS data
        FROM
            variants AS v
            JOIN instance_questions AS iq ON (iq.id = v.instance_question_id)
            JOIN assessment_questions AS aq ON (aq.id = iq.assessment_question_id)
            JOIN questions AS q ON (q.id = aq.question_id)
            LEFT JOIN users AS u ON (u.user_id = v.authn_user_id)
        WHERE
            iq.assessment_instance_id = $assessment_instance_id
    )
    UNION
    (
        SELECT
            3 AS event_order,
            'Submission'::TEXT AS event_name,
            'blue3'::TEXT AS event_color,
            s.date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            q.qid,
            q.id AS question_id,
            v.id as variant_id,
            v.number as variant_number,
            jsonb_build_object('submitted_answer', s.submitted_answer) AS data
        FROM
            submissions AS s
            JOIN variants AS v ON (v.id = s.variant_id)
            JOIN instance_questions AS iq ON (iq.id = v.instance_question_id)
            JOIN assessment_questions AS aq ON (aq.id = iq.assessment_question_id)
            JOIN questions AS q ON (q.id = aq.question_id)
            LEFT JOIN users AS u ON (u.user_id = s.auth_user_id)
        WHERE
            iq.assessment_instance_id = $assessment_instance_id
    )
    UNION
    (
        SELECT
            3.5 AS event_order,
            'External grading results'::TEXT AS event_name,
            'blue1'::TEXT AS event_color,
            gj.graded_at AS date,
            u.user_id AS auth_user_id,
            u.uid AS auth_user_uid,
            q.qid,
            q.id AS question_id,
            v.id as variant_id,
            v.number as variant_number,
            to_jsonb(gj.*) AS data
        FROM
            grading_jobs AS gj
            JOIN submissions AS s ON (s.id = gj.submission_id)
            JOIN variants AS v ON (v.id = s.variant_id)
            JOIN instance_questions AS iq ON (iq.id = v.instance_question_id)
            JOIN assessment_questions AS aq ON (aq.id = iq.assessment_question_id)
            JOIN questions AS q ON (q.id = aq.question_id)
            LEFT JOIN users AS u ON (u.user_id = s.auth_user_id)
        WHERE
            iq.assessment_instance_id = $assessment_instance_id
            AND gj.grading_method = 'External'
            AND gj.graded_at IS NOT NULL
    )
    UNION
    (
{'fraction_non_alphanumeric': 0.06746704945843664, 'fraction_numerical': 0.002348949497585802, 'mean_word_length': 1.5666443402545212, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3523201', 'n_tokens_mistral': 2393, 'n_tokens_neox': 2315, 'n_words': 628}