Elsevier analytics teams need to assess citation activity for papers published within a defined time frame. Write a PostgreSQL query that calculates each paper's citation count and the overall average citation count per paper using a window function.
2023-01-01 through 2024-12-31.| Column | Type | Description |
|---|---|---|
| paper_idPK | INT | Unique identifier for the paper |
| title | VARCHAR(200) | Title of the paper |
| publication_date | DATE | Date the paper was published |
| Column | Type | Description |
|---|---|---|
| citation_idPK | INT | Unique identifier for a citation record |
| paper_id | INT | Identifier of the cited paper |
| citation_date | DATE | Date the citation was recorded |
| citation_source | VARCHAR(50) | Source that supplied the citation record |