Implement query I #18

Closed
nddrylliog opened this Issue May 10, 2012 · 17 comments

Comments

Projects
None yet
2 participants
Collaborator

nddrylliog commented May 10, 2012

List the average weight, average height and average age, of teams of coaches with more than XXX season career wins and more than YYY win percentage, in each season they coached. (XXX and YYY are parameters. Try with combinations: {XXX,YYY}={<1000,70%>,<1000,60%>,<1000,50%>,<700,55%>,<700,45%>}. Sort the result by year in ascending order.

nddrylliog was assigned May 10, 2012

Collaborator

nddrylliog commented May 10, 2012

I've started it (see 38beef4 ), but I'm going to bed for now. After all, the deadline is still far along, and I'm making good progress on these queries.

@nddrylliog nddrylliog pushed a commit that referenced this issue May 21, 2012

Amos Wenger Making progress on query i #18 3eb1811
Collaborator

nddrylliog commented May 22, 2012

Finished in 70b1e86, closing.

nddrylliog closed this May 22, 2012

greut reopened this May 22, 2012

Collaborator

greut commented May 22, 2012

It seems it's computing the data (avg and age) from the coaches instead of the team members. Or did I misunderstood the query we have to solve?

@greut greut added a commit that referenced this issue May 22, 2012

@greut greut Using denormalized coaches table. #18 98898ad
Collaborator

nddrylliog commented May 22, 2012

"of teams of coaches" -> I thought this meant precisely that it was the average of weight/height etc. of the players in the teams managed by (coaches who had more then XXX/YYY career/wins percentage). Don't you think? We could argue either way imho...

Collaborator

greut commented May 22, 2012

Apparently we agree on that, the SQL query doesn't seem to do that. Since I changed it to use the coaches table, it might be empty on your side until we find what's going on.

Collaborator

nddrylliog commented May 22, 2012

Yeah but still using only the coaches tables how can you find the average weight/height/age of the players of these teams?

Collaborator

greut commented May 22, 2012

I'd use best_coaches.year + best_coaches.team_id (to add) and join that with player_seasons. The coaches table is only useful for the XXX param which doesn't have to be recomputed.

Collaborator

nddrylliog commented May 22, 2012

By "you would" you mean "in the final version of the query which is not yet done", correct?

Collaborator

greut commented May 22, 2012

Indeed. When the average age is above 60, something is wrong :-)

Collaborator

nddrylliog commented May 22, 2012

My average age seemed correct btw, (using to_date(year, 'YYYY') instead of
SYSDATE)
On May 22, 2012 10:07 PM, "Yoan Blanc" <
reply@reply.github.com>
wrote:

Indeed. When the average age is above 60, something is wrong :-)


Reply to this email directly or view it on GitHub:
nddrylliog#18 (comment)

@greut greut added a commit that referenced this issue May 29, 2012

@greut greut Quick fix on the the I. #18
I'm taking the definition "season" to be a bit broader that just for one
team one time, but for the whole actual season (year).
92f956c
Collaborator

nddrylliog commented Jun 1, 2012

I wanted to work on that some more, but it returns 0 rows, I guess because 'coaches' is empty because of triggers. I'll try debugging them now on my side.

Collaborator

nddrylliog commented Jun 1, 2012

Hypothesis: my user doesn't have the permission to create triggers :D

Collaborator

nddrylliog commented Jun 1, 2012

Aaand that was it. coaches table is correctly populated now. Finishing up this query

Collaborator

nddrylliog commented Jun 1, 2012

Welp, I think something's wrong:

nddrylliog closed this in 61192ab Jun 1, 2012

Collaborator

nddrylliog commented Jun 1, 2012

It's giving very good results now :)

Collaborator

greut commented Jun 1, 2012

Sweet!

Collaborator

greut commented Jun 3, 2012

The reason why I redid the HTML view:

In query (c) we expect from you to list the asked statistics (avg height, avg weight, avg age) for every season of a coach who in total in his career he had at least XXX wins and YYY win percentage. For example, for a "good coach" who coached for 10 seasons you will have 10 rows in your resultset.
http://moodle.epfl.ch/mod/forum/discuss.php?d=167271#p375511

@greut greut added a commit that referenced this issue Jun 3, 2012

@greut greut No SQL in the controller. #18 7d56c63
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment