Newbie with Simplifying Query Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Newbie with Simplifying Query Question

Author

Topic

ImagineTNT
Starting Member

5 Posts

Posted - 2007-08-13 : 13:27:43

Hi all. Hope this isn't too newb question (I did do a search). For a job interview I was asked to solve a problem displaying a list of team scores (wins, losses, ties). If you're wondering about whether it's ok to ask for help 1) they didn't say I couldn't and 2) I will say I came up with my own answer but also asked for help here.

The database structure is as follows:

Robdt
Id gamedate
1 2002-08-04 00:00:00.000
2 2002-07-05 00:00:00.000
3 2002-10-06 00:00:00.000
4 2002-09-09 00:00:00.000
5 2002-11-15 00:00:00.000

robtm
id name
1 REDS
2 ORIOLES
3 RED SOX
4 PADRES

robtm_gm
id game_id team_id score
1 1 1 4
2 1 3 4
3 2 2 1
4 2 3 6
5 3 4 21
6 3 1 8
7 4 2 2
8 4 1 10

The output should be in table form somewhat like this:
Team Wins Losses Ties
Tigers 5 0 0
Titans 4 1 0
Panthers 4 0 1
Stars 2 2 1
Rockets 2 3 0
Blue Jays 1 4 0

Below is the query I came up with. It works (correct me if I'm wrong) but I'm wondering if there is a more elegant and scalable way to do this? Thanks!

/*
Computed by grouping aggregates of different data sets
Figure out number of team wins by selecting number of games where team score is the max. Remove ties by making sure the low score isn't the same
Ties and Losses figured using similar logic
*/

SELECT
/* wins/ties/losses are separate rows so add them all up and put them on one row per team */
robtm.name , sum(derived.wins) AS wins , sum(derived.losses) AS losses , sum(derived.ties) AS ties
FROM
(

/* wins */
SELECT
team_id , COUNT(*) as wins , 0 AS losses , 0 AS ties
from
robtm_gm ,
(
select
game_id , max(score) as winning_score , min(score) as losing_score
from
robtm_gm
group by
game_id
having
max(score) > min(score)
) AS derived
where
robtm_gm.game_id = derived.game_id AND
robtm_gm.score = derived.winning_score
group by
team_id

UNION

/* losses */
SELECT
team_id , 0 AS wins , COUNT(team_id) as losses , 0 AS ties
from
robtm_gm ,
(
select
game_id , max(score) as winning_score , min(score) as losing_score
from
robtm_gm
group by
game_id
having
max(score) > min(score)
) AS derived

where
robtm_gm.game_id = derived.game_id AND
robtm_gm.score = derived.losing_score
group by
team_id

UNION

/* ties */
SELECT
team_id , 0 AS wins , 0 AS losses , COUNT(*) as ties
from
robtm_gm ,
(
select
game_id , max(score) as winning_score , min(score) as losing_score
from
robtm_gm
group by
game_id
having
max(score) = min(score)
) AS derived

where
robtm_gm.game_id = derived.game_id AND
robtm_gm.score = derived.winning_score
group by
team_id

) AS derived

/* get team name */
INNER JOIN
robtm ON robtm.id = derived.team_id

/* group by team name so we can add up wins/losses/ties */
GROUP BY
robtm.name
ORDER BY
wins desc, losses asc, ties asc

Thanks!
Reuben

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 13:40:17

this can be used with CASE when statement within a same query

if i get more time to work on this i will post a reply, but in the mean time if you can create scripts to enter data into tables
that will make it more easier for anybody to help you.

like create table table_name (columns)

and then have the insert scripts.

insert into table_name values (blah, blah, blah.....)

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 13:59:42

The output expected and data provided doesnt seem to match. You have team names in output list that are no where in the provided data.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-13 : 14:03:31

I'll give you a clue: it can be done a lot easier, without any UNIONS.

GROUP BY and CASE are all you need.

What makes it difficult is this is a pretty bad schema for this type of data. Nothing constrains the data to have exactly two teams per game. Plus you can even have the same team listed multiple times in the same game with different scores. It makes no sense ... it's not really a good interview question unless the answer they are after is criticism of the schema, and I doubt that's what they want!

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:28:19

Jeff,

I beg to differ. They are not after the criticsizing the schema

yes in real life the database can be different and can include computed columns to calculate wins and losses to make the queries more simpler. Or it can even have column which will have 1 for win and 0 for loss.

They might be looking how effective the person is when it comes to the use of CASE statement.

If Reuban can post the scripts for tables and inserts then it will be easier to help.

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-13 : 14:58:13

Ashley -- I know they don't want a criticism of the schema, that's what I said.

The fact, however, remains: the schema is flawed. badly. It makes no sense. Not because it is "hard" to query or doesn't have handy computed columns to help, but because it has no data integrity at all ... It simply does not model a situation where two different teams compete in a game. You can have 10 teams all playing in the same game, or you can have a game that lists the same team 1,000 different times, playing itself all in the same game, or just one team playing in a given game, and so on.

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 15:10:24

they just picked out the sample question from somewhere to interview.

sometimes the managers who interview have no idea of technical stuff.

so they might not be able to tell if its a bad schema or not.

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-13 : 15:19:23

>>so they might not be able to tell if its a bad schema or not.

So how can they tell if it is a good solution or not?

- Jeff
http://weblogs.sqlteam.com/JeffS

ImagineTNT
Starting Member

5 Posts

Posted - 2007-08-13 : 17:01:19

OK. here are the very basic scripts. The data tables I originaly posted were not what was in my database so those wouldn't match the output. These scripts and the data were created random from scratch.

create table robdt
(
id int IDENTITY(1,1) NOT NULL,
gamedate datetime
)

create table robtm
(
id int IDENTITY(1,1) NOT NULL,
name varchar(100)
)

create table robtm_gm
(
id int IDENTITY(1,1) NOT NULL,
game_id int ,
team_id int ,
score int
)

insert into robtm
(name)
values
('Tigers')

insert into robtm
(name)
values
('Titans')

insert into robtm
(name)
values
('Panthers')

insert into robtm
(name)
values
('Stars')

insert into robtm
(name)
values
('Rockets')

insert into robtm
(name)
values
('Blue Jays')

insert into robdt
(gamedate)
values
('1/1/2002')

insert into robdt
(gamedate)
values
('2/1/2002')

insert into robdt
(gamedate)
values
('3/1/2002')

insert into robdt
(gamedate)
values
('4/1/2002')

insert into robdt
(gamedate)
values
('5/1/2002')

insert into robdt
(gamedate)
values
('6/1/2002')

insert into robdt
(gamedate)
values
('7/1/2002')

insert into robdt
(gamedate)
values
('8/1/2002')

insert into robdt
(gamedate)
values
('9/1/2002')

insert into robdt
(gamedate)
values
('10/1/2002')

insert into robtm_gm
(game_id , team_id , score)
values
(1,1,2)
insert into robtm_gm
(game_id , team_id , score)
values
(1,2,1)

insert into robtm_gm
(game_id , team_id , score)
values
(2,3,2)
insert into robtm_gm
(game_id , team_id , score)
values
(2,4,1)

insert into robtm_gm
(game_id , team_id , score)
values
(3,1,2)
insert into robtm_gm
(game_id , team_id , score)
values
(3,3,1)

insert into robtm_gm
(game_id , team_id , score)
values
(4,1,2)
insert into robtm_gm
(game_id , team_id , score)
values
(4,4,1)

insert into robtm_gm
(game_id , team_id , score)
values
(5,2,2)
insert into robtm_gm
(game_id , team_id , score)
values
(5,3,2)

insert into robtm_gm
(game_id , team_id , score)
values
(6,3,2)
insert into robtm_gm
(game_id , team_id , score)
values
(6,4,2)

insert into robtm_gm
(game_id , team_id , score)
values
(7,5,5)
insert into robtm_gm
(game_id , team_id , score)
values
(7,6,2)

insert into robtm_gm
(game_id , team_id , score)
values
(8,1,2)
insert into robtm_gm
(game_id , team_id , score)
values
(8,4,3)

insert into robtm_gm
(game_id , team_id , score)
values
(9,4,2)
insert into robtm_gm
(game_id , team_id , score)
values
(9,6,4)

insert into robtm_gm
(game_id , team_id , score)
values
(10,2,2)
insert into robtm_gm
(game_id , team_id , score)
values
(10,6,5)

ImagineTNT
Starting Member

5 Posts

Posted - 2007-08-13 : 17:07:00

Ohh, and part of the interview response is to discuss how to design tables to track this stuff so of course I will bring up a more normalized way of designing the tables. Another topic is issues with a very large database and very high traffic.

ImagineTNT
Starting Member

5 Posts

Posted - 2007-08-14 : 18:18:17

Anyone have any ideas? I really don't know how you would rewrite this query using CASE instead of the UNIONs because I can't figure out how to pull out wins/ties/losses information together in one query.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-14 : 22:12:39

SELECT Team = A.name,
Wins = (SELECT COUNT(*) FROM robtm_gm Z WHERE Z.team_id = A.id AND Z.score
> (SELECT Y.score FROM robtm_gm Y WHERE Y.game_id = Z.game_id AND Z.team_id != Y.team_id)),
Losses = (SELECT COUNT(*) FROM robtm_gm Z WHERE Z.team_id = A.id AND Z.score
< (SELECT Y.score FROM robtm_gm Y WHERE Y.game_id = Z.game_id AND Z.team_id != Y.team_id)),
[Ties] = (SELECT COUNT(*) FROM robtm_gm Z WHERE Z.team_id = A.id AND Z.score
= (SELECT Y.score FROM robtm_gm Y WHERE Y.game_id = Z.game_id AND Z.team_id != Y.team_id))
FROM robtm A
ORDER BY A.id

ImagineTNT
Starting Member

5 Posts

Posted - 2007-08-14 : 22:54:19

Awesome, thanks! It took me a few minutes to unpack that and figure out what's going on. I started off all my thinking revolving around the robtm_gm table and didn't even think about basing things off the list of teams.

Thank you everyone for your help!!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-19 : 23:41:21

You really wanna do 4 correlated subqueries???

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 05:01:54

[code]-- Prepare sample data
DECLARE @Games TABLE (ID INT, GameID INT, TeamID INT, Score TINYINT)

INSERT @Games
SELECT 1, 1, 1, 4 UNION ALL
SELECT 2, 1, 3, 4 UNION ALL
SELECT 3, 2, 2, 1 UNION ALL
SELECT 4, 2, 3, 6 UNION ALL
SELECT 5, 3, 4, 21 UNION ALL
SELECT 6, 3, 1, 8 UNION ALL
SELECT 7, 4, 2, 2 UNION ALL
SELECT 8, 4, 1, 10

DECLARE @Teams TABLE (ID INT, Name SYSNAME)

INSERT @Teams
SELECT 1, 'REDS' UNION ALL
SELECT 2, 'ORIOLES' UNION ALL
SELECT 3, 'RED SOX' UNION ALL
SELECT 4, 'PADRES'

-- Show the expected result
SELECT t.Name,
SUM(CASE WHEN g1.Score = g2.Score THEN 1 ELSE 0 END) AS Ties,
SUM(CASE WHEN g1.Score < g2.Score THEN 1 ELSE 0 END) AS Losses,
SUM(CASE WHEN g1.Score > g2.Score THEN 1 ELSE 0 END) AS Wins
FROM @Games AS g1
INNER JOIN @Games AS g2 ON g2.GameID = g1.GameID
AND g2.TeamID <> g1.TeamID
INNER JOIN @Teams AS t ON t.ID = g1.TeamID
GROUP BY g1.TeamID,
t.Name
ORDER BY t.Name[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 05:51:44

And, if you want to, you can add this piece of code

INNER JOIN	(
			SELECT		GameID
			FROM		@Games
			WHERE		Score >= 0
			GROUP BY	GameID
			HAVING		MIN(TeamID) < MAX(TeamID)
					AND COUNT(*) = 2
		) AS g3 ON g3.GameID = g1.GameID

to deal with all flaws in the design.
The code above only selects the VALID games, that is

1) Only two teams per game
2) The two TeamID must be different
3) The score must be 0 or greater than 0

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources