Hi! I’m looking for a bit of help modifying a query to return usernames, instead of user ID numbers.
This is what I’ve been using so far (modified from @DavidGNavas’ excellent Network Chart thread)
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.
For example, compare our musician community with our graphic designers - there’s a big difference between the two