Original post: https://lemmy.ml/post/1848545 or https://mylemmy.win/post/117634 - reconciles all users, foreign and domestic. This may take a VERY long time (minutes, hours, days on a really big instance).

The following, modified version, reconciles only local users. For those interested in supporting users expecting these values to be surfaced like “karma” in apps that support it. Much faster than reconciling the entire user database. (works with 18.1; maybe later, maybe not; use at your own risk)

MERGE INTO "person_aggregates" AS "d"
 USING (SELECT "m"."id" AS "person_id"
             , coalesce("p"."post_count", 0) AS "post_count"
             , coalesce("p"."post_score", 0) AS "post_score"
             , coalesce("c"."comment_count", 0) AS "comment_count"
             , coalesce("c"."comment_score", 0) AS "comment_score"
          FROM "local_person" AS "m"
          LEFT JOIN (SELECT "p"."creator_id"
                          , count(distinct "p"."id") AS "post_count"
                          , sum("l"."score") AS "post_score"
                       FROM "post" AS "p"
                       LEFT JOIN "post_like" AS "l"
                         ON "l"."post_id" = "p"."id"
                      WHERE NOT "p"."removed"
                        AND NOT "p"."deleted"
                        AND "l"."person_id" <> "p"."creator_id"
                      GROUP BY "p"."creator_id") AS "p"
            ON "p"."creator_id" = "m"."id"
          LEFT JOIN (SELECT "c"."creator_id"
                          , count(distinct "c"."id") AS "comment_count"
                          , sum("l"."score") AS "comment_score"
                       FROM "comment" AS "c"
                       LEFT JOIN "comment_like" AS "l"
                         ON "l"."comment_id" = "c"."id"
                      WHERE NOT "c"."removed"
                        AND NOT "c"."deleted"
                        AND "l"."person_id" <> "c"."creator_id"
                      GROUP BY "c"."creator_id") AS "c"
            ON "c"."creator_id" = "m"."id"
         ORDER BY "m"."id") AS "s"
    ON "s"."person_id" = "d"."person_id"
  WHEN MATCHED AND ("d"."post_count" <> "s"."post_count" OR
                    "d"."post_score" <> "s"."post_score" OR
                    "d"."comment_count" <> "s"."comment_count" OR
                    "d"."comment_score" <> "s"."comment_score")
  THEN UPDATE
          SET "post_count" = "s"."post_count"
            , "post_score" = "s"."post_score"
            , "comment_count" = "s"."comment_count"
            , "comment_score" = "s"."comment_score";