SUBQUERY to find waiting period after multiple occurances

Oh, dear! What a title! Let me explain the situation up front, so you can see what this is about (and understand why it was so hard to pick a catchy title for it).

In our example, we are managing a db for a test center. A user (customer) is allowed to take a specific test up to three times, after which he must wait 6 months to try that test again. This query result (a count) is passed back to the main application where the business logic is applied.

(Another useful purpose for this query might be, "Find all of the customers who have ordered more than X times with us, but now have not ordered anything for the last Y months").

First, let's make a sample table with some data:

(I'm going to keep this very simple, and hard-code values in my query so it is easier to understand. You may need to change the dates of the test_submissions; they should all be WITHIN 180 DAYS of Now() )

-- Table structure for table `test_submission`
--
CREATE TABLE IF NOT EXISTS `test_submission` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`test_id` int( 11 ) DEFAULT NULL ,
`user_id` int( 11 ) DEFAULT NULL ,
`result` varchar( 10 ) DEFAULT NULL ,
`ctime` datetime DEFAULT NULL ,
PRIMARY KEY ( `id` )
);

INSERT INTO `test`.`test_submission` (`id` ,`test_id` ,`user_id` ,`result` ,`ctime`)
VALUES (NULL , '1', '1', 'rejected', '2013-08-09 10:23:10'),
(NULL , '1', '1', 'rejected', '2013-08-15 10:43:41'),
(NULL , '1', '1', 'rejected', '2013-08-30 10:23:10');

a test submission row is created each time a test is taken. So, let's find out if this user can take the test again today.

Now, the logic to this is a little "inside-out", and so I hope I can explain it clearly. Let us start with our subquery, since this is where we are really restricting our rows:

SELECT DATE_ADD(MAX(TS2.ctime), INTERVAL -180 DAY)
FROM test_submission TS2
WHERE TS2.user_id = 1
AND TS2.test_id = 1
AND TS2.result = 'rejected'
AND NOW() < DATE_ADD(TS2.ctime, INTERVAL 180 DAY);

We need to find out, Has there been a rejection within the last 180 days?

"NOW() < DATE_ADD(TS2.ctime, INTERVAL 180 DAY)" let's us look at only those rows; "DATE_ADD(MAX(TS2.ctime), INTERVAL -180 DAY)" will then get a date 180 days earlier than the most recent of that subset. That's our starting point.

Now, from that return date ('2013-03-03 10:23:10' in this example), let's work forward again to find out, How many rejections have there been after that (i.e., within 180 days of the most recent failure)?

That's confusing. Let me try to summarize once more:

We want to see if there has been a rejection within the waiting period of 180 days. 
So we look at all the rejections, and take the most recent.

Now, just because he has a rejection within 180 days of today doesn't mean he's 
disqualified from taking the test again - we need to check that rejection to see 
if it is the 3rd in a 180 day period (because he only has to wait 180 days after 
his 3rd rejection). If it IS his 3rd failure, we already know he hasn't waited 
long enough.

I just messed you up even more, didn't I?

To simplify the explanation, I'll just plug in the date from above for now:

SELECT count(TS.id) as count
FROM test_submission TS
WHERE TS.user_id = 1
AND TS.test_id = 1
AND TS.result = 'rejected'
AND TS.ctime > ('2013-03-03 10:23:10');

The answer here is 3. For my own application logic, I've chosen to return the actual count. You could put a HAVING count(TS.id) > 3 and worked off a true/false, or whatever suits you.

3! Nope, he can't take it today - needs more study time.

Here is the final query, all in one piece:

SELECT count(TS.id) as count 
FROM test_submission TS 
WHERE TS.user_id = 1 
AND TS.test_id = 1 
AND TS.result =  'rejected'
AND TS.ctime > (SELECT DATE_ADD(MAX(TS2.ctime), INTERVAL -180 DAY)
                FROM test_submission TS2 
                WHERE TS2.user_id = 1
                AND TS2.test_id = 1
                AND TS2.result = 'rejected' 
                AND NOW() < DATE_ADD(TS2.ctime, INTERVAL 180 DAY));

Hope that helped!

Jeff Madsen is a web applications programmer living in Japan. 

Follow him on Twitter if that's your thing,
or drop him a line about an interesting project. 

Contact me