-- [params]
-- date :start_date
-- date :end_date
SELECT
pr.user_id,
(regexp_match(pr.modifications, 'category_id:\s*-\s*([0-9]+)\s*-\s*([0-9]+)'))[1]::int AS old_category_id,
(regexp_match(pr.modifications, 'category_id:\s*-\s*([0-9]+)\s*-\s*([0-9]+)'))[2]::int AS new_category_id,
COUNT(*) AS change_count
FROM post_revisions pr
WHERE pr.modifications LIKE '%category_id:%'
AND pr.modifications ~ 'category_id:\s*-\s*[0-9]+\s*-\s*[0-9]+'
AND pr.updated_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY pr.user_id, old_category_id, new_category_id
ORDER BY pr.user_id, change_count DESC
-- TOPICS MOVED LIST
-- [params]
-- date :start_date
-- date :end_date
-- category_id :cat_id
SELECT
pr.user_id, pr.post_id, pr.updated_at,
(regexp_match(pr.modifications, 'category_id:\s*-\s*([0-9]+)\s*-\s*([0-9]+)'))[1]::int AS old_category_id,
(regexp_match(pr.modifications, 'category_id:\s*-\s*([0-9]+)\s*-\s*([0-9]+)'))[2]::int AS new_category_id
FROM post_revisions pr
WHERE (regexp_match(pr.modifications, 'category_id:\s*-\s*([0-9]+)\s*-\s*([0-9]+)'))[1]::int = :cat_id::int
AND pr.updated_at::date BETWEEN :start_date::date AND :end_date::date
ORDER BY pr.updated_at DESC