Data Explorer クエリ - スタッフ カバレッジ

[Vu_Tran_Nguyen]

皆さん、こんにちは。

特定の期間におけるスタッフの投稿/トピックの数と割合を確認するのに役立つデータエクスプローラークエリをご存知ですか？

ご協力ありがとうございます。

[michebs (Michelle)]

これで、以下のクエリがお役に立つことを願っています。

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

WITH posts_data AS (
    SELECT 
        COUNT(*) AS total_posts,
        SUM(CASE
            WHEN u.admin = 't' OR u.moderator = 't' THEN 1 
            ELSE 0
        END) AS staff_posts
    FROM posts p
    INNER JOIN users u ON u.id = p.user_id
    WHERE p. post_type = 1
        AND p.deleted_at ISNULL
        AND p.created_at BETWEEN :start_date AND :end_date
        AND p.user_id > 0
)
        
SELECT 
    total_posts, 
    staff_posts,
    ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data

total_posts	staff_posts	staff_posts %
7400	3108	42.0

[Vu_Tran_Nguyen]

@michebs様

共有いただきありがとうございます。
これを、私たちが作成した特定のグループに適用することはできますか？

例えば、スタッフのグループがあり、その全員がモデレーターや管理者ではない場合、「Katalon_team」という新しいグループを作成し、そのKatalonチームの全メンバーのデータを取得することはできますか？

よろしくお願いいたします。

[michebs (Michelle)]

クエリを調整し、グループ名をインプットパラメータとして追加しました。これにより、どのグループでも機能するようになります。お役に立てば幸いです。

--[params]
--date      :start_date = 2022-01-01
--date      :end_date = 2023-01-01
--string    :group_name = staff

WITH group_filter AS (
    SELECT user_id FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE LOWER(g.name) LIKE '%'||:group_name||'%'
),

posts_data AS (
    SELECT
        COUNT(*) AS total_posts,
        SUM(CASE
            WHEN gf.user_id ISNULL THEN 0
            ELSE 1
        END) AS staff_posts
    FROM posts p
    LEFT JOIN group_filter gf ON gf.user_id = p.user_id
    WHERE p. post_type = 1
        AND p.deleted_at ISNULL
        AND p.created_at BETWEEN :start_date AND :end_date
        AND p.user_id > 0
)

SELECT
    total_posts,
    staff_posts,
    ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data

[Vu_Tran_Nguyen]

Michelle様

SQLの更新ありがとうございます。添付の画像のように、実行時に問題が発生しました。

この問題を解決するにはどうすればよいか、アドバイスをお願いいたします。よろしくお願いいたします。

[michebs (Michelle)]

このエラーは、最後の SELECT、27 行目の欠落に関連しているようです。

SELECT 
    total_posts, 
    staff_posts,
    ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data

これは理にかなっていますか？

[Vu_Tran_Nguyen]

はい、すみません、私の間違いでした。ご協力いただきありがとうございました。