Ran into problem with understanding the query, which I needed to write for the project.
Checked the solutions, compared it to my code and don’t understand, why the one in the solutions is the correct one. It is the first query for the project:
Codecademy solution:
SELECT
AVG(people.weight), teams.name, batting.yearid
FROM people
INNER JOIN batting
ON people.playerid = batting.playerid
INNER JOIN teams
ON batting.team_id = teams.id
GROUP BY teams.name, batting.yearid
ORDER BY AVG(people.weight) DESC;
My solution:
WITH tmp AS (
SELECT batting.yearid AS year,
batting.playerid AS p_id,
teams.name AS t_name,
people.weight AS weight
FROM batting
JOIN people
ON batting.playerid = people.playerid
JOIN teams
ON batting.team_id = teams.id
WHERE weight IS NOT NULL
)
SELECT year, t_name, AVG(weight)
FROM tmp
GROUP BY 1, 2
HAVING year = 2013
ORDER BY 3 DESC;
Can somebody please explain why is my solution wrong?
I believe than my solution is correct - if you launch the Codecademy solution, it gives repeating years in the ‘yearid’ column.
Would very much appreciate your help!!!