0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for those who base their lives on Jewish law and tradition and anyone interested in learning more

-- 사용자 10,000명 샘플링 후 가장 긴 포스팅 간격 계산
WITH UserPostDates AS (
    SELECT
        Posts.OwnerUserId,
        Posts.CreationDate
    FROM
        Posts
    WHERE
        Posts.OwnerUserId IS NOT NULL
),
SampledUsers AS (
    SELECT DISTINCT TOP 10000 OwnerUserId
    FROM UserPostDates
    ORDER BY OwnerUserId
),
SampledUserPostDates AS (
    SELECT
        u.OwnerUserId,
        p.CreationDate
    FROM
        SampledUsers u
    JOIN UserPostDates p
        ON u.OwnerUserId = p.OwnerUserId
),
PostIntervals AS (
    SELECT
        OwnerUserId,
        CreationDate,
        LEAD(CreationDate) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS NextPostDate
    FROM
        SampledUserPostDates
),
IntervalDurations AS (
    SELECT
        OwnerUserId,
        DATEDIFF(DAY, CreationDate, NextPostDate) AS IntervalDays
    FROM
        PostIntervals
    WHERE
        NextPostDate IS NOT NULL
)
SELECT
    OwnerUserId,
    MAX(IntervalDays) AS MaxIntervalDays
FROM
    IntervalDurations
GROUP BY
    OwnerUserId
ORDER BY
    MaxIntervalDays DESC;

view current version

edited dec 10

created dec 10 jon.doe30899