Introduction

The h-index of an author is the largest number h such that the author has at least h papers with at least h citations each. SQL is a good fit for this because the calculation is basically a ranking problem over each author's papers.

The Data Model

Assume a table like this:

1CREATE TABLE AuthorPapers (
2    AuthorID INT,
3    PaperID INT,
4    Citations INT
5);

Sample data:

1INSERT INTO AuthorPapers (AuthorID, PaperID, Citations) VALUES
2    (1, 101, 10),
3    (1, 102, 8),
4    (1, 103, 5),
5    (1, 104, 4),
6    (1, 105, 3),
7    (2, 201, 25),
8    (2, 202, 1),
9    (2, 203, 1);

For AuthorID = 1, the sorted citation counts are 10, 8, 5, 4, 3. That author has four papers with at least four citations, but not five papers with at least five citations, so the h-index is 4.

The Core SQL Idea

The standard pattern is:

1. rank each paper within an author by citation count descending
2. keep rows where citations >= rank
3. take the maximum such rank per author

Window functions make this simple.

Query for All Authors

1WITH ranked AS (
2    SELECT
3        AuthorID,
4        PaperID,
5        Citations,
6        ROW_NUMBER() OVER (
7            PARTITION BY AuthorID
8            ORDER BY Citations DESC, PaperID
9        ) AS paper_rank
10    FROM AuthorPapers
11)
12SELECT
13    AuthorID,
14    COALESCE(MAX(paper_rank), 0) AS h_index
15FROM ranked
16WHERE Citations >= paper_rank
17GROUP BY AuthorID
18ORDER BY AuthorID;

This works because paper_rank represents "the nth most cited paper." If the nth most cited paper still has at least n citations, then n is a valid h-index candidate.

Query for One Author

If you only need a single author, add a filter:

1WITH ranked AS (
2    SELECT
3        PaperID,
4        Citations,
5        ROW_NUMBER() OVER (
6            ORDER BY Citations DESC, PaperID
7        ) AS paper_rank
8    FROM AuthorPapers
9    WHERE AuthorID = 1
10)
11SELECT COALESCE(MAX(paper_rank), 0) AS h_index
12FROM ranked
13WHERE Citations >= paper_rank;

This is easier to read when debugging one author's result by hand.

Why ROW_NUMBER Works Better Than RANK

People often wonder whether RANK() or DENSE_RANK() is more appropriate because citations can tie. For h-index computation, ROW_NUMBER() is usually the cleanest tool because the definition depends on position in the sorted list of papers, not on grouping equal citation counts together.

For example, if an author has citation counts:

• 9
• 9
• 2

The paper positions are still first, second, and third. What matters is whether the third paper has at least 3 citations. It does not, so the h-index is 2.

Handling Authors with No Qualifying Papers

One subtlety: if you want every author in the result, including those with h-index 0, then you need a table of authors and a left join. Otherwise authors with no qualifying rows disappear from the grouped result.

Example:

1WITH ranked AS (
2    SELECT
3        a.AuthorID,
4        ap.PaperID,
5        ap.Citations,
6        ROW_NUMBER() OVER (
7            PARTITION BY a.AuthorID
8            ORDER BY ap.Citations DESC, ap.PaperID
9        ) AS paper_rank
10    FROM Authors a
11    LEFT JOIN AuthorPapers ap
12        ON a.AuthorID = ap.AuthorID
13)
14SELECT
15    AuthorID,
16    COALESCE(MAX(CASE WHEN Citations >= paper_rank THEN paper_rank END), 0) AS h_index
17FROM ranked
18GROUP BY AuthorID;

That version is more complete for reporting systems.

Performance Notes

For typical bibliometric datasets, the window-function solution is efficient and readable. Helpful indexes include:

• '(AuthorID, Citations DESC)'
• or at least (AuthorID, Citations)

The database still has to order papers within each author, but indexing can reduce the cost significantly.

If the dataset is huge and recalculated often, materializing sorted citation summaries or precomputed author metrics may be worth it.

Common Pitfalls

Using COUNT(*) alone is not enough because h-index depends on the relationship between citation count and sorted paper position.

Using RANK() with ties can produce unintuitive results because h-index cares about paper positions, not just distinct citation levels.

Forgetting COALESCE can leave authors with no qualifying papers out of the result or return NULL instead of 0.

Failing to specify a stable secondary sort such as PaperID can make tied citation rows appear in arbitrary order across runs.

Summary

• The h-index is the maximum rank where citations are still at least as large as the rank.
• In SQL, compute it by sorting papers per author with ROW_NUMBER().
• Filter rows where Citations >= paper_rank, then take the maximum rank.
• Use a left join and COALESCE if you need authors with h-index 0 included.
• Window functions make the query compact, readable, and efficient for this problem.