How to calculate percentage of "equal or better" in mysql DB?

Question

Dear All,

Figuring out a title for this question was hard, but the following is harder for me. I hope anyone can help.

I have a simple MySQL database table. This table is filled with records containing an ID and the number of week-visitors. It has records of 2 year of about 200+ websites.

To summarize, I want to be able to know two things:

1.) - "In week 54 of 2009 the website somethingonline.com had 300 visitors" (Easy of course. I can do this)

2.) - "The webiste sometingonline.com was among the 8% best scoring websites in that week."

Now, how can I get number 2.??? Of course, I want to know that percentage of all websites in every week so I get a list like:

1. sometingonline1.com - 300 visitors - 8% of the website score like this or better
2. sometingonline2.com - 400 visitors - 4% of the website score like this or better
3. sometingonline3.com - 500 visitors - 2% of the website score like this or better
4. sometingonline4.com - 600 visitors - 1% of the website score like this or better

How can I get these results? Is this possible in one query?

I use MySQL and PHP.

Answer 1

The key is to involve two different "copies" of your visits table. In this query v1 represents the website you're actually looking at. For each of those v1 websites, we'll join to a copy of the visits table, matching any row that covers a site with more visits in the same week.

SELECT v1.website_name, v1.visits, COUNT(v2.id)
FROM visits AS v1
INNER JOIN visits AS v2 ON (v1.week_number = v2.week_number AND v2.visits > v1.visits AND v2.id != v1.id)
WHERE week_number = 54

This will tell you the number of sites that had more visitors. To get that as a percentage, run a separate query to simply count the total number of sites that had any visits in that week. In your PHP script you can then do the simple division to get the percentage you want.