استعلام لإنشاء بعض المجموعات بناءً على النشاط

مجتمع البيانات والتقارير
13

لقد قمت بدمجها وتحسينها (بمهاراتي الضعيفة في SQL)، إذا احتجت إلى أسماء مستخدمين، أقوم فقط بتنزيل CSV ونسخ/لصق عمود اسم المستخدم.
أضفت likes_received_max حتى أتمكن من تقسيم المجموعات، باستثناء المجموعة أعلاه.

على سبيل المثال:
first_steps: 5 إعجابات (<30)، 500 منشور مقروء، >5 منشورات العام الماضي.
beginners: 30 إعجابًا (<100)، 1000 منشور مقروء، >10 منشورات العام الماضي.
padawan: 100 إعجاب، 2000 منشور مقروء، >10 منشورات العام الماضي.
hero: 200 إعجاب، 5000 منشور مقروء، >10 منشورات العام الماضي.

-- [params]
-- int :likes_received
-- int :posts_read
-- int :likes_received_max
-- int :posts_count


WITH user_activity AS (
    SELECT
        p.user_id,
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT
    us.user_id,
    u.username,
    us.likes_received,
    us.posts_read_count,
    ua.posts_count,
    u.title
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
  JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= :posts_count
  AND us.likes_received < :likes_received_max
ORDER BY 2 ASC, 3 ASC, 4 ASC