Rank Papers by Discipline

Elsevier uses discipline-level citation analytics to compare papers indexed in Scopus. Write a PostgreSQL query that counts citations for each paper and ranks papers within their academic discipline.

Requirements

1. Include every article associated with a valid discipline, including articles with zero citations.
2. Calculate each article's citation count from the citations table.
3. Return both RANK() and DENSE_RANK() rankings within each discipline, ordered by citation count descending.
4. Sort the final output by discipline name, citation count descending, and article ID.

Representative Sample Data

articles: 101, “RNA Folding in Cells”, Biology; 102, “Plant Genome Networks”, Biology; 107, “Graph Neural Models”, Computer Science; 108, “Efficient Vector Search”, Computer Science.

citations: three rows for article 101, two rows for article 102, and one row each for articles 107 and 108.