Hi! I’m looking for a bit of help modifying a query to return usernames, instead of user ID numbers.
WITH pairs AS ( SELECT p.user_id liked, pa.user_id liker FROM post_actions pa LEFT JOIN posts p ON p.id = pa.post_id LEFT JOIN topics t ON t.id = p.topic_id LEFT JOIN categories c ON c.id = t.category_id WHERE post_action_type_id = 2 AND c.id = 47 ) SELECT liker liker_user_id, liked liked_user_id, count(*) FROM pairs GROUP BY liked, liker ORDER BY count DESC
Which I’ve then used a very clunky vlookup to match user names with ID numbers…
Once I have a list of pairs (likers & liked) expressed as usernames, it lets me follow the method used in the Network Chart thread to generate these sorts of charts - showing activity within specific categories, as we have very distinct sub-communities on different parts of our forums:
The live charts are a valuable way of exploring our different sub-communities, quickly identifying influential members and clusters of people.