特定ユーザーのアクティビティを調査する

[Jim.Morrison]

こんにちは、

特定のユーザー名について、ある日付範囲（日別）の「いいね」数、「受け取ったいいね」数、「投稿数」、および「解決済み」数を取得したいと考えています。このようなレポート作成に関するご提案はありますか？Data Explorer プラグインを活用する必要があると考えていますが、同様の用途に使えるクエリをお持ちの方はいらっしゃいますか？ご意見をお聞かせください！

[simon]

ローカル開発サイトでは、あなたが探しているものと非常に似たクエリを実行していました。要件に合わせて少し修正しました。試みたことの1つは、通常のトピックで発生したアクティビティか、プライベートメッセージ（PM）で発生したアクティビティのどちらかに対してクエリを実行できるようにすることです。これを行った動機の一部は、一定期間における自分のアクティビティを確認したかったためです。通常のトピックかPMかでクエリを実行できることは、私のケースでは理にかなっています。personal_messages チェックボックスがチェックされている場合のクエリによる解決策の扱いは理想的ではありません。その場合、solutions_provided カラムは単に NULL を返します。おそらく、通常のトピック/PM の切り替えはクエリから削除すべきでしょう。

このクエリは過度に複雑ではありませんが、かなり長くなっています。結果に問題がある場合は、お知らせください。

このクエリを実行するには、事前に3つのパラメータを指定する必要があります。username、start_date、end_date です。日付パラメータは ‘yyyy-mm-dd’ の形式である必要があります。例：2020-03-13。personal_messages パラメータのデフォルト値は false です。PM で発生したアクティビティの結果を表示したい場合は、クエリの personal_messages チェックボックスを選択してください。

--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false

WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 15
GROUP BY day, tu.id
)

SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC

[Jim.Morrison]

完璧です！@simon さん、ありがとうございます！