SQL Tuesday

[Albatross]

So here is some sample data

http://sqlfiddle.com/#!2/5e844

I want the id of the employee which is present (status='P') at least 5 days in a row.So for the sample data,1 and 4.I already solved it,I`ll post the answer later.If you post the answer as text use spoilers.:awe:

 

[Cerb]

[SPOILER]-- goes in schema side, since MySQL can't self-join subqueries         
CREATE VIEW onlyp AS                                                   
  SELECT id, date                                                      
  FROM t                                                               
  WHERE status = 'P'                                                   
;                                                                      
SELECT onlyp.id                                                        
FROM onlyp                                                             
  INNER JOIN onlyp AS o4 ON onlyp.id = o4.id AND onlyp.date = o4.date+4
  INNER JOIN onlyp AS o3 ON onlyp.id = o3.id AND onlyp.date = o3.date+3
  INNER JOIN onlyp AS o2 ON onlyp.id = o2.id AND onlyp.date = o2.date+2
  INNER JOIN onlyp AS o1 ON onlyp.id = o1.id AND onlyp.date = o1.date+1
;                                                                      [/SPOILER]

With the limited data, though, you could get the right answer with a wrong query. Here's a "clever" query that returns the right answer with the given data, but is wrong.

[SPOILER]SELECT distinct s1.id                                                  
FROM onlyp                                                             
  INNER JOIN onlyp AS o4 ON onlyp.id = o4.id AND onlyp.date = o4.date+4
  INNER JOIN                                                           
  (                                                                    
    SELECT s0.id                                                       
    FROM (SELECT id, COUNT(date) AS cd                                 
          FROM onlyp                                                   
          GROUP BY id) AS s0                                           
    WHERE s0.cd = 5                                                    
  ) AS s1 ON onlyp.id = s1.id                                          
;                                                                      [/SPOILER]

In general, this would be a good thing to use DBMS-specific features for, like CTEs, to find gaps instead of just brute-force verify the series.

 

[Albatross]

@Cerb Thanks for posting,you`re correct,on other dbs you have window functions or CTEs or ROW function but I wanted it to be challenging,that`s why I left it on Mysql. +1 for noticing that correct result != correct query.Anybody else?

 

[BoberFett]

Mine was similar to your first answer Cerb.

[SPOILER]select distinct t1.ID
from t t1
left join t t2 on t1.date = t2.date + 1
left join t t3 on t2.date = t3.date + 1
left join t t4 on t3.date = t4.date + 1
left join t t5 on t4.date = t5.date + 1
where t1.status = 'P' 
and t2.status = 'P'
and t3.status = 'P'
and t4.status = 'P'
and t5.status = 'P'[/SPOILER]

 

[Albatross]

@Boberfett Thanks for posting. So here is my answer :

Spoiler

SELECT id ,status FROM
(SELECT id,status,
CASE WHEN date=@last_ci+INTERVAL 1 DAY THEN @n ELSE @n:=@n+1 END AS g,
@last_ci := date As date
FROM
t, (SELECT @n:=0) r
ORDER BY id,date)x
GROUP BY
id,g
HAVING SUM(status='P')>4

If anybody else has what he thinks are interesting queries setup a sqlfiddle and start your thread,it will be fun,no matter the db flavor .

 

[ninaholic37]

I thought this thread said "Squirrel Tuesday". The :thumbsup: kinda looks like one too...

 

[slugg]

We should have a Functional Friday... 🙂