SQL for computing h-score (h-index)

Question

According to wikipedia:

A scientist has index h if h of his/her Np papers have at least h citations each, and the other (Np − h) papers have no more than h citations each.

Imagine we have SCIENTISTS, PAPERS, CITATIONS tables with 1-n relation between SCIENTISTS and PAPERS and 1-n relation between PAPERS and CITATION TABLES. How to write a SQL statement that would compute h-score for each scientist in SCIENTISTS table?

To present some research effort I did here is a SQL computing number of citations for each paper:

SELECT COUNT(CITATIONS.id) AS citations_count
FROM PAPERS
LEFT OUTER JOIN CITATIONS ON (PAPERS.id = CITATIONS.paper_id)
GROUP BY PAPERS.id
ORDER BY citations_count DESC;

Answer 1

What the h-value is doing is counting the citations in two ways. Let's say a scientist has the following citation counts:

10
 8
 5
 5
 2
 1

Let's the number that have that many or more citations, and the difference between the two:

10    1    9
 8    2    6
 5    3    2
 5    3    2
 2    5    -3
 1    6    -5

The number you want is where this is 0. In this case, the number is 4.

The fact that the number is 4 makes this hard, because it is not in the original data. That makes the calculation harder, because you need to generate a numbers table.

The following does this using SQL Server syntax for generating a table with 100 numbers:

with numbers as (
      select 1 as n
      union all
      select n+1
      from numbers
      where n < 100
     ),
     numcitations as (
      SELECT p.scientistid, p.id, COUNT(c.id) AS citations_count
      FROM PAPERS p LEFT OUTER JOIN
           CITATIONS c
           ON p.id = c.paper_id
      GROUP BY p.scientist, p.id
     ),
     hcalc as (
      select scientistid, numbers.n,
             (select count(*)
              from numcitations nc
              where nc.scientistid = s.scientistid and
                    nc.citations_count >= numbers.n
             ) as hval
      from numbers cross join
           (select scientistid from scientist) s
     )
select *
from hcalc
where hval = n;

EDIT:

There is a way to do this without using the numbers table. The h-score is the count of cases where the number of citations is greater than or equal to the citation count. This is much easier to calculate:

select scientistid, count(*)
from (SELECT p.scientistid, p.id, COUNT(c.id) AS citations_count,
             rank() over (partition by p.scientistid, p.id order by count(c.id) desc) as ranking
      FROM PAPERS p LEFT OUTER JOIN
           CITATIONS c
           ON p.id = c.paper_id
      GROUP BY p.scientist, p.id
     ) t
where ranking <= citations_count
group by scientistid;