Q4

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

/*
Show the top 3 users (and their name) every month by ViewCount

*/
WITH cte_base as (
SELECT
  U.ID, 
  U.DisplayName,
  MONTH(U.CREATIONDATE) MM,
  YEAR(U.CREATIONDATE) YYYY,
  SUM(P.VIEWCOUNT) AS S_VIEWCOUNT
FROM USERS U
INNER JOIN POSTS P ON U.ID = P.OWNERUSERID
GROUP BY   
  U.ID, 
  U.DisplayName,
  MONTH(U.CREATIONDATE),
  YEAR(U.CREATIONDATE)
--ORDER BY YYYY,MM
)

SELECT TOP 3
  ID, 
  DisplayName,
  S_VIEWCOUNT,
  YYYY,MM
FROM cte_base U
--

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results