SELECT rtheme_title, COUNT( user_rank_theme ) AS theme_count
FROM  `phpbb3_users`
INNER JOIN  `phpbb3_rank_themes` ON user_rank_theme = rtheme_id GROUP BY rtheme_id

	// top five rank themes
	if (($user_ranks = $cache->get('_top_five_ranks')) === false)
	{
	    $user_ranks = array();

		// grab users with most posts
	    $sql = 'SELECT rtheme_title, COUNT( user_rank_theme ) AS theme_count, user_id
	       	FROM ' . USERS_TABLE . '
			INNER JOIN  ' . RANK_THEMES_TABLE . ' ON user_rank_theme = rtheme_id
			GROUP BY rtheme_id
			ORDER BY theme_count DESC';
		$result = $db->sql_query_limit($sql, 5);
		
		while ($row = $db->sql_fetchrow($result))
		{
			$user_ranks[$row['user_id']] = array(
				'user_id'		=> $row['user_id'],
                'user_rank_theme'		=> $row['user_rank_theme'],
			);
		}
        $db->sql_freeresult($result);

		// cache this data for five minutes, this improves performance
		$cache->put('_top_five_ranks', $user_ranks, 300);
	}

	foreach ($user_ranks as $row)
	{
		$template->assign_block_vars('top_five_ranks',array(
			'TRANKS' 			=> $row['theme_count'],
			'TRANKS_TITLE'		=> $row['rtheme_title'])
		);
    }