Re: help with a query

Yarden:

On Mon, 15 Nov 2021 at 10:30, yarden tamam <tamamyarden(at)gmail(dot)com> wrote:
> i have data bast that represents basketball teams. and i'm trying to find one team that has not won yet but has gain that most points during their games from the other team who have not won yet.
> i wrote a query that is checking if a team has not won at all. but i'm trying to figure out how to count the points because every team play either at home or away.
> thank you for you help.
> here is the query the i have wrote so far:
> select team.id, nickname,color
> from team,points,game
> where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = team.tid and (hscore - vscore )< 0 )
> having

This has the faint smell of homework assignment, but anyway, decompose
the problem a bit and build from it, if I understand your logic you
can do more or less...

with home_games_total as ( select htid as tid, sum(hscore) as
tscore, sum (vscore as oscore), max(hscore>vscore) as won_any )

This captures total result for home games for the team, for the other
team, and wether it has won any match ( using the fact that true>false
in max, there are better ways but this is easy to type for an example
). Then you can add....

with visitor_games_total as ( select vtid as tid, sum(vscore) as
tscore, sum (hscore as oscore), max(vscore>hscore) as won_any )

to capture visitor teams stats, then you just have to combine them with

with all_games_totals as (select * from home games total union all
select * from visitor_games_total )

note each team will have play twice, one for the home team, one for
the visitor in the first two sum, and the last subquery will have two
rows per team, one for home games, one for visitor. Now just add them
all to get one row per team

with team_result as (select tid, sum(tscore), sum(oscore),
max(won_any) from all_games_total group by tid)

and there you just have to search for wat you want:

select * from team_result where not won_any order by tscore desc
limit 1 -- ( or use tscore-oscore, if you want point diffs )

From this you can probably optimize, but if your tables are not too
big doing it that way is easy for maintenance. And even if you have
all playing history for NBA if you limit first two queries to a season
they will not spit too many results. Also the first two queries plus
the third can be done in a single scan by capturing stats for home and
visitor in a single round, then sum it with itself with columns
swapped.

Also note, this is totally untested code and probably chock full of
typos and errors.

Francisco Olarte.