سحب عدد المستخدمين لكل شهر ميلادي بشكل رجعي

البيانات والتقارير
10

سؤال مثير للاهتمام.

نظرت أولاً في المثال هنا. لكن هذا يتجاهل المستخدمين المحذوفين. تحصل فقط على عدد المستخدمين الذين تم تسجيلهم في ذلك الوقت ولا يزالون موجودين، وليس أولئك الذين تم حذفهم في هذه الأثناء.
كانت فكرتي لذلك هي أخذ معرف المستخدم الذي سجل آخر مرة في الشهر. هذا هو أقصى عدد ممكن من المستخدمين في ذلك الوقت. يمكن بعد ذلك طرح عدد المستخدمين المحذوفين من هذا. ومع ذلك، فإن حسابات الروبوتات (مثل forum-helper) لها معرف سلبي، ولكن يتم احتسابها إذا تم حذفها. (ولكن هذا ربما يكون انحرافًا طفيفًا). كان استعلامي:

-- [params]
-- date :start_date
-- date :end_date


WITH month_dates AS (
    -- Generate end-of-month dates between the start and end date
    SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
    FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
    -- For each end-of-month date, find the most recent user created before that date
    SELECT md.month_end,
           (SELECT id
            FROM users u
            WHERE u.created_at < md.month_end
            ORDER BY u.created_at DESC
            LIMIT 1) AS user_max_id
    FROM month_dates md
),
cumulative_deletion_count AS (
    -- Calculate the cumulative deletions up to each end-of-month date
    SELECT md.month_end,
           (SELECT COUNT(*)
            FROM user_histories uh
            WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
    FROM month_dates md
)
SELECT
    md.month_end,
    ru.user_max_id,
    cdc.deletions_count,
    ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
    month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end

لكن ما لا يأخذه في الاعتبار هو (إلغاء) التنشيط، والتي يتم تخزينها أيضًا في جدول user_histories. ولكن ربما يساعدك كنقطة انطلاق.

5 إعجابات