Search for rating for first or second ever review

Anki Suggestions
7

An SQL query for cards rated “Again” on both the first and second review:

WITH
    first_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
            )
    ),
    second_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
                OFFSET
                    1
            )
    )
SELECT
    first_review.cid
FROM
    first_review
    INNER JOIN second_review ON first_review.cid = second_review.cid

An SQL query for cards rated “Again” on the first, second, and third review:

WITH
    first_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
            )
    ),
    second_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
                OFFSET
                    1
            )
    ),
    third_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
                OFFSET
                    2
            )
    )
SELECT
    first_review.cid
FROM
    first_review
    INNER JOIN second_review ON first_review.cid = second_review.cid
    INNER JOIN third_review ON first_review.cid = third_review.cid

( I am not very familiar with SQL so I can’t tell you, but surely there must be more concise and efficient SQL queries to get the same results as these. )

1 Like