Find communities with Louvain and Leiden

Download the complete SQL script, including dataset setup for a fresh database.

Assign every person a community, compare group sizes, and inspect their members. Run the setup once, then execute this script.

Find and inspect communities

SELECT warren.louvain('example_social', 'louvain', seed := 42) AS louvain_modularity;
SELECT warren.leiden('example_social', 'leiden', seed := 42) AS leiden_modularity;

WITH labels AS (
    SELECT 'Louvain' AS algorithm, node_id, value::bigint AS community
    FROM warren.vector_values('example_social', 'louvain', NULL::bigint)
    UNION ALL
    SELECT 'Leiden', node_id, value::bigint
    FROM warren.vector_values('example_social', 'leiden', NULL::bigint)
), sizes AS (
    SELECT algorithm, community, count(*) AS members
    FROM labels GROUP BY algorithm, community
)
SELECT algorithm, count(*) AS communities, min(members) AS smallest,
       max(members) AS largest, round(avg(members), 1) AS average_size
FROM sizes GROUP BY algorithm ORDER BY algorithm;

SELECT c.value::bigint AS community, count(*) AS members,
       (array_agg(p.name ORDER BY p.id))[1:5] AS sample_members
FROM warren.vector_values('example_social', 'louvain', NULL::bigint) c
JOIN warren_examples.people p ON p.id = c.node_id
GROUP BY c.value ORDER BY members DESC, community LIMIT 10;

Each algorithm persists a label vector: louvain or leiden. A label is a community identifier, not an importance score or a people.id. Read it with vector_values and join node_id back to people.

The functions return modularity, a measure of how much more edge weight stays inside groups than expected under a model with the same degrees. The summary counts communities and reports their smallest and largest sizes. The last query shows sample members of the largest Louvain groups.

On pg_warren 0.7.0 with this dataset and seed 42, Louvain found 20 groups with modularity about 0.262; Leiden found 22 with modularity about 0.295. These are example results, not a guarantee for future algorithm versions or a general claim that Leiden always scores higher.

Interpret the grouping

Both algorithms use the undirected view, even though the source graph is directed. Different seeds can produce different partitions. Higher resolution favours smaller communities; lower values favour larger ones.

This synthetic network has no planted group structure. On real data, inspect communities against external attributes, such as geography or operating region, before assigning business meaning to them. See communities for the algorithms and their settings.