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. Attempt-level verbal subtype accuracy + median time (with attempt totals) You are given response-level data for an online assessment. Each question belongs to a section (verbal/design/analytics). Verbal questions also have a subtype (grammar/vocab/tense/other).

For each attempt that has at least 15 verbal responses, return one row per (attempt_id, verbal subtype) with:

• accuracy = average of is_correct (treat is_correct as 0/1)
• median_response_time_sec for that subtype within the attempt
• total_verbal_responses in the attempt
• total_verbal_time_sec in the attempt

Output columns: attempt_id, subtype, accuracy, median_response_time_sec, total_verbal_responses, total_verbal_time_sec.

Use PostgreSQL 14+. Median should be computed using percentile_cont(0.5). Tables candidates ( candidate_id INT, role_applied TEXT, location TEXT ) submissions ( attempt_id INT, candidate_id INT, started_at TIMESTAMP ) questions ( question_id INT, section TEXT, subtype TEXT, points INT ) responses ( attempt_id INT, question_id INT, is_correct INT, response_time_sec INT ) Hints Filter to verbal questions by joining responses to questions and using q.section = 'verbal'. Use percentile_cont(0.5) WITHIN GROUP (ORDER BY response_time_sec) to compute a median in Postgres. Flag rushing attempts by share of very fast verbal responses A verbal response is considered "very fast" if response_time_sec < 15.

Flag "rushing" attempts where more than 20% of verbal responses are very fast.

Return attempt_id, candidate_id, and rushing_rate (the fraction of verbal responses with response_time_sec < 15).

Use PostgreSQL 14+. Tables candidates ( candidate_id INT, role_applied TEXT, location TEXT ) submissions ( attempt_id INT, candidate_id INT, started_at TIMESTAMP ) questions ( question_id INT, section TEXT, subtype TEXT, points INT ) responses ( attempt_id INT, question_id INT, is_correct INT, response_time_sec INT ) Hints Filter to verbal responses first by joining to questions. Compute rushing_rate as AVG((response_time_sec < 15)::int). Find the verbal subtype with strongest time-vs-correct correlation Within the verbal section only, compute the Pearson correlation between response_time_sec and is_correct for each verbal subtype, using PostgreSQL's corr() function.

Return the subtype with the strongest magnitude correlation (i.e., maximum ABS(corr)). Also return:

• corr (the correlation value)
• n (number of responses used)

Notes:

• Cast is_correct to double precision.
• If a subtype's correlation is NULL (e.g., no variance), it should not win over non-NULL correlations.

Output columns: subtype, corr, n. Tables questions ( question_id INT, section TEXT, subtype TEXT, points INT ) responses ( attempt_id INT, question_id INT, is_correct INT, response_time_sec INT ) Hints Compute corr(response_time_sec::double precision, is_correct::double precision) grouped by subtype. Use ORDER BY ABS(corr) DESC NULLS LAST to pick the strongest magnitude correlation. Latest attempt throughput and location ranking For each candidate, consider ONLY their latest attempt (based on submissions.started_at).

Using only verbal responses from that latest attempt, compute:

• verbal_questions_per_minute = (number of verbal responses) / (total verbal time in minutes)
• verbal_correct_per_minute = (number of correct verbal responses) / (total verbal time in minutes)
• avg_time_per_question_sec = average verbal response_time_sec

Then rank candidates within each location by:

1. higher verbal_correct_per_minute
2. if tied, lower avg_time_per_question_sec

Return: candidate_id, location, latest_attempt_id, verbal_questions_per_minute, verbal_correct_per_minute, avg_time_per_question_sec, rank_in_location.

Use PostgreSQL 14+. Tables candidates ( candidate_id INT, role_applied TEXT, location TEXT ) submissions ( attempt_id INT, candidate_id INT, started_at TIMESTAMP ) questions ( question_id INT, section TEXT, subtype TEXT, points INT ) responses ( attempt_id INT, question_id INT, is_correct INT, response_time_sec INT ) Hints Use ROW_NUMBER() to find each candidate's latest attempt by started_at. Compute per-minute rates by dividing counts by (sum_time_sec / 60.0).