change "LIMIT 5" to whatever you want. If you want you can add WHERE clauses to ignore certain users, content types or unpublished content but this is your basic query.

SELECT users.name,users.uid, SUM( node_counter.totalcount ) AS 'Total Pageviews'
FROM node
JOIN node_counter ON node.nid = node_counter.nid
JOIN users ON node.uid = users.uid
GROUP BY node.uid
ORDER BY `Total Pageviews` DESC LIMIT 5