I am trying to make a query to take first 3 categories with most posts per user, but no luck so far. This is where I am now:
SELECT DISTINCT(terms.term_id) as term_ID, terms.name, terms.slug, posts.post_author, t0.count_id FROM wp_posts as posts JOIN wp_term_relationships as relationships ON posts.ID = relationships.object_ID JOIN wp_term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id JOIN wp_terms as terms ON tax.term_id = terms.term_id JOIN ( SELECT COUNT(*) as count_id, count_terms.term_id FROM wp_posts as posts_count JOIN wp_term_relationships as count_relationships ON posts_count.ID = count_relationships.object_ID JOIN wp_term_taxonomy as count_tax ON count_relationships.term_taxonomy_id = count_tax.term_taxonomy_id JOIN wp_terms as count_terms ON count_tax.term_id = count_terms.term_id WHERE count_tax.taxonomy = "category" AND posts_count.post_status = "publish" AND posts_count.post_author in (1,2,3) group by count_terms.term_id ) as t0 on posts.post_author in (1,2,3) WHERE tax.taxonomy = "category" AND posts.post_status = "publish" AND posts.post_author in (1,2,3)
Which returns the users with categories, but with total sum of all the posts in those categories for this users. And I need first 3 categories with most posts per user. Any idea how to do that?