Write SQL to analyze response accuracy and speed
Overview
This question evaluates proficiency with SQL-based data manipulation and analytical querying on relational assessment data, including aggregation, grouping, joins, windowed ranking, and use of PostgreSQL statistical functions to analyze accuracy and response-time metrics.
You are given response-level data for an online assessment with sections verbal/design/analytics and verbal subtypes grammar/vocab/tense/other. Using PostgreSQL 14+, answer the SQL tasks below on the following schema and sample rows (illustrative only; real data are larger). Schema: candidates(candidate_id INT PRIMARY KEY, role_applied TEXT, location TEXT); submissions(attempt_id INT PRIMARY KEY, candidate_id INT, started_at TIMESTAMP); questions(question_id INT PRIMARY KEY, section TEXT, subtype TEXT, points INT); responses(attempt_id INT, question_id INT, is_correct INT, response_time_sec INT). Sample rows: candidates: | candidate_id | role_applied | location | -> | 1 | DA | US | | 2 | DA | CN | | 3 | DA | IN |. submissions: | attempt_id | candidate_id | started_at | -> | 1001 | 1 | 2025-08-15 10:00 | | 1002 | 2 | 2025-08-15 10:00 | | 1003 | 1 | 2025-08-16 10:00 |. questions: | question_id | section | subtype | points | -> | 101 | verbal | grammar | 1 | | 102 | verbal | vocab | 1 | | 103 | verbal | tense | 1 | | 104 | verbal | other | 1 | | 105 | verbal | grammar | 1 | | 201 | design | n/a | 2 | | 301 | analytics | n/a | 2 |. responses: | attempt_id | question_id | is_correct | response_time_sec | -> | 1001 | 101 | 1 | 32 | | 1001 | 102 | 0 | 60 | | 1001 | 103 | 1 | 41 | | 1001 | 104 | 0 | 12 | | 1001 | 105 | 1 | 39 | | 1002 | 101 | 0 | 18 | | 1002 | 102 | 1 | 55 | | 1002 | 103 | 1 | 42 | | 1002 | 104 | 0 | 10 | | 1002 | 105 | 0 | 20 | | 1003 | 101 | 1 | 30 | | 1003 | 102 | 1 | 52 | | 1003 | 103 | 1 | 46 | | 1003 | 104 | 1 | 58 | | 1003 | 105 | 1 | 37 |. Tasks: (1) For each attempt with at least 15 verbal responses in the real dataset, compute per-subtype accuracy (avg is_correct) and median response_time_sec; also return total verbal responses and total verbal time per attempt. (2) Flag 'rushing' attempts where over 20% of verbal responses have response_time_sec < 15. Return attempt_id, candidate_id, rushing_rate. (3) Within the verbal section only, find the subtype with the strongest magnitude Pearson correlation between response_time_sec and is_correct (cast to double). Return subtype, corr, n. Use Postgres corr(). (4) For each candidate, compute their latest attempt’s verbal questions per minute and correct per minute; then rank candidates within location by correct per minute, breaking ties by lower time per question.