Using 'sql_render' to transform a 'dplyer' request in an SQL request may be helpful to handle the error. If a deep dive is preferred, the main issue is that the ORDER BY clause within your SQL query, when used with dplyr::tbl and dbplyr, gets translated into a subquery, where ORDER BY is not allowed in SQL Server unless TOP, OFFSET, or FOR XML is also specified. To resolve this, remove the order by year(p.CreationDate) clause from your dbplyr::sql() call, as sorting should typically be handled after the data is retrieved into R.
show_query() can be used for detailed examination about SQL Server errors from dbplyr (e.g., dplyr::tbl(...) %>% show_query()) before attempting to execute the query, run that generated SQL directly in SQL Server Management Studio (or a similar tool), and get a detailed feedback about the query itself. Additionally, be aware of differences between SQL dialects when writing queries to be used with dbplyr::sql().