“No such column: mer.answer_id” error when calculating median time spent in SQLite

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