I’m trying to run a SQL query in SQLite to calculate the median time spent on a test, but I’m getting the error no such column: mer.answer_id
. The query uses a CTE (Common Table Expression) and a nested subquery to calculate the median, but SQLite doesn’t seem to support this syntax.
Here’s the SQL I’m trying to run:
select "mer".*, (
WITH ordered_times AS (
SELECT
CAST(substr(time_spent, 1, 2) AS INTEGER) * 3600 +
CAST(substr(time_spent, 4, 2) AS INTEGER) * 60 +
CAST(substr(time_spent, 7, 2) AS INTEGER) as seconds
FROM mock_exam_results AS mer_inner
JOIN answers AS a ON mer_inner.answer_id = a.id
WHERE mer_inner.is_first_time = 1
AND mer_inner.answer_id = mer.answer_id
AND a.is_correct = 1
ORDER BY seconds
),
time_stats AS (
SELECT
(
SELECT AVG(seconds)
FROM (
SELECT seconds
FROM ordered_times
LIMIT 2 - (SELECT COUNT(*) % 2 FROM ordered_times)
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM ordered_times)
)
) as median_time_seconds
)
SELECT
printf('%02d:%02d:%02d',
FLOOR(median_time_seconds / 3600),
FLOOR((median_time_seconds % 3600) / 60),
FLOOR(median_time_seconds % 60)
)
FROM time_stats
) as users_median_time_spent
from "mock_exam_results" as "mer"
The mock_exam_results
table has an answer_id
column, but SQLite doesn’t seem to recognize it in the query. I’ve checked that the column exists and there are no typographical errors.
I commented out the AND mer_inner.answer_id = mer.answer_id
clause to test if it would work, but it resulted in all the records having the same users_median_time_spent
value because the median was being calculated for all records as a whole, rather than individually for each answer_id
.
I hope you can help me with this issue.
You need to sign in to view this answers