先月の当社の重要指標に基づくリーダーボードのクエリ構築を試みています：
- 受け取った「いいね」
- 解決されたトピック
- 獲得したバッジ
まずは User Participation クエリをベースにし、不要な部分を除去して、その後 badges gained（獲得したバッジ）の追加を始めました。バッジ獲得分のクエリ単体は以下のようになります：
-- [params]
-- string :interval = 1 month
-- string :trunc = month
SELECT user_id, count(id)
FROM user_badges
WHERE granted_at > date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval)
AND granted_at < date_trunc(:trunc, CURRENT_TIMESTAMP)
GROUP BY user_id
ORDER BY count DESC
これは問題なく動作しますが、これを修正済みの User Participation クエリに変換しようとした瞬間、頭が爆発してしまいました：
-- [params]
-- string :interval = 1 month
-- string :trunc = month
with
t as (
select date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) as start,
date_trunc(:trunc, CURRENT_TIMESTAMP) as end
),
pr as (
select user_id,
count(1) as visits,
sum(posts_read) as posts_read
from user_visits, t
where posts_read > 0
and visited_at > t.start
and visited_at < t.end
group by user_id
),
likes as (
select
post_actions.user_id as given_by_user_id,
posts.user_id as received_by_user_id
from t, post_actions
left join posts
on post_actions.post_id = posts.id
where post_actions.created_at > t.start
and post_actions.created_at < t.end
and post_action_type_id = 2
),
lr as (
select received_by_user_id as user_id,
count(1) as likes_received
from likes
group by user_id
),
e as (
select email, user_id
from user_emails u
where u.primary = true
),
b as (
SELECT b.user_id, count(1) as badges
FROM user_badges b, t, pr
WHERE granted_at > t.start
AND granted_at < t.end
AND pr.user_id = b.user_id
GROUP BY b.user_id
)
select pr.user_id,
username,
name,
email,
visits,
coalesce(likes_received,0) as likes_received,
coalesce(badges,0) as badges_recv
from pr
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by
likes_received desc,
visits desc
以下のエラーが発生しています（まだ「解決されたトピック」の追加すら始めていません）：
PG::UndefinedColumn: ERROR: column "badges" does not exist
LINE 65: coalesce(badges,0) as badges_recv ^
これは私には理屈が通じません。badges は明確に定義されているはずなのに… 詰まってしまいました。badges received（受け取ったバッジ）と topics solved（解決されたトピック）を追加してこのクエリを完成させるお手伝いをいただけると助かります！