Comment Ranking

From a table including the fields user name, rank, number of comments, write the SQL query which updates the rank based on the number of comments: the user with the more comment will have the better rank (1).

I found it a lot more interesting to use real world data so I am polling the comment data from Wonderland Labs into a new table to accomplish this task.

This is the display function that echoes table state.

<?php
function _show_user_ranks() { ?>
<table>
<?
$head_made = false;
$result = db_query('select * from {user_comment_ranks} ORDER BY rank, `user name`');
while ($row = db_fetch_array($result)) {
    if (!$head_made) {
        $head_made = true;
?>
    <tr>
<? foreach(array_keys($row) as $head) echo '<th>', $head, '</th>'; ?>
    </tr>
<? } ?>
    <tr>
<? foreach($row as $value) echo '<td>', $value, '</td>'; ?>
    </tr>
<? } ?>
</table>
<?
}

The following code initializes a new table (in order not to contaminate the workign site) and loads it with data from this site.

/*  creating initial table;  braces are a "drupalism" to localize table names with prefixes on shared servers */  
$query = <<<QUERY CREATE TABLE IF NOT EXISTS {user_comment_ranks} (
   `uid` int(11) NOT NULL,   
`user name` varchar(200) NOT NULL,
   `rank` int(11) NOT NULL,   
`number of comments` int(11) NOT NULL,
   PRIMARY KEY (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
QUERY;
db_query($query);

  /* reset the table if necessary */ 
db_query('TRUNCATE {user_comment_ranks}'); 

Real data is transferred from Drupal into the new table.

/* inserting ranking information into table */

$query = <<<QUERY
INSERT INTO {user_comment_ranks} (uid, `user name`)
SELECT uid, name FROM {users};
QUERY;
db_query($query);

Finally -- the query asked for by the initial question, updates the new table with ranking qualification.

$query = <<<QUERY
    UPDATE {user_comment_ranks} u
    SET `number of comments` =  (SELECT count(*) FROM {comments} c
    WHERE c.uid = u.uid);
QUERY;
db_query($query);
?>
<h2>With Comment Tally</h2>
<?= _show_user_ranks(); ?>

<h2>With Rank Tallies</h2>
<?
$query = <<<QUERY

UPDATE {user_comment_ranks} u
	INNER JOIN (
   	
   	SELECT DISTINCT ur.uid, (
SELECT count( * ) AS url_count
FROM {user_comment_ranks} url
WHERE url.`number of comments` >= ur.`number of comments`
) AS r
FROM {user_comment_ranks} ur

   ) as dur USING (uid)
SET u.rank = dur.r
QUERY;
db_query($query);
?>
<?= _show_user_ranks(); ?>

A few notes

Drupal allows anonymous comments which are assigned to "User zero". This system will treat user zero as an actual record and of course, that user is assigned a tally which is the highest total in this case.

The Rank number indicates how many users have MORE posts than you (+ 1). Because of ties, the rank is not a linear value - in this case (as of last check -- there is a big "hump" of users at rank 961 becuase there are 961 users who have the same or fewer comments as each other.

Initial User List