In [3] : import sqlite3 as dbimport pandas as pdfrom datetime import datetimefrom collections import defaultdictdisk_engine = db. connect ( ‘ file: prob0 . db?mode=ro’, uri=True)def load_data( ) :df = pd. read_sql_query ( “SELECT * FROM soccer_results”, disk_engine)return dfIn [4]: # Test: Exercise 0 (exposed)df = load_data ( )assert df . shape [ 0] == 22851, “Row counts do not match. Try loading the data again”assert df . shape[1] == 9, “You don’t have all the columns. Try loading the data again”print ( “n (Passed! ) “)df . head ( )(Passed! )Out [ 4 ] :date home_team away_team home_score away_score tournamentcitycountry neutral0 1994-01-02BarbadosGrenada0Friendly Bridgetown Barbados FALSE1994-01-02GhanaEgypt- NFriendlyAccraa FALSE2 1994-01-05Mali Burkina FasoFriendlyBamakoMali FALSE3 1994-01-09MauritaniaMali3Friendly Nouakchott Mauritania FALSE1994-01-11ThailandNigeriaFriendlyBangkokThailand FALSEEach row of this dataframe is a game, which is played between a “home team” (column home_team) and an “away team” (away_team). The number of goalsscored by each team appears in the home_score and away_score columns, respectively.Exercise 1 (1 point): Write an SQL query find the ten (10) teams that have the highest average away-scores since the year 2000. Your query should satisfy thefollowing criteria. It should return two columns:. team: The name of the teamave_goals: The team’s average number of goals in “away” games. An “away game” is one in which the team’s name appars in away_team and thegame takes place at a “non-neutral site” (neutral value equals FALSE).It should only include teams that have played at least 30 away matches. It should round the average goals value (ave_goals) to three decimal places.It should only return the top 10 teams in descending order by average away-goals.. It should only consider games played since 2000 (including the year 2000).Store your query string as the variable, query_top10_away, below. The test cell will run this query string against the input dataframe, df, defined above andreturn the result in a dataframe named offensive_teams. (See the test cell.)Note. The following exercises have hidden test cases and you’ll be awarded full points for passing both the exposed and hidden test cases.In [5]: query_top10_away =SELECT away_team, ROUND ( avg (away_score) , 3)FROM dataWHERE neutral is false and strftime( ‘ $H’, date) ; >= 2000GROUP BY away_team HAVING count ( *) >= 30ORDER BY ROUND ( avg ( away_score) , 3)DESC LIMIT 10;
ScholarMatic: Explanation & Answer
Your ready answer from a verified tutor is just a click away for as little as $14.99
Click Order Now to get 100% Original Answer Customized to your instructions!
