Discussion on: How I do solve this impossible SQL query?

[Francois LAGIER]

Obviously, I'm not 100% sure since I don't access to the table but I will try something like this:

  SELECT 
        articles.id,
        articles.title,
        articles.published_at,
        articles.positive_reactions_count AS total_reactions,
        articles.cached_tag_list,
        collections.slug AS series_title,
        SUM(CASE WHEN reactions.category = 'like' THEN 1 ELSE 0 END) AS total_likes,
        SUM(CASE WHEN reactions.category = 'unicorn' THEN 1 ELSE 0 END) AS total_unicorns,
        SUM(CASE WHEN reactions.category = 'readinglist' THEN 1 ELSE 0 END) AS total_bookmarks,
        SUM(page_views.counts_for_number_of_views) from total_views,
        AVG(page_views.time_tracked_in_seconds) from total_views
  FROM articles
  FULL JOIN collections ON collections.id = articles.collection_id
  INNER JOIN reactions ON reactions.reactable_id = articles.id AND reactions.reactable_type = 'Article'
  INNER JOIN page_views ON page_views.article_id = articles.id
  WHERE articles.user_id = my_id
  AND articles.published = TRUE
  GROUP BY 1,2,3,4,5,6

This query should work if the reactions is a snapshot and not just a log.

I always love playing with weird SQL so happy to help. Feel free to reach out at [e-mail]

[Sebastian]

Do the aggregation functions work in that query? Looks like you could add a group by at the end, like:
"
group by
articles.id,
articles.title,
articles.published_at,
articles.positive_reactions_count,
articles.cached_tag_list,
collections.slug
"

[Francois LAGIER]

Of course not ... I forgot the group by. Thanks for pointing that.