Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 77 views

    SQL Queries NFL

    Uploaded by

    Tiago Santos
    The document discusses using SQL queries to summarize fantasy football matchup results for a specific week from a league's data. Query 1 finds the home and away teams for each matchup in week 5. Query 2 calculates the total points scored by each team that week. Query 3 combines the results of the first two queries to display the name of each team in a matchup along with their score for that week.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    SQL Queries NFL

    Uploaded by

    Tiago Santos
    77 views2 pages
    The document discusses using SQL queries to summarize fantasy football matchup results for a specific week from a league's data. Query 1 finds the home and away teams for each matchup in week 5. Query 2 calculates the total points scored by each team that week. Query 3 combines the results of the first two queries to display the name of each team in a matchup along with their score for that week.

    Original Title

    SQL Queries Nfl

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses using SQL queries to summarize fantasy football matchup results for a specific week from a league's data. Query 1 finds the home and away teams for each matchup in week 5. Query 2 calculates the total points scored by each team that week. Query 3 combines the results of the first two queries to display the name of each team in a matchup along with their score for that week.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    77 views2 pages

    SQL Queries NFL

    Uploaded by

    Tiago Santos
    The document discusses using SQL queries to summarize fantasy football matchup results for a specific week from a league's data. Query 1 finds the home and away teams for each matchup in week 5. Query 2 calculates the total points scored by each team that week. Query 3 combines the results of the first two queries to display the name of each team in a matchup along with their score for that week.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 2

    ## Queries: Week Matchup Result

    As first query, let's find which team won and what score they have in a specific
    week (5 for example) in the fantasy league. To do so, we'll have to join the
    **roster** of each **team** in that week with the individual **player'a points**,
    sum then up to find the team's score and compare with the opponent team score.

    For this exercise we'll use, data extract from my **league**, named "It's football,
    dudes", which I extract the data from Fantasy website.

    ```{sql connection=con}
    -- what are the league_id?
    select *
    from league
    ```

    There is only one league in the data set, we'll use it's code: 3940933. Let's make
    this in two steps, first let's find which teams plays against each other in the
    week 5.

    ### Query 1 - Games Schedule

    ```{sql connection=con}
    -- from the matchup
    select
    m.league_id,
    m.week,
    m.home_id,
    hmt.team_name as home_team_name,
    m.away_id,
    awt.team_name as away_team_name
    from matchup m
    -- join the home team with team table
    inner join team hmt on hmt.team_id = m.home_id
    -- joint the visitor team with team table
    inner join team awt on awt.team_id = m.away_id
    -- week 5 on the league of interest
    where m.week=5 and m.league_id=3940933;
    ```

    Now we know what are the matchups for week 5, let's calculate each team's score in
    that week, to do this we join the roster of each team with the individual players
    points and sum up in a team score.

    ### Query 2 - Roster Score

    ```{sql connection=con}
    -- week team total score
    select league_id, week, team_id, sum(points)
    from (
    -- join roster with points to get individual player's points in the rosters
    select r.league_id, r.week, r.team_id, r.player_id, r.rosterSlot, p.points
    from roster r inner join points p
    on r.league_id=p.league_id and r.week=p.week and r.player_id=p.player_id
    ) roster_points
    -- exclude players in the bench, their pontuation don't count as team points
    where rosterSlot != "BN" and week=5 and league_id=3940933
    group by league_id, week, team_id;
    ```
    Now, let's up all toghether.

    ### Query 3 - Game Score

    ```{sql connection=con}
    -- round results query
    select
    hmt.team_name as home_team_name,
    htpts.total_points as home_team_points,
    awt.team_name as away_team_name,
    awpts.total_points as away_team_points
    from matchup m
    -- to get tha names of home and visitor teams
    inner join team hmt on hmt.team_id = m.home_id
    inner join team awt on awt.team_id = m.away_id
    -- score for home team
    inner join (
    select league_id, week, team_id, round(sum(points),1) as total_points
    from (
    -- join roster with points to get individual player's points in the
    rosters
    select r.league_id, r.week, r.team_id, r.player_id, r.rosterSlot,
    p.points
    from roster r inner join points p
    on r.league_id=p.league_id and r.week=p.week and
    r.player_id=p.player_id
    ) roster_points
    -- exclude players in the bench, their pontuation don't count as team points
    where rosterSlot != "BN"
    group by league_id, week, team_id
    ) htpts on m.league_id=htpts.league_id and m.week=htpts.week and
    hmt.team_id=htpts.team_id
    -- score for visitor team
    inner join (
    select league_id, week, team_id, round(sum(points),1) total_points
    from (
    -- join roster with points to get individual player's points in the
    rosters
    select r.league_id, r.week, r.team_id, r.player_id, r.rosterSlot,
    p.points
    from roster r inner join points p
    on r.league_id=p.league_id and r.week=p.week and
    r.player_id=p.player_id
    ) roster_points
    -- exclude players in the bench, their pontuation don't count as team points
    where rosterSlot != "BN"
    group by league_id, week, team_id
    ) awpts on m.league_id=awpts.league_id and m.week=awpts.week and
    awt.team_id=awpts.team_id
    -- just from week and league of interest
    where m.week=5 and m.league_id=3940933
    order by home_team_points desc
    ```

    Finally we get the round *5* of league *3940933*, with the names of the teams and
    the score marked for each team.

    You might also like