Your question is Average Citation Count by Time. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |