Quick 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.

Write SQL to analyze response accuracy and speed

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Online Assessment

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.

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

  1. Filter to verbal questions by joining responses to questions and using q.section = 'verbal'.
  2. 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

  1. Filter to verbal responses first by joining to questions.
  2. 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

  1. Compute corr(response_time_sec::double precision, is_correct::double precision) grouped by subtype.
  2. 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

  1. Use ROW_NUMBER() to find each candidate's latest attempt by started_at.
  2. Compute per-minute rates by dividing counts by (sum_time_sec / 60.0).

Loading coding console...