Create a Network chart of your forum - data visualization

Community wiki Administrators
,
1

Thanks to the Data Explorer Plugin and the query that @riking created, we have a great visualization of the interaction in our :discourse: instance.

You can also play with it.

It’s easy to do it:

1 - Install Data Explorer plugin

2 - Run the riking query or an modified version of it.

We use a version that only extracts from a specific group 
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
    WHERE post_action_type_id = 2
)
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs, group_users AS a, groups AS b
where ( liker = a.user_id
and a.group_id = b.id
and b.name ilike 'group1' )
and
liked in (select c.user_id from  group_users AS c, groups AS d
where
c.group_id = d.id
and d.name ilike 'group1' )
GROUP BY liked, liker
ORDER BY count DESC
You could do it another modified version of the SQL query that filter by period 
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
    WHERE post_action_type_id = 2 AND
    p.created_at >= CURRENT_DATE - INTERVAL '1 month'
)
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs, group_users AS a, groups AS b
where ( liker = a.user_id
and a.group_id = b.id
and b.name ilike 'group1' )
and
liked in (select c.user_id from  group_users AS c, groups AS d
where
c.group_id = d.id
and d.name ilike 'group1' )
GROUP BY liked, liker
ORDER BY count DESC

3 - Download the .csv

4 - Create a Fusion Table

5 - Upload the .csv

Captura de pantalla 2016-01-04 a las 17.32.53.png764×530 32.6 KB

6 - Add a network chart

Options: check “link is directional” to see who gives or receives likes from who and “color by columns” to see who receives more than gives or vice versa.

7 - Visualization!

In order to visualize names instead of ids, you only have to change the query to show usernames too, but be careful with data privacy! :smile:

19 Likes
2

I tried this recipe and I like it! Well done and many thanks for posting it.

I’d like to do this but the query to show usernames is a bit beyond my sql capabilities. I’d be grateful for assistance.

3

Replace these two lines with this to have the usernames instead:

SELECT u1.username_lower liker_username, u2.username_lower liked_username, count(*)
FROM pairs, group_users AS a, groups AS b
LEFT JOIN users u1 ON u1.id = liker
LEFT JOIN users u2 ON u2.id = liked
4 Likes
4

Bumping an old topic, as I’ve been experimenting with this a lot over the last few days… the network graphs have been very useful for identifying which members to look at in more detail, even just viewing user_id (though username would be far more valuable).

@riking I’ve tried your suggested change to the query in order include usernames (for example, using the first example query from this thread, looking at a specific user group):

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
    WHERE post_action_type_id = 2
)
SELECT u1.username_lower liker_username, u2.username_lower liked_username, count(*)
FROM pairs, group_users AS a, groups AS b
LEFT JOIN users u1 ON u1.id = liker
LEFT JOIN users u2 ON u2.id = liked
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs, group_users AS a, groups AS b
where ( liker = a.user_id
and a.group_id = b.id
and b.name ilike 'group1' )
and
liked in (select c.user_id from  group_users AS c, groups AS d
where
c.group_id = d.id
and d.name ilike 'group1' )
GROUP BY liked, liker
ORDER BY count DESC

That runs into this problem though:

PG::UndefinedColumn: ERROR: column “liker” does not exist
LINE 16: LEFT JOIN users u1 ON u1.id = liker
^
HINT: There is a column named “liker” in table “pairs”, but it cannot be referenced from this part of the query.

This is beyond my (extremely basic!) SQL knowledge to troubleshoot :frowning: Is there something obvious that I’m missing with this query?

3 Likes