连个表就行 | #查询用户刷题日期和下一次刷题日期#

WITH RANKED AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rn
    FROM
        questions_pass_record
),
NEXT AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rn
    FROM
        questions_pass_record
)

SELECT
    RANKED.user_id,
    RANKED.date,
    NEXT.date as nextdate
FROM
    RANKED
LEFT JOIN
    NEXT
ON
    RANKED.user_id = NEXT.user_id
AND
    RANKED.rn + 1 = NEXT.rn
ORDER BY
    RANKED.user_id, RANKED.date