1 2 Previous Next 22 Replies Latest reply on Apr 4, 2013 1:23 PM by Nimish Garg

# SQL Challenge

My teacher presented us with the following challenge.

There is a table that has 3 columns:
- Team
- Date of the match
- Won or Lost

Write an SQL Query (no PL/SQL), that returns the team or teams that has/have the highest winning streak and the number of consecutive wins.

Only 'regular' SQL is allowed, so no Oracle-specific functions.

I have already (I think) solved this, but I was wondering what solutions you guys would come up with.

I'll post my solution later.

Below I've provided some sample DDL.
``````CREATE TABLE Games(Team VARCHAR2(10), MatchDate DATE, WL VARCHAR2(1));
INSERT INTO Games VALUES ('TeamA','1-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','2-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','3-JAN-2012','W');
INSERT INTO Games VALUES ('TeamB','4-JAN-2012','W');
INSERT INTO Games VALUES ('TeamB','5-JAN-2012','W');
INSERT INTO Games VALUES ('TeamB','6-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','7-JAN-2012','L');
INSERT INTO Games VALUES ('TeamA','8-JAN-2012','W');
INSERT INTO Games VALUES ('TeamA','10-JAN-2012','W');``````
Edited by: popovitsj on Apr 3, 2013 7:50 AM

Edited by: popovitsj on Apr 3, 2013 8:21 AM

Edited by: popovitsj on 3-apr-2013 12:23

Edited by: popovitsj on 3-apr-2013 12:36
• ###### 1. Re: SQL Challenge
MatchDate NUMBER(8,0)
You lost me when you defined a date as a number.

According to your data, TeamA had a win and a loss on the same day
``````INSERT INTO Games VALUES ('TeamA',3,'W');
INSERT INTO Games VALUES ('TeamA',3,'L');``````
If that's correct, how can we know which occurred first?
• ###### 2. Re: SQL Challenge
To add - what is 'regular sql' ?
• ###### 3. Re: SQL Challenge
I defined date as a number to simplify the test data.

I don't think that matters.

You are right that you shouldnt have wins and losses on the same day for the same team. Sorry about that. I edited the sample data to fix that.

Edited by: popovitsj on Apr 3, 2013 7:50 AM
• ###### 4. Re: SQL Challenge
Only regular sql means no database specific date-functions and such, but he didn't elaborate on it.
• ###### 5. Re: SQL Challenge
Hi,
popovitsj wrote:
I defined date as a number to simplify the test data.

I don't think that matters.
It always wrong to use a NUMBER for date information. Use a DATE, or maybe TIMESTAMP, instead.
If all you care about is putting the matches in order, then call the column something like match<b>seq</b>, not match<b>date</b>.

A Fixed-Difference approach can do this. See {message:id=9953384} and/or {message:id=9957164}

What if there happens to be a tie? For example, in your sample data, it looks like the longest winning streak is 3 games, and two different teams have achieved that. Do you want to see both 'TeamA' and 'TeamB' in the results, or do you just need to see one or the other? You said you wanted "the team or teams", which sounds like you want both, but it makes the solution more complicated, so I want to be sure.
• ###### 6. Re: SQL Challenge
popovitsj wrote:
I defined date as a number to simplify the test data.

I don't think that matters.

You are right that you shouldnt have wins and losses on the same day for the same team. Sorry about that.
It does matter. It changes how data is queried, how it is handled...it changes everything. It changes how those that are trying to help you handle your request.

If you are requesting help, I would suggest that when asked to clarify an issue, not to basically reply "don't worry about it."
• ###### 7. Re: SQL Challenge
My apologies. I have now fixed the sample data.
• ###### 8. Re: SQL Challenge
Hi,

Using the Fixed Difference technique, I did this in 30 lines of code (I have a pretty verbose style), and 2 sub-queries.

That was for the solution the shows both 'TeamA' and 'TeamB', since they tied for the longest winning streak.
If you just need to see that the longest winning streak was 3 games, and one of the teams that did it, I did that in 20 lines and 1 sub-query, but I used the LAST function, and it's unclear if you're allowed to use that.
popovitsj wrote:
I have already (I think) solved this, but I was wondering what solutions you guys would come up with.

I'll post my solution later.
Right back at you.
• ###### 9. Re: SQL Challenge
You're saying you want me to post first? :p
• ###### 10. Re: SQL Challenge
Hi,
popovitsj wrote:
You're saying you want me to post first? :p
I'll wait a few days; hopefully, the assignment will be due by then.

However, you have the links that explain the fixed difference technique; you can get the same solution, or a better version, yourself.
• ###### 11. Re: SQL Challenge
I believe that this gives you what you are looking for....
``````WITH wins
AS (  SELECT *
FROM games
WHERE WL = 'W'
ORDER BY MatchDate DESC)
SELECT team, COUNT (*) WL
FROM wins
GROUP BY team
;``````
giving output of
``````TEAM         WL
TeamA         5
TeamB         3``````
• ###### 12. Re: SQL Challenge
LostInPermuation wrote:
I believe that this gives you what you are looking for....
``````WITH wins
AS (  SELECT *
FROM games
WHERE WL = 'W'
ORDER BY MatchDate DESC)
SELECT team, COUNT (*) WL
FROM wins
GROUP BY team
;``````
giving output of
``````TEAM         WL
TeamA         5
TeamB         3``````
This is not correct. TeamA has a highest Winning Streak of 3, not 5.

Edited by: popovitsj on 3-apr-2013 12:07
• ###### 13. Re: SQL Challenge
Below is my solution.

I would like to hear if you guys think this is correct.
``````WITH t1 AS
(
SELECT RowNum AS T1RowNum, Team, MatchDate, WL
FROM Games
ORDER BY Team, MatchDate
),
t2 AS
(
SELECT t1a.Team, t1a.MatchDate, t1a.WL,
Team ||
(
SELECT MIN(MatchDate)
FROM t1 t1b
WHERE t1a.Team = t1b.Team
AND WL = 'W'
AND t1b.T1RowNum < t1a.T1RowNum + 1
AND t1b.T1RowNum >
COALESCE
(
(
SELECT MAX(T1RowNum)
FROM t1
WHERE Team = t1a.Team
AND T1RowNum < t1a.T1RowNum
AND WL = 'L'
),
(
SELECT MIN(T1RowNum) - 1
FROM t1
WHERE Team = t1a.Team
AND T1RowNum < t1a.T1RowNum + 1
)
)
) AS StreakTag
FROM t1 t1a
WHERE t1a.WL = 'W'
), t3 AS
(
SELECT StreakTag, Team, COUNT(*) AS Cnt
FROM t2
GROUP BY StreakTag, Team
)
SELECT Team, Cnt FROM t3
WHERE Cnt =
(
SELECT MAX(Cnt) FROM t3
);``````
• ###### 14. Re: SQL Challenge
OK,

I'll post first:
``````WITH A AS
(select 'TeamA' team ,to_date('1-JAN-2012') matchdate ,'W' WL from dual UNION ALL
select 'TeamA', to_date('2-JAN-2012'),'W' WL from dual UNION ALL
select 'TeamA', to_date('3-JAN-2012'),'W' WL from dual UNION ALL
select 'TeamB', to_date('4-JAN-2012'),'W' WL from dual UNION ALL
select 'TeamB', to_date('5-JAN-2012'),'W' WL from dual UNION ALL
select 'TeamB', to_date('6-JAN-2012'),'W' WL from dual UNION ALL
select 'TeamA', to_date('7-JAN-2012'),'L' WL from dual UNION ALL
select 'TeamA', to_date('8-JAN-2012'),'W' WL from dual UNION ALL
select 'TeamA', to_date('10-JAN-2012'),'W' WL from dual
)
,B AS
(
SELECT
TEAM
,COUNT( TEAM) OVER (PARTITION BY TEAM ORDER BY MATCHDATE) CNT
,WL
,CASE WHEN WL = 'W' THEN 0 ELSE COUNT( TEAM) OVER (PARTITION BY TEAM ORDER BY MATCHDATE) END L
,MATCHDATE
FROM
A
)
,C AS
(SELECT
TEAM
,CNT - MAX(L) OVER (PARTITION BY TEAM ORDER BY MATCHDATE) ST
FROM
B
)
,D AS
(SELECT
TEAM
,ST
,MAX(ST) OVER (PARTITION BY 1) LST
FROM
C
)
SELECT
TEAM
,LST

FROM
D
WHERE
ST = LST

TEAM  LST
----- ---
TeamA   3
TeamB   3 ``````
Regards,

Peter