In postgres 11 im implementing something in type of suggesting new tag for user. To achieve that im first counting amount of activities of specific user in specific category. Query looks as follows
select ac.id as cat_id from activities a join articles art on art.id = a.article_id join articles_categories ac on ac.id = art.category_id where a.user_id = 787 group by ac.id order by count(*) desc
Now as it returned categories id's from most often used to least often used i would like to use this ids to find other tags that are most often used within this categories, but user dont know them yet. For this i started to write following query:
select count(t.*) as occurence_per_cat, t.id, t.name, ac.id, ac.name as cat_name from tags t join articles_tags at on at.tag_id = t.id join articles art on art.id = at.article_id join articles_categories ac on ac.id = art.category_id group by ac.id, t.id order by occurence_per_cat desc
Now i would like to somehow use list of categories id's from first query, to sort results of second query first by category id, then by number of occurences, per category. I suspect i may be wrong about entire concept but cant figure this out. If anybody knows way of achieving the same, regardless if with approach i took or any other, it will be greatly appreciated.