While we were working at Indibits, on a project, we had a small requirement. We had to calculate all sign ups so far in this week. And our week starts from Saturday. I wanted to do it all in one shot. A single, short and optimized MySQL query which will return the count of all valid sign ups happened at or after 12 AM on last Saturday. And if today is Saturday, then we consider today as the date. But the problem was, how to get date of last Saturday? In PHP, it is almost easy. But in MySQL it was looking bit difficult. But when I started finding the solution, then I realized, it’s more difficult than I estimated. But I applied my logic and found a good and optimized solution in just 12 minutes.
So here is the challenge for you. Write a query, which will return date of last Saturday. And remember, if today is Saturday, then consider today as last Saturday. Remember, this will be in another query. The part of the query is:
SELECT COUNT(user_id) FROM users WHERE created >= ...
To participate in this contest email your solution to contests [at] indibits.com. You can take reference/help of anything you want. I request you to write about from where you take reference/help honestly (that I will not publish).
You must be thinking, what will be the prize for the best solution? Yes, there are many. This contest is sponsored by Indibits. And if you able to provide a good solution, then either you are a great developer or you have the potential to be great developer. So, Indibits may offer a full time job to you. Also, I may give you a surprise gift. Also, your solution will be published on this blog with full credit (your name, about you and a link to your blog/twitter/site). I will publish almost all correct entries, with credits (your name and a link to your blog/twitter/site).
Unable to solve this, but you need a job? Please check out all jobs at Indibits.
Do you need a web or business solution? We create really good web applications. Please visit our website or email us at development [at] indibits.com