SQL for largest time difference between consecutive posts

[Mouth]

Can anyone think of some SQL to answer the question of "what's the longest time between postings on a thread?"

i.e, which thread has the greatest time difference between two consecutive posts?

For example, thread x has a 18 year gap between posts 3 and 4, and thread y has a 21 year gap between posts 326 and 327 - thread y would be identified as the largest post time gap.

 

[Lee]

WITH PostGaps AS (
    SELECT
        p1.thread_id,
        p1.post_id,
        p1.post_date,
        LAG(p1.post_id) OVER (PARTITION BY p1.thread_id ORDER BY p1.post_date) AS previous_post_id,
        LAG(p1.post_date) OVER (PARTITION BY p1.thread_id ORDER BY p1.post_date) AS previous_post_date,
        (p1.post_date - LAG(p1.post_date) OVER (PARTITION BY p1.thread_id ORDER BY p1.post_date)) AS gap_seconds
    FROM xf_post p1
)
SELECT
    thread_id,
    previous_post_id,
    post_id,
    max_gap_seconds,
    ROUND(max_gap_seconds / (60 * 60 * 24 * 365), 2) AS max_gap_years,
    ROUND(max_gap_seconds / (60 * 60 * 24), 2) AS max_gap_days
FROM (
    SELECT
        thread_id,
        previous_post_id,
        post_id,
        gap_seconds AS max_gap_seconds
    FROM PostGaps
    WHERE previous_post_date IS NOT NULL
    ORDER BY gap_seconds DESC
    LIMIT 1
) subquery;