September 28, 2007SQL Challenge #2: Top-ten Members Analysis
Comments (0)
phpBB Doctor SQL Challenges
This is the second SQL Challenge from the phpBB Doctor. It is my hope that this series of posts will be interesting but educational as well. The concept is simple: I will post a challenge that must be solved with (unless stated otherwise) a single SQL statement. No php code, no “insert into…” temporary tables, just one simple (or more likely not-so-simple!) SQL statement. There is often more than one way to solve the same problem, so I personally will be interested to see some of the solutions that are presented. Eventually I will post my original answer and optionally the best answers (judged by me) submitted to the challenge as well. Some of the challenges will use phpBB tables and data, others may use special data that I will create and make available for you to download.
What do you win? You win the knowledge that you solved a challenge. Is that enough for you? 
This one, in my opinion, is tough. In my solution I use several different SQL tricks that you may or may not have been exposed to. But give it a shot and see what you come up with. 
The Setup
For this challenge you should be able to use your own board. You need at least three months of posting data in order to attempt this challenge. The more data you have, the more interesting this query becomes, at least in my opinion. This challenge is based on a real-world question that I wanted to answer the other day. In many cases the “top ten” posters are in that position because they have remained active for a long time. My biggest board is now over five years old, and over time some of the top-ten posters have gradually been falling down (or off of) the list. So I thought it would be interesting to write a query that would identify the current top-ten users and then show me their average monthly post count for the last three months. That way I would get an indication of how many of my top-ten posters were still showing any level of activity.
The Challenge
You want to know the average number of posts per month made by your top-ten posters over the past three months. You need to determine the top-ten posters from your board inception date and then show the average number of posts each of them has made in the last three months. The technique that I used requires version 4.1 of MySQL at minimum.
You can assume that the value in user_posts is accurate and use it to determine your top-ten posters. In many cases the user_posts value is not 100% accurate, either because of pruning (which does not reduce the post count) or MODs (some board admins have forums that do not increment your post count). I won’t worry about that for this challenge, treat user_posts as being the correct value.
Here are the results that I got, with the names obscured for privacy:
+--------------+-----------------+ | username | avg(post_count) | +--------------+-----------------+ | 1afa4b18ad00 | 202.0000 | | 3ec682b8d52e | 25.0000 | | 586c0d4f087d | 120.3333 | | 87e25d48d6a8 | 0.0000 | | 97ec7e2e5296 | 13.6667 | | a719c7a5e035 | 115.3333 | | c1909711326d | 119.6667 | | cac58b5234e1 | 154.0000 | | f6bc46b581ba | 0.0000 | | f8b21c8ff704 | 179.0000 | +--------------+-----------------+
As you can see, I got my answer. Two of my current top-ten users have not done a thing (average of 0.0 posts) in the last three months. If you are curious, the username is obscured by running it through the md5() function and then chopping off some of the characters. Ultimately this query did not tell me anything that I had not already been able to derive simply by looking at the data. But it does use some interesting SQL techniques.
Comments (0)