Coding questions

Write SQL to analyze response accuracy and speed

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.

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

Table `candidates`

column	type	constraints
candidate_id	INT	PRIMARY KEY, NOT NULL
role_applied	TEXT	NOT NULL
location	TEXT	NOT NULL

Sample data:

candidate_id	location	role_applied
1	US	DA
2	CN	DA
3	IN	DA
4	US	DA

Table `submissions`

column	type	constraints
attempt_id	INT	PRIMARY KEY, NOT NULL
candidate_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL

Sample data:

attempt_id	candidate_id	started_at
1001	1	2025-08-15 10:00:00
1002	2	2025-08-15 10:00:00
1003	1	2025-08-16 10:00:00
1004	3	2025-08-17 10:00:00
1005	4	2025-08-18 10:00:00

Table `questions`

column	type	constraints
question_id	INT	PRIMARY KEY, NOT NULL
section	TEXT	NOT NULL
subtype	TEXT	NOT NULL
points	INT	NOT NULL

Sample data:

points	question_id	section	subtype
1	101	verbal	grammar
1	102	verbal	vocab
1	103	verbal	tense
1	104	verbal	other
1	105	verbal	grammar
1	106	verbal	vocab
1	107	verbal	tense
1	108	verbal	other
1	109	verbal	grammar
1	110	verbal	vocab
1	111	verbal	tense
1	112	verbal	other
1	113	verbal	grammar
1	114	verbal	vocab
1	115	verbal	tense
1	116	verbal	other
2	201	design	n/a
2	301	analytics	n/a

Table `responses`

column	type	constraints
attempt_id	INT	NOT NULL
question_id	INT	NOT NULL
is_correct	INT	NOT NULL
response_time_sec	INT	NOT NULL

Sample data:

attempt_id	is_correct	question_id	response_time_sec
1001	1	101	32
1001	1	105	39
1001	0	109	28
1001	1	113	35
1001	0	102	60
1001	1	106	50
1001	0	110	55
1001	1	114	45
1001	1	103	40
1001	1	107	40
1001	1	111	40
1001	0	115	10
1001	0	104	12
1001	0	108	18
1001	1	112	22
1001	0	116	14
1002	0	101	18
1002	0	105	20
1002	0	109	14
1002	1	113	16
1002	1	102	55
1002	1	106	48
1002	0	110	12
1002	0	114	10
1002	1	103	40
1002	0	107	10
1002	0	111	10
1002	0	115	10
1002	0	104	10
1002	0	108	9
1002	0	112	8
1002	0	116	7
1003	1	101	30
1003	1	105	37
1003	1	109	33
1003	1	113	34
1003	1	102	52
1003	1	106	49
1003	1	110	51
1003	0	114	47
1003	1	103	40
1003	1	107	40
1003	1	111	40
1003	1	115	40
1003	1	104	58
1003	0	108	40
1003	1	112	36
1003	1	116	20
1004	1	101	25
1004	0	105	27
1004	1	109	29
1004	0	113	31
1004	0	102	35
1004	1	106	33
1004	0	110	37
1004	1	114	39
1004	1	103	40
1004	1	107	40
1004	0	111	10
1004	0	115	10
1004	0	104	15
1004	0	108	16
1004	1	112	14
1004	0	116	13
1005	1	101	28
1005	1	105	30
1005	1	109	32
1005	1	113	34
1005	1	102	36
1005	1	106	38
1005	1	110	40
1005	0	114	42
1005	1	103	40
1005	1	107	40
1005	1	111	40
1005	0	115	10
1005	1	104	26
1005	1	108	24
1005	0	112	22
1005	1	116	20

Expected output

One row per (attempt_id, subtype) for attempts with at least 15 verbal responses, including subtype accuracy/median time plus attempt-level verbal totals repeated on each row.

accuracy	attempt_id	median_response_time_sec	subtype	total_verbal_responses	total_verbal_time_sec
0.75	1001	33.5	grammar	16	540
0.25	1001	16	other	16	540
0.75	1001	40	tense	16	540
0.5	1001	52.5	vocab	16	540
0.25	1002	17	grammar	16	297
0	1002	8.5	other	16	297
0.25	1002	10	tense	16	297
0.5	1002	30	vocab	16	297
1	1003	33.5	grammar	16	647
0.75	1003	38	other	16	647
1	1003	40	tense	16	647
0.75	1003	50	vocab	16	647
0.5	1004	28	grammar	16	414
0.25	1004	14.5	other	16	414
0.5	1004	25	tense	16	414
0.5	1004	36	vocab	16	414
1	1005	31	grammar	16	502
0.75	1005	23	other	16	502
0.75	1005	40	tense	16	502
0.75	1005	39	vocab	16	502

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.
Compute attempt totals in a separate CTE and join them back to the per-subtype aggregates.

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

Table `candidates`

column	type	constraints
candidate_id	INT	PRIMARY KEY, NOT NULL
role_applied	TEXT	NOT NULL
location	TEXT	NOT NULL

Sample data:

candidate_id	location	role_applied
1	US	DA
2	CN	DA
3	IN	DA
4	US	DA

Table `submissions`

column	type	constraints
attempt_id	INT	PRIMARY KEY, NOT NULL
candidate_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL

Sample data:

attempt_id	candidate_id	started_at
1001	1	2025-08-15 10:00:00
1002	2	2025-08-15 10:00:00
1003	1	2025-08-16 10:00:00
1004	3	2025-08-17 10:00:00
1005	4	2025-08-18 10:00:00

Table `questions`

column	type	constraints
question_id	INT	PRIMARY KEY, NOT NULL
section	TEXT	NOT NULL
subtype	TEXT	NOT NULL
points	INT	NOT NULL

Sample data:

points	question_id	section	subtype
1	101	verbal	grammar
1	102	verbal	vocab
1	103	verbal	tense
1	104	verbal	other
1	105	verbal	grammar
1	106	verbal	vocab
1	107	verbal	tense
1	108	verbal	other
1	109	verbal	grammar
1	110	verbal	vocab
1	111	verbal	tense
1	112	verbal	other
1	113	verbal	grammar
1	114	verbal	vocab
1	115	verbal	tense
1	116	verbal	other

Table `responses`

column	type	constraints
attempt_id	INT	NOT NULL
question_id	INT	NOT NULL
is_correct	INT	NOT NULL
response_time_sec	INT	NOT NULL

Sample data:

attempt_id	is_correct	question_id	response_time_sec
1001	1	101	32
1001	1	105	39
1001	0	109	28
1001	1	113	35
1001	0	102	60
1001	1	106	50
1001	0	110	55
1001	1	114	45
1001	1	103	40
1001	1	107	40
1001	1	111	40
1001	0	115	10
1001	0	104	12
1001	0	108	18
1001	1	112	22
1001	0	116	14
1002	0	101	18
1002	0	105	20
1002	0	109	14
1002	1	113	16
1002	1	102	55
1002	1	106	48
1002	0	110	12
1002	0	114	10
1002	1	103	40
1002	0	107	10
1002	0	111	10
1002	0	115	10
1002	0	104	10
1002	0	108	9
1002	0	112	8
1002	0	116	7
1003	1	101	30
1003	1	105	37
1003	1	109	33
1003	1	113	34
1003	1	102	52
1003	1	106	49
1003	1	110	51
1003	0	114	47
1003	1	103	40
1003	1	107	40
1003	1	111	40
1003	1	115	40
1003	1	104	58
1003	0	108	40
1003	1	112	36
1003	1	116	20
1004	1	101	25
1004	0	105	27
1004	1	109	29
1004	0	113	31
1004	0	102	35
1004	1	106	33
1004	0	110	37
1004	1	114	39
1004	1	103	40
1004	1	107	40
1004	0	111	10
1004	0	115	10
1004	0	104	15
1004	0	108	16
1004	1	112	14
1004	0	116	13
1005	1	101	28
1005	1	105	30
1005	1	109	32
1005	1	113	34
1005	1	102	36
1005	1	106	38
1005	1	110	40
1005	0	114	42
1005	1	103	40
1005	1	107	40
1005	1	111	40
1005	0	115	10
1005	1	104	26
1005	1	108	24
1005	0	112	22
1005	1	116	20

Expected output

One row per rushing attempt (attempts where rushing_rate > 0.20), including the owning candidate_id.

attempt_id	candidate_id	rushing_rate
1002	2	0.625
1004	3	0.25

Hints

Filter to verbal responses first by joining to questions.
Compute rushing_rate as AVG((response_time_sec < 15)::int).
Filter attempts with rushing_rate > 0.20.

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.

Table `questions`

column	type	constraints
question_id	INT	PRIMARY KEY, NOT NULL
section	TEXT	NOT NULL
subtype	TEXT	NOT NULL
points	INT	NOT NULL

Sample data:

points	question_id	section	subtype
1	101	verbal	grammar
1	102	verbal	vocab
1	103	verbal	tense
1	104	verbal	other
1	105	verbal	grammar
1	106	verbal	vocab
1	107	verbal	tense
1	108	verbal	other
1	109	verbal	grammar
1	110	verbal	vocab
1	111	verbal	tense
1	112	verbal	other
1	113	verbal	grammar
1	114	verbal	vocab
1	115	verbal	tense
1	116	verbal	other

Table `responses`

column	type	constraints
attempt_id	INT	NOT NULL
question_id	INT	NOT NULL
is_correct	INT	NOT NULL
response_time_sec	INT	NOT NULL

Sample data:

attempt_id	is_correct	question_id	response_time_sec
1001	1	101	32
1001	1	105	39
1001	0	109	28
1001	1	113	35
1001	0	102	60
1001	1	106	50
1001	0	110	55
1001	1	114	45
1001	1	103	40
1001	1	107	40
1001	1	111	40
1001	0	115	10
1001	0	104	12
1001	0	108	18
1001	1	112	22
1001	0	116	14
1002	0	101	18
1002	0	105	20
1002	0	109	14
1002	1	113	16
1002	1	102	55
1002	1	106	48
1002	0	110	12
1002	0	114	10
1002	1	103	40
1002	0	107	10
1002	0	111	10
1002	0	115	10
1002	0	104	10
1002	0	108	9
1002	0	112	8
1002	0	116	7
1003	1	101	30
1003	1	105	37
1003	1	109	33
1003	1	113	34
1003	1	102	52
1003	1	106	49
1003	1	110	51
1003	0	114	47
1003	1	103	40
1003	1	107	40
1003	1	111	40
1003	1	115	40
1003	1	104	58
1003	0	108	40
1003	1	112	36
1003	1	116	20
1004	1	101	25
1004	0	105	27
1004	1	109	29
1004	0	113	31
1004	0	102	35
1004	1	106	33
1004	0	110	37
1004	1	114	39
1004	1	103	40
1004	1	107	40
1004	0	111	10
1004	0	115	10
1004	0	104	15
1004	0	108	16
1004	1	112	14
1004	0	116	13
1005	1	101	28
1005	1	105	30
1005	1	109	32
1005	1	113	34
1005	1	102	36
1005	1	106	38
1005	1	110	40
1005	0	114	42
1005	1	103	40
1005	1	107	40
1005	1	111	40
1005	0	115	10
1005	1	104	26
1005	1	108	24
1005	0	112	22
1005	1	116	20

Expected output

Exactly one row: the verbal subtype with the maximum absolute Pearson correlation between response_time_sec and is_correct, along with the correlation value and number of rows used.

corr	n	subtype
1	20	tense

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.
Return COUNT(*) as n so you can validate the sample size.

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:

higher verbal_correct_per_minute
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+.

Table `candidates`

column	type	constraints
candidate_id	INT	PRIMARY KEY, NOT NULL
role_applied	TEXT	NOT NULL
location	TEXT	NOT NULL

Sample data:

candidate_id	location	role_applied
1	US	DA
2	CN	DA
3	IN	DA
4	US	DA

Table `submissions`

column	type	constraints
attempt_id	INT	PRIMARY KEY, NOT NULL
candidate_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL

Sample data:

attempt_id	candidate_id	started_at
1001	1	2025-08-15 10:00:00
1002	2	2025-08-15 10:00:00
1003	1	2025-08-16 10:00:00
1004	3	2025-08-17 10:00:00
1005	4	2025-08-18 10:00:00

Table `questions`

column	type	constraints
question_id	INT	PRIMARY KEY, NOT NULL
section	TEXT	NOT NULL
subtype	TEXT	NOT NULL
points	INT	NOT NULL

Sample data:

points	question_id	section	subtype
1	101	verbal	grammar
1	102	verbal	vocab
1	103	verbal	tense
1	104	verbal	other
1	105	verbal	grammar
1	106	verbal	vocab
1	107	verbal	tense
1	108	verbal	other
1	109	verbal	grammar
1	110	verbal	vocab
1	111	verbal	tense
1	112	verbal	other
1	113	verbal	grammar
1	114	verbal	vocab
1	115	verbal	tense
1	116	verbal	other

Table `responses`

column	type	constraints
attempt_id	INT	NOT NULL
question_id	INT	NOT NULL
is_correct	INT	NOT NULL
response_time_sec	INT	NOT NULL

Sample data:

attempt_id	is_correct	question_id	response_time_sec
1001	1	101	32
1001	1	105	39
1001	0	109	28
1001	1	113	35
1001	0	102	60
1001	1	106	50
1001	0	110	55
1001	1	114	45
1001	1	103	40
1001	1	107	40
1001	1	111	40
1001	0	115	10
1001	0	104	12
1001	0	108	18
1001	1	112	22
1001	0	116	14
1002	0	101	18
1002	0	105	20
1002	0	109	14
1002	1	113	16
1002	1	102	55
1002	1	106	48
1002	0	110	12
1002	0	114	10
1002	1	103	40
1002	0	107	10
1002	0	111	10
1002	0	115	10
1002	0	104	10
1002	0	108	9
1002	0	112	8
1002	0	116	7
1003	1	101	30
1003	1	105	37
1003	1	109	33
1003	1	113	34
1003	1	102	52
1003	1	106	49
1003	1	110	51
1003	0	114	47
1003	1	103	40
1003	1	107	40
1003	1	111	40
1003	1	115	40
1003	1	104	58
1003	0	108	40
1003	1	112	36
1003	1	116	20
1004	1	101	25
1004	0	105	27
1004	1	109	29
1004	0	113	31
1004	0	102	35
1004	1	106	33
1004	0	110	37
1004	1	114	39
1004	1	103	40
1004	1	107	40
1004	0	111	10
1004	0	115	10
1004	0	104	15
1004	0	108	16
1004	1	112	14
1004	0	116	13
1005	1	101	28
1005	1	105	30
1005	1	109	32
1005	1	113	34
1005	1	102	36
1005	1	106	38
1005	1	110	40
1005	0	114	42
1005	1	103	40
1005	1	107	40
1005	1	111	40
1005	0	115	10
1005	1	104	26
1005	1	108	24
1005	0	112	22
1005	1	116	20

Expected output

One row per candidate showing metrics computed from that candidate's latest attempt, plus a rank within their location (ordered by correct per minute desc, then avg time per question asc).

avg_time_per_question_sec	candidate_id	latest_attempt_id	location	rank_in_location	verbal_correct_per_minute	verbal_questions_per_minute
18.56	2	1002	CN	1	0.8081	3.2323
25.88	3	1004	IN	1	1.0145	2.3188
31.38	4	1005	US	1	1.5538	1.9124
40.44	1	1003	US	2	1.2983	1.4838

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).
Use RANK() OVER (PARTITION BY location ORDER BY correct_per_min DESC, avg_time_per_question ASC).

Attempt-level verbal subtype accuracy + median time (with attempt totals)

Table candidates

Table submissions

Table questions

Table responses

Expected output

Hints

Flag rushing attempts by share of very fast verbal responses

Table candidates

Table submissions

Table questions

Table responses

Expected output

Hints

Find the verbal subtype with strongest time-vs-correct correlation

Table questions

Table responses

Expected output

Hints

Latest attempt throughput and location ranking

Table candidates

Table submissions

Table questions

Table responses

Expected output

Hints

Table `candidates`

Table `submissions`

Table `questions`

Table `responses`

Table `candidates`

Table `submissions`

Table `questions`

Table `responses`

Table `questions`

Table `responses`

Table `candidates`

Table `submissions`

Table `questions`

Table `responses`