Refs https://news.ycombinator.com/item?id=34222374 and https://ananthakumaran.in/2023/01/01/solving_n_plus_1_queries_on_rails.html

posts_comments_votes.sql

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL
);

CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INTEGER NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts(id)
);

CREATE TABLE votes (
  id INTEGER PRIMARY KEY,
  comment_id INTEGER NOT NULL,
  FOREIGN KEY (comment_id) REFERENCES comments(id)
);

INSERT INTO posts (id, title, content) VALUES
  (1, 'First post', 'This is the first post'),
  (2, 'Second post', 'This is the second post'),
  (3, 'Third post', 'This is the third post');

INSERT INTO comments (id, content, post_id) VALUES
  (1, 'First comment on first post', 1),
  (2, 'Second comment on first post', 1),
  (3, 'First comment on second post', 2),
  (4, 'Second comment on second post', 2),
  (5, 'First comment on third post', 3),
  (6, 'Second comment on third post', 3);

INSERT INTO votes (id, comment_id) VALUES
  (1, 1), (2, 1), (3, 1),
  (4, 2), (5, 2),
  (6, 3), (7, 3), (8, 3), (9, 3),
  (10, 4), (11, 4), (12, 4), (13, 4), (14, 4),
  (15, 5);

An example query:

with comment_vote_counts as (
  select
    comment_id,
    count(*) as vote_count
  from
    votes
  group by
    comment_id
),
comments_with_vote_counts as (
  select
    id,
    post_id,
    content,
    coalesce(vote_count, 0) as votes
  from
    comments
    left join comment_vote_counts on comments.id = comment_vote_counts.comment_id
)
select
  posts.id,
  posts.title,
  posts.content,
  json_group_array(
    json_object(
      'id',
      comments_with_vote_counts.id,
      'content',
      comments_with_vote_counts.content,
      'votes',
      comments_with_vote_counts.votes
    )
  ) as comments
from
  posts
  join comments_with_vote_counts on comments_with_vote_counts.post_id = posts.id
  group by posts.id

https://lite.datasette.io/?install=datasette-pretty-json&sql=https://gist.githubusercontent.com/simonw/3d6cbcd55beda108a88265a80f042726/raw/06ac070945874bdfe78442973ae76240cd8de370/posts_comments_votes.sql#/data?sql=with+comment_vote_counts+as+%28%0A++select%0A++++comment_id%2C%0A++++count%28*%29+as+vote_count%0A++from%0A++++votes%0A++group+by%0A++++comment_id%0A%29%2C%0Acomments_with_vote_counts+as+%28%0A++select%0A++++id%2C%0A++++post_id%2C%0A++++content%2C%0A++++coalesce%28vote_count%2C+0%29+as+votes%0A++from%0A++++comments%0A++++left+join+comment_vote_counts+on+comments.id+%3D+comment_vote_counts.comment_id%0A%29%0Aselect%0A++posts.id%2C%0A++posts.title%2C%0A++posts.content%2C%0A++json_group_array%28%0A++++json_object%28%0A++++++%27id%27%2C%0A++++++comments_with_vote_counts.id%2C%0A++++++%27content%27%2C%0A++++++comments_with_vote_counts.content%2C%0A++++++%27votes%27%2C%0A++++++comments_with_vote_counts.votes%0A++++%29%0A++%29+as+comments%0Afrom%0A++posts%0A++join+comments_with_vote_counts+on+comments_with_vote_counts.post_id+%3D+posts.id%0A++group+by+posts.id

Which outputs: