みなさん、こんにちは..
データエクスプローラーを使ってユーザーのアクティビティを追跡するお手伝いをぜひお願いしたいです。
入力: ユーザー名と日付
私が期待するクエリの結果は以下のようになります。
| ユーザー名 | 日付 | 作成したトピック数 | 作成した投稿数 | 受け取ったいいね数 | 単語の総数 |
|---|---|---|---|---|---|
| ユーザーA | 2019-10-01 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| ユーザーB | 2019-10-01 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| ユーザーB | 2019-10-01 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| ユーザーC | 2019-10-01 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| … | |||||
| ユーザーZ | 2019-10-01 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 |
ご協力いただき、誠にありがとうございます…
挙げられた入力では、お望みの出力が得られないと思います。単一のユーザーのデータを取得したい場合、以下のようなクエリが出発点として機能するかもしれません。このクエリは共通テーブル式（CTE）を使用して、読み書きを容易にしています。このパターンを踏襲することで、結果にさらに多くのデータを追加することも可能です。
一つ確信が持てなかったのは、特定の日付におけるデータの合計値が必要なのか、それともその日に行われたアクションの回数が必要なのかという点です。例えば、2019-10-31 時点でユーザーがサイト滞在期間中に作成したトピックの合計数が 20 件であることを知りたいのか、それとも 2019-10-31 当日にユーザーが正確に 3 件のトピックを作成したことを知りたいのか、という点です。現在のクエリの構造では、後者の結果が返されるように設計されています。
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::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 = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
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 days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
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
ORDER BY d.day
毎日に行われたアクションの回数が知りたいです。あなたが提示してくれたクエリと全く同じように。
本当にありがとうございます、ご支援いただき感謝しております。私たちに大変役立っています。
ユーザーの活動を活発化させるため、当フォーラムで最も活発なユーザーを表彰するチャレンジを開催する予定です。
そのため、チャレンジをさらに面白くするため、追加のデータ取得を検討しています。
@simon 氏によるクエリで現在取得しているデータは、トピック数、投稿数、単語数、受け取ったいいね数です
追加で取得したいデータには以下が含まれます：
これらの追加データを取得できるよう、ご協力をお願いいたします。
各項目の重み付けについては、Excel を使用して行う予定です。勝者を選定するための重み付けに関するご助言があれば、ぜひお聞かせください。
このチャレンジは真剣でありながらも楽しく行いたいと考えています。最高のユーザーとは、最も活発で、最も役立つ情報を提供し、かつ最も人気のある人の組み合わせです。 
引き続き、ご支援いただき誠にありがとうございます。
ご注意: データをグループ単位で取得することは可能でしょうか？そうすれば、グループ ID のみを指定するだけで済みます。今回の計画では、すべての参加者を一つのグループにまとめたいと考えています。現在、参加者のユーザー ID を一つずつ入力する必要があります。
@manchestermania さん、こんにちは
同じマンチェスター出身者です 
私の会社では、従業員によるコミュニティへのエンゲージメントを高めるため、同様のコンペティションを社内で実施しようと考えています。
コンペティションは実際に実施されましたか？データはどのように収集されましたか？また、最終的にさまざまなエンゲージメント指標をどのように重み付けしましたか？
ぜひお話ししたいです！
ありがとうございます
ジュリア