Popularity Percentage

Each friendship is stored as one row but counts for both people, so the whole solution rests on duplicating every edge in reverse with UNION ALL — turning an undirected graph into a directed one where every user appears as user1 with their full friend list — then dividing each user's distinct-friend count by a single scalar total-user count fanned out across every row with a CROSS JOIN.

The problem

Table Friends(user1 INT, user2 INT), primary key (user1, user2). Each row is one friendship, and the friendship is undirected: a row (2, 1) means 2 and 1 are mutual friends, and the pair is stored only once. For every user, return percentage_popularity = (number of friends) ÷ (total users on the platform) × 100, rounded to 2 decimals, ordered by user1 ascending.

Sample input (the 9 rows that drive the worked example below):

Friends
+-------+-------+
| user1 | user2 |
+-------+-------+
|   2   |   1   |
|   1   |   3   |
|   4   |   1   |
|   1   |   5   |
|   1   |   6   |
|   2   |   6   |
|   7   |   2   |
|   8   |   3   |
|   3   |   9   |
+-------+-------+

The single hard idea: user 1 appears in column user1 only twice (rows 1,3 and 1,5, 1,6) but actually has five friends (2, 3, 4, 5, 6) — the other three friendships hide in user2. You cannot just GROUP BY user1 on the raw table; you would undercount everyone who happens to sit in the right-hand column.

The mechanism: make every edge bidirectional

An undirected edge {a, b} contributes a friend to a and a friend to b. SQL can only group by a column, so we manufacture the missing direction: keep every original row, and UNION ALL a copy with the columns swapped. Now each friendship exists as two directed rows (a→b) and (b→a), and a plain GROUP BY user1 sees the complete friend list of every user.

UNION ALL (not UNION) is correct here on purpose: the two halves can never collide, so there is nothing to deduplicate, and UNION would pay for a needless sort/hash dedupe pass over the whole doubled set.

The full query

WITH two_way AS (                       -- 1. directed edges, both ways
    SELECT user1, user2 FROM Friends
    UNION ALL
    SELECT user2 AS user1, user1 AS user2 FROM Friends
),
unique_users AS (                       -- 2. every distinct person
    SELECT user1 AS user_id FROM two_way
    UNION                               -- UNION (dedupes) on purpose here
    SELECT user2 AS user_id FROM two_way
),
total_users AS (                        -- 3. one scalar: N
    SELECT COUNT(*) AS total_count FROM unique_users
)
SELECT
    t.user1,
    ROUND(100.0 * COUNT(DISTINCT t.user2) / tu.total_count, 2) AS percentage_popularity
FROM two_way t
CROSS JOIN total_users tu              -- 4. fan N out onto every row
GROUP BY t.user1, tu.total_count
ORDER BY t.user1;

Note UNION ALL in step 1 (collisions impossible, skip dedupe) but plain UNION in step 2 (we want distinct user ids). The CROSS JOIN against a one-row table is just the clean way to attach a scalar to every group; total_count is repeated in GROUP BY only because it is a non-aggregated column. The arithmetic is written 100.0 * COUNT(...) / total_count — that ordering matters, and the next section explains why.

Worked example, fully traced

Run the sample 9 rows through each stage.

Stage 1 — two_way doubles to 18 directed rows

Originals (2,1)(1,3)(4,1)(1,5)(1,6)(2,6)(7,2)(8,3)(3,9) plus their swaps (1,2)(3,1)(1,4)(5,1)(6,1)(6,2)(2,7)(3,8)(9,3).

Stage 2–3 — distinct users, then count

unique_users = {1,2,3,4,5,6,7,8,9}, so total_count = 9. This 9 is the denominator for everyone.

Stage 4 — group, count distinct friends, divide

User 1 is the payoff: in the raw table user 1 sat in user1 for only three rows, yet here it correctly shows 5 friends — the two friendships (2,1) and (4,1) were recovered by the swapped copies.

The real systems trap: integer division silently returns 0

Look hard at the arithmetic. COUNT(DISTINCT t.user2) is a BIGINT and total_count is a BIGINT. For every user, the friend count is smaller than the total user count, so the true ratio is always between 0 and 1. In any engine where integer ÷ integer = integer, that ratio truncates to 0 before anything else happens — and you get a table of all 0.00 that looks plausible enough to ship.

Why the naive parenthesization is wrong

The original solution wrote ROUND(100.00 * (COUNT(...) / total_count), 2). The parentheses force COUNT / total_count to evaluate first, as a standalone integer division:

-- PostgreSQL / SQLite / SQL Server (integer / integer truncates):
100.00 * (5 / 9)   -- inner 5/9 -> 0  -> 100.00 * 0  = 0.00   WRONG

-- MySQL / MariaDB ('/' is ALWAYS float division):
100.00 * (5 / 9)   -- inner 5/9 -> 0.5556 -> 55.56          happens to work

So the page's query is a latent portability bug: it returns correct numbers on the LeetCode (MySQL) judge and a column of zeros the moment someone runs it on Postgres. The 100.00 literal does not save it, because the float multiply only sees the already-truncated 0.

The fix: let the float reach the division

Drop the inner parentheses so multiplication and division evaluate left-to-right, and the 100.0 float promotes the whole chain before the divide:

100.0 * COUNT(DISTINCT t.user2) / tu.total_count
-- = (100.0 * 5) / 9 = 500.0 / 9 = 55.5556  -> portable on every engine

Equivalent explicit fixes: cast one operand, COUNT(...) * 1.0 / total_count, or CAST(COUNT(...) AS DECIMAL) / total_count. The corrected query above uses the 100.0 * … / form for this reason.

Pitfalls

• Integer division returning all zeros. The headline trap above. It is invisible on the MySQL judge and breaks on Postgres/SQLite/SQL Server. Default to writing the float literal so it multiplies before the divide, or cast explicitly — never wrap the bare int / int in parentheses and hope.
• Forgetting DISTINCT in the count. The schema's PK (user1, user2) stops duplicate stored friendships, so on clean data COUNT(*) and COUNT(DISTINCT user2) agree. But if the source ever held the same pair twice (or a self-loop (5,5)), COUNT(*) would over-count friends. COUNT(DISTINCT user2) is the defensive choice.
• Using UNION where UNION ALL belongs (and vice-versa). Step 1 must be UNION ALL: an accidental UNION there would collapse genuine distinct directed rows and silently undercount in graphs that contain… well, it can't here because halves never collide, but the dedupe sort is pure wasted cost on large tables. Step 2 must be UNION: UNION ALL there would leave the same user id repeated many times and inflate total_count.
• Counting friends from the raw table. A naive SELECT user1, COUNT(*) FROM Friends GROUP BY user1 undercounts every user who appears in the user2 column — user 1 would show 3 instead of 5. The bidirectional step is not optional.
• ROUND banker's vs arithmetic rounding. Standard SQL ROUND(x, 2) is arithmetic (round-half-up) in MySQL/Postgres, but some engines/locales use round-half-to-even. None of the sample values land on a .005 boundary, so it doesn't bite here — just know the engine's rule before trusting the last digit on real money/metrics.

Takeaways

• An undirected relationship stored once becomes a directed one you can GROUP BY by UNION ALL-ing the row with its columns swapped — the core move for friend/mutual-edge problems.
• A single platform-wide scalar (the user count) is attached to every group with a CROSS JOIN to a one-row table; that's cleaner than a correlated subquery in the SELECT.
• The portability landmine is integer ÷ integer: when the numerator is always smaller than the denominator, it truncates to 0. Force float arithmetic before the division, and never paper over it with a leading float literal trapped outside the parentheses.
• Pick UNION vs UNION ALL by intent: ALL when collisions are impossible or wanted (cheaper), plain UNION only when you actually need dedupe.

Problem from LeetCode #1782-style "Popularity Percentage" (Meta/Facebook tagged) on the Friends schema. Worked example traced and arithmetically verified against a reference implementation (user 1 = 5/9 = 55.56). The integer-division portability analysis cross-checked against MySQL (float /) versus PostgreSQL/SQLite/SQL Server (truncating integer /) semantics per their respective documentation. Re-authored and deepened for this guide: corrected the latent parenthesization bug in the original 100.00 * (COUNT / total) formula, replaced the heavyweight MathJax SVG with a plain inline formula, and added the bidirectional-edge and integer-division mechanism diagrams.