
# Installing ipython-sql to use SQL with Python
!conda install -yc conda-forge ipython-sql

Collecting package metadata (current_repodata.json): ...working... done
Solving environment: ...working... done

## Package Plan ##

  environment location: C:\Users\manit\anaconda3\envs\sql_analysis

  added / updated specs:
    - ipython-sql


The following packages will be downloaded:

    package                    |            build
    ---------------------------|-----------------
    ca-certificates-2020.12.5  |       h5b45459_0         173 KB  conda-forge
    certifi-2020.12.5          |   py39hcbf5309_0         144 KB  conda-forge
    ipython-sql-0.3.9          |py39hde42818_1002          28 KB  conda-forge
    openssl-1.1.1i             |       h8ffe710_0         5.8 MB  conda-forge
    prettytable-2.0.0          |     pyhd8ed1ab_0          22 KB  conda-forge
    python_abi-3.9             |           1_cp39           4 KB  conda-forge
    sqlalchemy-1.3.20          |   py39h4cdbadb_0         1.8 MB  conda-forge
    sqlparse-0.4.1             |     pyh9f0ad1d_0          34 KB  conda-forge
    ------------------------------------------------------------
                                           Total:         8.0 MB

The following NEW packages will be INSTALLED:

  ipython-sql        conda-forge/win-64::ipython-sql-0.3.9-py39hde42818_1002
  prettytable        conda-forge/noarch::prettytable-2.0.0-pyhd8ed1ab_0
  python_abi         conda-forge/win-64::python_abi-3.9-1_cp39
  sqlalchemy         conda-forge/win-64::sqlalchemy-1.3.20-py39h4cdbadb_0
  sqlparse           conda-forge/noarch::sqlparse-0.4.1-pyh9f0ad1d_0

The following packages will be SUPERSEDED by a higher-priority channel:

  ca-certificates    pkgs/main::ca-certificates-2020.12.8-~ --> conda-forge::ca-certificates-2020.12.5-h5b45459_0
  certifi            pkgs/main::certifi-2020.12.5-py39haa9~ --> conda-forge::certifi-2020.12.5-py39hcbf5309_0
  openssl              pkgs/main::openssl-1.1.1i-h2bbff1b_0 --> conda-forge::openssl-1.1.1i-h8ffe710_0


Downloading and Extracting Packages

certifi-2020.12.5    | 144 KB    |            |   0% 
certifi-2020.12.5    | 144 KB    | #1         |  11% 
certifi-2020.12.5    | 144 KB    | ########## | 100% 

ca-certificates-2020 | 173 KB    |            |   0% 
ca-certificates-2020 | 173 KB    | ##7        |  28% 
ca-certificates-2020 | 173 KB    | ########## | 100% 

ipython-sql-0.3.9    | 28 KB     |            |   0% 
ipython-sql-0.3.9    | 28 KB     | #####7     |  58% 
ipython-sql-0.3.9    | 28 KB     | ########## | 100% 

openssl-1.1.1i       | 5.8 MB    |            |   0% 
openssl-1.1.1i       | 5.8 MB    |            |   0% 
openssl-1.1.1i       | 5.8 MB    | 1          |   2% 
openssl-1.1.1i       | 5.8 MB    | 4          |   4% 
openssl-1.1.1i       | 5.8 MB    | 7          |   8% 
openssl-1.1.1i       | 5.8 MB    | #3         |  14% 
openssl-1.1.1i       | 5.8 MB    | #5         |  16% 
openssl-1.1.1i       | 5.8 MB    | #8         |  19% 
openssl-1.1.1i       | 5.8 MB    | ##1        |  21% 
openssl-1.1.1i       | 5.8 MB    | ##3        |  24% 
openssl-1.1.1i       | 5.8 MB    | ##5        |  26% 
openssl-1.1.1i       | 5.8 MB    | ##9        |  30% 
openssl-1.1.1i       | 5.8 MB    | ###5       |  35% 
openssl-1.1.1i       | 5.8 MB    | ####       |  41% 
openssl-1.1.1i       | 5.8 MB    | ####4      |  45% 
openssl-1.1.1i       | 5.8 MB    | ####9      |  50% 
openssl-1.1.1i       | 5.8 MB    | #####3     |  53% 
openssl-1.1.1i       | 5.8 MB    | #####6     |  57% 
openssl-1.1.1i       | 5.8 MB    | ######1    |  62% 
openssl-1.1.1i       | 5.8 MB    | ######6    |  66% 
openssl-1.1.1i       | 5.8 MB    | ######9    |  70% 
openssl-1.1.1i       | 5.8 MB    | #######5   |  75% 
openssl-1.1.1i       | 5.8 MB    | #######9   |  80% 
openssl-1.1.1i       | 5.8 MB    | ########2  |  83% 
openssl-1.1.1i       | 5.8 MB    | ########6  |  86% 
openssl-1.1.1i       | 5.8 MB    | ########8  |  89% 
openssl-1.1.1i       | 5.8 MB    | #########3 |  94% 
openssl-1.1.1i       | 5.8 MB    | ########## | 100% 
openssl-1.1.1i       | 5.8 MB    | ########## | 100% 

prettytable-2.0.0    | 22 KB     |            |   0% 
prettytable-2.0.0    | 22 KB     | #######1   |  72% 
prettytable-2.0.0    | 22 KB     | ########## | 100% 

sqlparse-0.4.1       | 34 KB     |            |   0% 
sqlparse-0.4.1       | 34 KB     | ####7      |  48% 
sqlparse-0.4.1       | 34 KB     | ########## | 100% 

python_abi-3.9       | 4 KB      |            |   0% 
python_abi-3.9       | 4 KB      | ########## | 100% 
python_abi-3.9       | 4 KB      | ########## | 100% 

sqlalchemy-1.3.20    | 1.8 MB    |            |   0% 
sqlalchemy-1.3.20    | 1.8 MB    |            |   1% 
sqlalchemy-1.3.20    | 1.8 MB    | 6          |   7% 
sqlalchemy-1.3.20    | 1.8 MB    | #4         |  14% 
sqlalchemy-1.3.20    | 1.8 MB    | #8         |  19% 
sqlalchemy-1.3.20    | 1.8 MB    | ##3        |  24% 
sqlalchemy-1.3.20    | 1.8 MB    | ##7        |  28% 
sqlalchemy-1.3.20    | 1.8 MB    | ###2       |  32% 
sqlalchemy-1.3.20    | 1.8 MB    | ###9       |  40% 
sqlalchemy-1.3.20    | 1.8 MB    | ####6      |  47% 
sqlalchemy-1.3.20    | 1.8 MB    | #####1     |  52% 
sqlalchemy-1.3.20    | 1.8 MB    | #####8     |  59% 
sqlalchemy-1.3.20    | 1.8 MB    | ######6    |  66% 
sqlalchemy-1.3.20    | 1.8 MB    | #######1   |  71% 
sqlalchemy-1.3.20    | 1.8 MB    | ########   |  81% 
sqlalchemy-1.3.20    | 1.8 MB    | ########8  |  88% 
sqlalchemy-1.3.20    | 1.8 MB    | #########3 |  93% 
sqlalchemy-1.3.20    | 1.8 MB    | #########7 |  98% 
sqlalchemy-1.3.20    | 1.8 MB    | ########## | 100% 
Preparing transaction: ...working... done
Verifying transaction: ...working... done
Executing transaction: ...working... done


%%capture
%load_ext sql
%sql sqlite:///database.sqlite


%%sql
SELECT *
FROM sqlite_master
WHERE type='table';

 * sqlite:///database.sqlite
Done.


%%sql
SELECT name, type
FROM sqlite_master
WHERE type IN ("table", "view")

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM league;

 * sqlite:///database.sqlite
Done.


%%sql
DROP VIEW IF EXISTS best_leagues;

CREATE VIEW best_leagues AS
SELECT id, country_id,
CASE 
    WHEN name = 'England Premier League' THEN 'Premier League'
    WHEN name = 'France Ligue 1' THEN 'League 1'
    WHEN name = 'Germany 1. Bundesliga' THEN 'Bundesliga'
    WHEN name = 'Italy Serie A' THEN 'Serie A'
    WHEN name = 'Spain LIGA BBVA' THEN 'La Liga'
END AS name
FROM league
WHERE name IN ('England Premier League', 'France Ligue 1', 'Germany 1. Bundesliga', 'Italy Serie A','Spain LIGA BBVA');

SELECT * FROM best_leagues;

 * sqlite:///database.sqlite
Done.
Done.
Done.


%%sql
SELECT t.* FROM team t LIMIT 1;

 * sqlite:///database.sqlite
Done.


%%sql
DROP VIEW IF EXISTS best_teams;

CREATE VIEW best_teams AS
SELECT t.* FROM team t
    INNER JOIN Match m ON m.home_team_api_id = t.team_api_id
    INNER JOIN best_leagues l ON l.id = m.league_id
    GROUP BY t.team_long_name;
    
SELECT COUNT(*) FROM best_teams

 * sqlite:///database.sqlite
Done.
Done.
Done.


%%sql

SELECT * FROM player LIMIT 0;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM match 
WHERE season = '2011/2012' LIMIT 5;

 * sqlite:///database.sqlite
Done.


%%sql
DROP VIEW IF EXISTS best_players;

CREATE VIEW best_players AS
WITH pl AS
(
SELECT home_player_1, home_player_2, home_player_3, home_player_4, home_player_5, home_player_6,
        home_player_7, home_player_8, home_player_9, home_player_10, home_player_11, away_player_1,
        away_player_2, away_player_3, away_player_4, away_player_5, away_player_6, away_player_7,
        away_player_8, away_player_9, away_player_10, away_player_11
    FROM Match 
    INNER JOIN best_leagues ON best_leagues.id = Match.league_id 
)

SELECT id, player_api_id, player_name, player_fifa_api_id, date(birthday),  substr(birthday, 1,4) AS birth_year,
        substr(birthday, 6,2) AS birth_month, substr(birthday, 9,2) AS birth_day, height, weight

        FROM player WHERE player_api_id IN (SELECT home_player_1 FROM pl)
                        OR player_api_id IN (SELECT home_player_2 FROM pl)
                        OR player_api_id IN (SELECT home_player_3 FROM pl)
                        OR player_api_id IN (SELECT home_player_4 FROM pl)
                        OR player_api_id IN (SELECT home_player_5 FROM pl)
                        OR player_api_id IN (SELECT home_player_6 FROM pl)
                        OR player_api_id IN (SELECT home_player_7 FROM pl)
                        OR player_api_id IN (SELECT home_player_8 FROM pl)
                        OR player_api_id IN (SELECT home_player_9 FROM pl)
                        OR player_api_id IN (SELECT home_player_10 FROM pl)
                        OR player_api_id IN (SELECT home_player_11 FROM pl)
                        
                        OR player_api_id IN (SELECT away_player_1 FROM pl)
                        OR player_api_id IN (SELECT away_player_2 FROM pl)
                        OR player_api_id IN (SELECT away_player_3 FROM pl)
                        OR player_api_id IN (SELECT away_player_4 FROM pl)
                        OR player_api_id IN (SELECT away_player_5 FROM pl)
                        OR player_api_id IN (SELECT away_player_6 FROM pl)
                        OR player_api_id IN (SELECT away_player_7 FROM pl)
                        OR player_api_id IN (SELECT away_player_8 FROM pl)
                        OR player_api_id IN (SELECT away_player_9 FROM pl)
                        OR player_api_id IN (SELECT away_player_10 FROM pl)
                        OR player_api_id IN (SELECT away_player_11 FROM pl)
                        ;
                        
SELECT COUNT(*) FROM best_players;

 * sqlite:///database.sqlite
Done.
Done.
Done.


%%sql
SELECT * FROM best_players LIMIT 5;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM best_teams LIMIT 5;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM best_leagues;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM Team_attributes LIMIT 10;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT buildUpPlaySpeedClass, MIN(buildUpPlaySpeed) AS Min_Points, MAX(buildUpPlaySpeed) AS Max_Points
    FROM team_attributes
    GROUP BY buildUpPlaySpeedClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT buildUpPlayDribblingClass, MIN(buildUpPlayDribbling) AS Min_Points, MAX(buildUpPlayDribbling) AS Max_Points
    FROM team_attributes
    GROUP BY buildUpPlayDribblingClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT buildUpPlayPassingClass, MIN(buildUpPlayPassing) AS Min_Points, MAX(buildUpPlayPassing) AS Max_Points
    FROM team_attributes
    GROUP BY buildUpPlayPassingClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT chanceCreationPassingClass, MIN(chanceCreationPassing) AS Min_Points, MAX(chanceCreationPassing) AS Max_Points
    FROM team_attributes
    GROUP BY chanceCreationPassingClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT chanceCreationCrossingClass, MIN(chanceCreationCrossing) AS Min_Points, MAX(chanceCreationCrossing) AS Max_Points
    FROM team_attributes
    GROUP BY chanceCreationCrossingClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT chanceCreationShootingClass, MIN(chanceCreationShooting) AS Min_Points, MAX(chanceCreationShooting) AS Max_Points
    FROM team_attributes
    GROUP BY chanceCreationShootingClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT defencePressureClass, MIN(defencePressure) AS Min_Points, MAX(defencePressure) AS Max_Points
    FROM team_attributes
    GROUP BY defencePressureClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT defenceAggressionClass, MIN(defenceAggression) AS Min_Points, MAX(defenceAggression) AS Max_Points
    FROM team_attributes
    GROUP BY defenceAggressionClass;

 * sqlite:///database.sqlite
Done.


%%sql

SELECT defenceTeamWidthClass, MIN(defenceTeamWidth) AS Min_Points, MAX(defenceTeamWidth) AS Max_Points
    FROM team_attributes
    GROUP BY defenceTeamWidthClass;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT l.name AS League, m.Season AS Season,
    
    ROUND(AVG(a.buildUpPlaySpeed),2) AS Avg_PlaySpeed, MAX(a.buildUpPlaySpeed) AS Max_PlaySpeed,
    MIN(a.buildUpPlaySpeed) AS Min_PlaySpeed, 
    
    
        RANK() 
        OVER (PARTITION BY Season ORDER BY ROUND(AVG(a.buildUpPlaySpeed),2) DESC) AS Ranking      
       
        FROM best_leagues l
        INNER JOIN Match m ON m.league_id = l.id
        INNER JOIN Team t ON t.team_api_id = m.home_team_api_id
        INNER JOIN Team_Attributes a ON a.team_api_id = t.team_api_id
  
GROUP BY League, Season;

 * sqlite:///database.sqlite
Done.


%%sql
WITH stats AS(
SELECT l.name AS League, t.team_long_name AS team_name, ROUND(AVG(a.buildUpPlayPassing),2) AS PlayPassing_Score,
    RANK() 
        OVER (PARTITION BY l.name ORDER BY ROUND(AVG(a.buildUpPlayPassing),2) DESC ) AS Ranking
    FROM best_leagues l
        INNER JOIN Match m ON m.league_id = l.id
        INNER JOIN Team t ON t.team_api_id = m.home_team_api_id
        INNER JOIN Team_Attributes a ON a.team_api_id = t.team_api_id
    GROUP BY League, team_name

)

SELECT * FROM (SELECT * FROM stats WHERE League = 'Bundesliga' LIMIT 5)
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'League 1' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'La Liga' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'Premier League' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'Serie A' LIMIT 5 )
;

 * sqlite:///database.sqlite
Done.


%%sql
WITH stats AS(
SELECT l.name AS League, t.team_long_name AS team_name, ROUND(AVG(a.buildUpPlayDribbling),2) AS PlayDribbling_Score,
    RANK() 
        OVER (PARTITION BY l.name ORDER BY ROUND(AVG(a.buildUpPlayDribbling),2) DESC ) AS Ranking
    FROM best_leagues l
        INNER JOIN Match m ON m.league_id = l.id
        INNER JOIN Team t ON t.team_api_id = m.home_team_api_id
        INNER JOIN Team_Attributes a ON a.team_api_id = t.team_api_id
    GROUP BY League, team_name

)

SELECT * FROM (SELECT * FROM stats WHERE League = 'Bundesliga' LIMIT 5)
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'League 1' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'La Liga' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'Premier League' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM stats WHERE League = 'Serie A' LIMIT 5 )
;

 * sqlite:///database.sqlite
Done.


%%sql
DROP VIEW IF EXISTS win_loss;

CREATE VIEW win_loss AS
SELECT season, home_team_api_id, away_team_api_id, league_id,
    CASE WHEN home_team_goal > away_team_goal THEN home_team_api_id
    WHEN away_team_goal > home_team_goal THEN away_team_api_id
    ELSE 'draw'
    END who_won,
    CASE WHEN home_team_goal > away_team_goal THEN away_team_api_id
    WHEN away_team_goal > home_team_goal THEN home_team_api_id
    ELSE 'draw'
    END who_lose
    
    FROM Match;

 * sqlite:///database.sqlite
Done.
Done.

[]


%%sql
DROP VIEW IF EXISTS victories_ranking;

CREATE VIEW victories_ranking AS
SELECT w.season, t.team_api_id, l.name AS League, t.team_long_name AS Team, COUNT(w.who_won) AS Wins, 
RANK()
    OVER(PARTITION BY w.season ORDER BY COUNT(w.who_won) DESC) AS ranking
FROM win_loss w
    INNER JOIN best_leagues l ON l.id = w.league_id
    INNER JOIN best_teams t ON t.team_api_id = w.who_won
GROUP BY w.season, w.who_won;

 * sqlite:///database.sqlite
Done.
Done.

[]


%%sql
DROP VIEW IF EXISTS losses_ranking;

CREATE VIEW losses_ranking AS
SELECT t.team_api_id, l.name AS League, t.team_long_name AS Team, COUNT(w.who_lose) AS Losses,
RANK()
    OVER(PARTITION BY w.season ORDER BY COUNT(w.who_lose) DESC) AS ranking
FROM win_loss w
    JOIN best_leagues l ON l.id = w.league_id
    JOIN best_teams t ON t.team_api_id = w.who_lose
GROUP BY w.season, w.who_lose;

 * sqlite:///database.sqlite
Done.
Done.

[]


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2008/2009' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2009/2010' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2010/2011' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2011/2012' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2012/2013' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2013/2014' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM (SELECT * FROM victories_ranking WHERE season = '2014/2015' ORDER BY Ranking LIMIT 5)

 * sqlite:///database.sqlite
Done.


%%sql
WITH wins AS (
SELECT l.name AS League, t.team_long_name AS Team, COUNT(w.who_lose) AS Loss,
        a.buildUpPlaySpeedClass AS speed_class, a.buildUpPlayDribblingClass AS dribbling_class,
        a.buildUpPlayPassingClass AS passing_class, a.chanceCreationPassingClass AS creation_passing_class,
        a.chanceCreationShootingCLass AS creation_shooting_class, a.defencePressureClass AS pressure_class,
        a.defenceAggressionClass AS agression_class, a.defenceTeamWidthClass AS team_width_class,
        a.defenceDefenderLineClass AS defender_line_class, a.buildUpPlayPositioningClass AS possition_class
    
FROM win_loss w
    JOIN best_leagues l ON l.id = w.league_id
    JOIN best_teams t ON t.team_api_id = w.who_lose
    JOIN Team_Attributes a ON a.team_api_id = w.who_lose
GROUP BY w.who_lose
ORDER BY COUNT(w.who_lose) DESC
)

SELECT * FROM (SELECT * FROM wins WHERE League = 'Bundesliga' LIMIT 5)
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'League 1' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'La Liga' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'Premier League' LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'Serie A' LIMIT 5 )

 * sqlite:///database.sqlite
Done.


%%sql
WITH wins AS (
SELECT l.name AS League, t.team_long_name AS Team, COUNT(w.who_won) AS 'Win',
        a.buildUpPlaySpeedClass AS speed_class, a.buildUpPlayDribblingClass AS dribbling_class,
        a.buildUpPlayPassingClass AS passing_class, a.chanceCreationPassingClass AS creation_passing_class,
        a.chanceCreationShootingCLass AS creation_shooting_class, a.defencePressureClass AS pressure_class,
        a.defenceAggressionClass AS agression_class, a.defenceTeamWidthClass AS team_width_class,
        a.defenceDefenderLineClass AS defender_line_class, a.buildUpPlayPositioningClass AS possition_class
FROM win_loss w
    JOIN best_leagues l ON l.id = w.league_id
    JOIN best_teams t ON t.team_api_id = w.who_won
    JOIN Team_Attributes a ON a.team_api_id = t.team_api_id
GROUP BY w.who_won
ORDER BY Win DESC
)

SELECT * FROM (SELECT * FROM wins WHERE League = 'Bundesliga' ORDER BY Win DESC LIMIT 5)
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'League 1' ORDER BY Win DESC LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'La Liga' ORDER BY Win DESC LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'Premier League' ORDER BY Win DESC LIMIT 5 )
UNION ALL
SELECT * FROM (SELECT * FROM wins WHERE League = 'Serie A' ORDER BY Win DESC LIMIT 5 )

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.buildUpPlaySpeedClass AS 'Speed Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.buildUpPlaySpeedClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.buildUpPlayDribblingClass AS 'Dribbling Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.buildUpPlayDribblingClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.buildUpPlayPassingClass AS 'Passing Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.buildUpPlayPassingClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.buildUpPlayPositioningClass AS 'Positioning Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.buildUpPlayPositioningClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.chanceCreationPassingClass AS 'Creation Passing Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.chanceCreationPassingClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.chanceCreationCrossingClass AS 'Creation Crossing Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.chanceCreationCrossingClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.chanceCreationShootingClass AS 'Creation Shooting Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.chanceCreationShootingClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.chanceCreationPositioningClass AS 'Creation Positioning Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.chanceCreationPositioningClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.defencePressureClass AS 'Defence Pressure Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.defencePressureClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.defenceAggressionClass AS 'Defence Aggression Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.defenceAggressionClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.defenceTeamWidthClass AS 'Defence Team Width Class', ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.defenceTeamWidthClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.defenceDefenderLineClass AS 'Defence Line Class',ROUND(AVG(v.Wins)) AS 'Average of Wins',
    ROUND(AVG(l.Losses)) AS "Average of Losses", ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses"
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
        GROUP BY a.defenceDefenderLineClass
        ORDER BY "Rate of Wins vs Losses" DESC;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.buildUpPlaySpeedClass AS 'Speed Class', a.buildUpPlayDribblingClass AS 'Dribbling Class',
    a.buildUpPlayPassingClass AS 'Passing Class', a.buildUpPlayPositioningClass AS 'Positioning Class',
    a.chanceCreationShootingClass AS 'Creation Shooting Class', a.defenceTeamWidthClass AS 'Defence Team Width Class',
    ROUND(AVG(v.Wins)) AS 'Average of Wins', ROUND(AVG(l.Losses)) AS "Average of Losses", 
    ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses", RANK() OVER(ORDER BY ROUND(AVG(v.Wins)/AVG(l.Losses),2) DESC) AS "Ranking"
    
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
            
        GROUP BY a.buildUpPlaySpeedClass, a.buildUpPlayDribblingClass, a.buildUpPlayPassingClass, a.buildUpPlayPositioningClass,
                a.chanceCreationShootingClass, a.defenceTeamWidthClass
        ORDER BY "Rate of Wins vs Losses" DESC
        LIMIT 10;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT a.buildUpPlaySpeedClass AS 'Speed Class', a.buildUpPlayDribblingClass AS 'Dribbling Class',
    a.buildUpPlayPassingClass AS 'Passing Class', a.buildUpPlayPositioningClass AS 'Positioning Class',
    a.chanceCreationPassingClass AS 'Creation Passing Class', a.chanceCreationCrossingClass AS 'Creation Crossing Class',
    a.chanceCreationShootingClass AS 'Creation Shooting Class', a.defencePressureClass AS 'Defence Pressure',
    a.defenceAggressionClass AS 'Defence Aggression Class', a.defenceTeamWidthClass AS 'Defence Team Width Class',
    a.defenceDefenderLineClass AS 'Defender Line Class',
    ROUND(AVG(v.Wins)) AS 'Average of Wins', ROUND(AVG(l.Losses)) AS "Average of Losses", 
    ROUND(AVG(v.Wins)/AVG(l.Losses),2) AS "Rate of Wins vs Losses",
    RANK() OVER(ORDER BY ROUND(AVG(v.Wins)/AVG(l.Losses),2) DESC) AS "Ranking"
    
        FROM Team_Attributes a
            INNER JOIN victories_ranking v ON v.team_api_id = a.team_api_id
            INNER JOIN losses_ranking l ON l.team_api_id = a.team_api_id
            
        GROUP BY a.buildUpPlaySpeedClass, a.buildUpPlayDribblingClass, a.buildUpPlayPassingClass, a.buildUpPlayPositioningClass,
                a.chanceCreationPassingClass, a.chanceCreationCrossingClass, a.chanceCreationShootingClass,
                a.defencePressure, a.defenceAggressionClass, a.defenceTeamWidthClass, a.defenceDefenderLineClass
        ORDER BY "Rate of Wins vs Losses" DESC
        LIMIT 10;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT * FROM victories_ranking limit 10;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT v.* FROM victories_ranking v 
    INNER JOIN Team_Attributes a ON a.team_api_id = v.team_api_id
    WHERE a.buildUpPlaySpeedClass = 'Balanced' AND a.buildUpPlayDribblingClass = 'Normal' AND
    a.buildUpPlayPassingClass = 'Short' AND (a.buildUpPlayPositioningClass = 'Free Form' OR a.buildUpPlayPositioningClass = 'Organised') AND
    (a.chanceCreationShootingClass = 'Little' OR a.chanceCreationShootingClass = 'Normal') AND a.defenceTeamWidthClass = 'Normal'
   
GROUP BY v.season, v.team_api_id;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT COUNT(*) FROM best_teams;

 * sqlite:///database.sqlite
Done.


%%sql
DROP VIEW IF EXISTS player_ranking;

CREATE VIEW player_ranking AS 
SELECT p.player_api_id, p.player_name AS 'Player', ROUND(AVG(a.overall_rating),2) AS 'Rating', 
RANK() OVER(ORDER BY ROUND(AVG(a.overall_rating),2) DESC) AS 'Ranking'
    FROM Player p 
        INNER JOIN Player_attributes a ON p.player_api_id = a.player_api_id
    GROUP BY p.player_name
    ORDER BY ROUND(AVG(a.overall_rating),2) DESC;

 * sqlite:///database.sqlite
Done.
Done.

[]


%%sql
SELECT * FROM player_ranking LIMIT 10;

 * sqlite:///database.sqlite
Done.


%%sql
SELECT 'Player with higher potential' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.potential AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.potential = (SELECT MAX(potential) FROM Player_Attributes)
    GROUP BY r.Player
    
UNION

SELECT 'Player with best heading_accuracy' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.heading_accuracy AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.heading_accuracy = (SELECT MAX(heading_accuracy) FROM Player_Attributes)
    GROUP BY r.Player
    
UNION

SELECT 'Player with best dribbling' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.dribbling AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.dribbling = (SELECT MAX(dribbling) FROM Player_Attributes)
    GROUP BY r.Player
    
UNION

SELECT 'Player with best ball control' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.ball_control AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.ball_control = (SELECT MAX(ball_control) FROM Player_Attributes)
    GROUP BY r.Player
    
UNION

SELECT 'Player with higher free kick accuracy' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.free_kick_accuracy AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.free_kick_accuracy = (SELECT MAX(free_kick_accuracy) FROM Player_Attributes)
    GROUP BY r.Player
    
UNION

SELECT 'Player with more interceptions' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.interceptions AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.interceptions = (SELECT MAX(interceptions) FROM Player_Attributes)
    GROUP BY r.Player
    
UNION

SELECT 'Player with more penalties' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.penalties AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.penalties = (SELECT MAX(penalties) FROM Player_Attributes)
    GROUP BY r.Player    
    
UNION

SELECT 'Player with more sliding tackles' AS 'Attribute', r.Player AS 'Player Name', 
    r.Ranking AS 'Ranking of Rating', a.sliding_tackle AS 'Value'
    FROM player_ranking r
    JOIN Player_Attributes a ON a.player_api_id = r.player_api_id
    WHERE a.sliding_tackle = (SELECT MAX(sliding_tackle) FROM Player_Attributes)
    GROUP BY r.Player

 * sqlite:///database.sqlite
Done.

id	country_id	name
1	1	Belgium Jupiler League
1729	1729	England Premier League
4769	4769	France Ligue 1
7809	7809	Germany 1. Bundesliga
10257	10257	Italy Serie A
13274	13274	Netherlands Eredivisie
15722	15722	Poland Ekstraklasa
17642	17642	Portugal Liga ZON Sagres
19694	19694	Scotland Premier League
21518	21518	Spain LIGA BBVA
24558	24558	Switzerland Super League

id	country_id	name
1729	1729	Premier League
4769	4769	League 1
7809	7809	Bundesliga
10257	10257	Serie A
21518	21518	La Liga

id	country_id	league_id	season	stage	date	match_api_id	home_team_api_id	away_team_api_id	home_team_goal	away_team_goal	home_player_X1	home_player_X2	home_player_X3	home_player_X4	home_player_X5	home_player_X6	home_player_X7	home_player_X8	home_player_X9	home_player_X10	home_player_X11	away_player_X1	away_player_X2	away_player_X3	away_player_X4	away_player_X5	away_player_X6	away_player_X7	away_player_X8	away_player_X9	away_player_X10	away_player_X11	home_player_Y1	home_player_Y2	home_player_Y3	home_player_Y4	home_player_Y5	home_player_Y6	home_player_Y7	home_player_Y8	home_player_Y9	home_player_Y10	home_player_Y11	away_player_Y1	away_player_Y2	away_player_Y3	away_player_Y4	away_player_Y5	away_player_Y6	away_player_Y7	away_player_Y8	away_player_Y9	away_player_Y10	away_player_Y11	home_player_1	home_player_2	home_player_3	home_player_4	home_player_5	home_player_6	home_player_7	home_player_8	home_player_9	home_player_10	home_player_11	away_player_1	away_player_2	away_player_3	away_player_4	away_player_5	away_player_6	away_player_7	away_player_8	away_player_9	away_player_10	away_player_11	goal	shoton	shotoff	foulcommit	card	cross	corner	possession	B365H	B365D	B365A	BWH	BWD	BWA	IWH	IWD	IWA	LBH	LBD	LBA	PSH	PSD	PSA	WHH	WHD	WHA	SJH	SJD	SJA	VCH	VCD	VCA	GBH	GBD	GBA	BSH	BSD	BSA
757	1	1	2011/2012	1	2011-07-29 00:00:00	1032692	1773	8635	2	1	1	2	4	6	8	3	5	7	3	5	7	1	3	4	7	1	3	5	7	9	4	6	1	3	3	3	3	7	7	7	10	10	10	1	3	3	3	7	7	7	7	7	10	10	37993	37865	37051	45840	179059	37981	38791	37963	38777	45865	68114	38391	38389	208493	149150	40536	38253	114333	178249	265123	46552	181276	None	None	None	None	None	None	None	None	7	4	1.5	6.5	4.1	1.42	5	3.7	1.5	5.5	3.6	1.5	None	None	None	5.5	3.75	1.57	6	3.6	1.57	7	4	1.5	6	3.75	1.5	6.5	4	1.44
758	1	1	2011/2012	1	2011-07-30 00:00:00	1032693	9998	9985	1	1	1	1	3	5	7	9	3	4	8	6	5	1	2	4	6	8	6	5	4	3	5	7	1	3	3	3	3	3	7	7	7	7	11	1	3	3	3	3	6	8	6	10	10	10	39153	39977	181574	166577	38906	36849	21753	37128	149258	26224	78902	38797	129462	245653	164229	33620	38969	17276	119117	38382	248689	46335	None	None	None	None	None	None	None	None	5	3.5	1.73	4.75	3.5	1.65	3.8	3.3	1.75	4.5	3.5	1.62	None	None	None	4.33	3.5	1.75	4.5	3.5	1.73	4.75	3.6	1.75	4.5	3.4	1.72	4.5	3.6	1.67
759	1	1	2011/2012	1	2011-07-30 00:00:00	1032694	9987	9993	3	1	1	2	4	6	8	2	4	6	8	4	6	1	2	4	6	8	3	5	7	3	5	7	1	3	3	3	3	7	7	7	7	10	10	1	3	3	3	3	7	7	7	10	10	10	91929	94462	38368	148314	109331	104411	39498	169200	43158	42153	38794	36873	57078	38800	174363	27508	38784	163613	38371	33622	166679	14487	None	None	None	None	None	None	None	None	1.44	4.33	7	1.45	3.95	6.25	1.5	3.7	5	1.44	3.75	6	None	None	None	1.4	4.33	7	1.44	4.2	6.5	1.44	4.5	7	1.45	4	6.25	1.44	4	6.5
760	1	1	2011/2012	1	2011-07-30 00:00:00	1032695	9991	9984	0	1	1	1	3	5	7	9	3	5	7	4	6	1	2	4	6	8	3	5	7	3	5	7	1	3	3	3	3	3	7	7	7	10	10	1	3	3	3	3	7	7	7	10	10	10	37854	12473	114368	178096	37440	None	33662	26771	166618	148329	12574	36835	38342	243250	37047	38789	27110	166670	188231	277766	38251	209855	None	None	None	None	None	None	None	None	1.57	3.8	6	1.55	3.85	5	1.55	3.5	4.8	1.57	3.5	5	None	None	None	1.6	3.75	5	1.5	3.9	6	1.53	4	6	1.55	3.75	5.5	1.53	3.75	5.5
761	1	1	2011/2012	1	2011-07-30 00:00:00	1032696	9994	10000	0	0	1	3	4	7	1	3	5	7	9	4	6	1	1	3	5	7	9	3	5	7	4	6	1	3	3	3	7	7	7	7	7	10	10	1	3	3	3	3	3	7	7	7	10	10	30934	94030	25791	166675	95609	38290	67898	30910	42706	104406	104404	37900	37100	41005	46877	80678	37886	131530	208984	None	208852	240044	None	None	None	None	None	None	None	None	2.2	3.3	3.3	2.15	3.25	3.05	2.1	3.1	3	2	3.2	3.2	None	None	None	2	3.3	3.4	2.2	3.2	3.2	2.2	3.3	3.3	2.15	3.25	3.1	2.2	3.3	2.88

id	player_api_id	player_name	player_fifa_api_id	date(birthday)	birth_year	birth_month	birth_day	height	weight
9677	2984	Sergio Aragones	111106	1977-02-01	1977	02	01	182.88	176
6856	5440	Marjan Petkovic	122118	1979-05-22	1979	05	22	185.42	185
4784	11316	Jean-Louis Leca	153275	1985-09-21	1985	09	21	180.34	165
1232	11319	Benoit Costil	158121	1987-07-03	1987	07	03	187.96	190
9895	11320	Steeve Elana	111163	1980-07-11	1980	07	11	187.96	187

id	team_api_id	team_fifa_api_id	team_long_name	team_short_name
3457	10260	11	Manchester United	MUN
3459	9825	1	Arsenal	ARS
3461	8472	106	Sunderland	SUN
3463	8654	19	West Ham United	WHU
3465	10252	2	Aston Villa	AVL

type	name	tbl_name	rootpage	sql
table	sqlite_sequence	sqlite_sequence	4	CREATE TABLE sqlite_sequence(name,seq)
table	Player_Attributes	Player_Attributes	11	CREATE TABLE "Player_Attributes" ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `player_fifa_api_id` INTEGER, `player_api_id` INTEGER, `date` TEXT, `overall_rating` INTEGER, `potential` INTEGER, `preferred_foot` TEXT, `attacking_work_rate` TEXT, `defensive_work_rate` TEXT, `crossing` INTEGER, `finishing` INTEGER, `heading_accuracy` INTEGER, `short_passing` INTEGER, `volleys` INTEGER, `dribbling` INTEGER, `curve` INTEGER, `free_kick_accuracy` INTEGER, `long_passing` INTEGER, `ball_control` INTEGER, `acceleration` INTEGER, `sprint_speed` INTEGER, `agility` INTEGER, `reactions` INTEGER, `balance` INTEGER, `shot_power` INTEGER, `jumping` INTEGER, `stamina` INTEGER, `strength` INTEGER, `long_shots` INTEGER, `aggression` INTEGER, `interceptions` INTEGER, `positioning` INTEGER, `vision` INTEGER, `penalties` INTEGER, `marking` INTEGER, `standing_tackle` INTEGER, `sliding_tackle` INTEGER, `gk_diving` INTEGER, `gk_handling` INTEGER, `gk_kicking` INTEGER, `gk_positioning` INTEGER, `gk_reflexes` INTEGER, FOREIGN KEY(`player_fifa_api_id`) REFERENCES `Player`(`player_fifa_api_id`), FOREIGN KEY(`player_api_id`) REFERENCES `Player`(`player_api_id`) )
table	Player	Player	14	CREATE TABLE `Player` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `player_api_id` INTEGER UNIQUE, `player_name` TEXT, `player_fifa_api_id` INTEGER UNIQUE, `birthday` TEXT, `height` INTEGER, `weight` INTEGER )
table	Match	Match	18	CREATE TABLE `Match` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `country_id` INTEGER, `league_id` INTEGER, `season` TEXT, `stage` INTEGER, `date` TEXT, `match_api_id` INTEGER UNIQUE, `home_team_api_id` INTEGER, `away_team_api_id` INTEGER, `home_team_goal` INTEGER, `away_team_goal` INTEGER, `home_player_X1` INTEGER, `home_player_X2` INTEGER, `home_player_X3` INTEGER, `home_player_X4` INTEGER, `home_player_X5` INTEGER, `home_player_X6` INTEGER, `home_player_X7` INTEGER, `home_player_X8` INTEGER, `home_player_X9` INTEGER, `home_player_X10` INTEGER, `home_player_X11` INTEGER, `away_player_X1` INTEGER, `away_player_X2` INTEGER, `away_player_X3` INTEGER, `away_player_X4` INTEGER, `away_player_X5` INTEGER, `away_player_X6` INTEGER, `away_player_X7` INTEGER, `away_player_X8` INTEGER, `away_player_X9` INTEGER, `away_player_X10` INTEGER, `away_player_X11` INTEGER, `home_player_Y1` INTEGER, `home_player_Y2` INTEGER, `home_player_Y3` INTEGER, `home_player_Y4` INTEGER, `home_player_Y5` INTEGER, `home_player_Y6` INTEGER, `home_player_Y7` INTEGER, `home_player_Y8` INTEGER, `home_player_Y9` INTEGER, `home_player_Y10` INTEGER, `home_player_Y11` INTEGER, `away_player_Y1` INTEGER, `away_player_Y2` INTEGER, `away_player_Y3` INTEGER, `away_player_Y4` INTEGER, `away_player_Y5` INTEGER, `away_player_Y6` INTEGER, `away_player_Y7` INTEGER, `away_player_Y8` INTEGER, `away_player_Y9` INTEGER, `away_player_Y10` INTEGER, `away_player_Y11` INTEGER, `home_player_1` INTEGER, `home_player_2` INTEGER, `home_player_3` INTEGER, `home_player_4` INTEGER, `home_player_5` INTEGER, `home_player_6` INTEGER, `home_player_7` INTEGER, `home_player_8` INTEGER, `home_player_9` INTEGER, `home_player_10` INTEGER, `home_player_11` INTEGER, `away_player_1` INTEGER, `away_player_2` INTEGER, `away_player_3` INTEGER, `away_player_4` INTEGER, `away_player_5` INTEGER, `away_player_6` INTEGER, `away_player_7` INTEGER, `away_player_8` INTEGER, `away_player_9` INTEGER, `away_player_10` INTEGER, `away_player_11` INTEGER, `goal` TEXT, `shoton` TEXT, `shotoff` TEXT, `foulcommit` TEXT, `card` TEXT, `cross` TEXT, `corner` TEXT, `possession` TEXT, `B365H` NUMERIC, `B365D` NUMERIC, `B365A` NUMERIC, `BWH` NUMERIC, `BWD` NUMERIC, `BWA` NUMERIC, `IWH` NUMERIC, `IWD` NUMERIC, `IWA` NUMERIC, `LBH` NUMERIC, `LBD` NUMERIC, `LBA` NUMERIC, `PSH` NUMERIC, `PSD` NUMERIC, `PSA` NUMERIC, `WHH` NUMERIC, `WHD` NUMERIC, `WHA` NUMERIC, `SJH` NUMERIC, `SJD` NUMERIC, `SJA` NUMERIC, `VCH` NUMERIC, `VCD` NUMERIC, `VCA` NUMERIC, `GBH` NUMERIC, `GBD` NUMERIC, `GBA` NUMERIC, `BSH` NUMERIC, `BSD` NUMERIC, `BSA` NUMERIC, FOREIGN KEY(`country_id`) REFERENCES `country`(`id`), FOREIGN KEY(`league_id`) REFERENCES `League`(`id`), FOREIGN KEY(`home_team_api_id`) REFERENCES `Team`(`team_api_id`), FOREIGN KEY(`away_team_api_id`) REFERENCES `Team`(`team_api_id`), FOREIGN KEY(`home_player_1`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_2`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_3`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_4`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_5`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_6`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_7`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_8`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_9`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_10`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`home_player_11`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_1`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_2`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_3`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_4`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_5`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_6`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_7`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_8`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_9`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_10`) REFERENCES `Player`(`player_api_id`), FOREIGN KEY(`away_player_11`) REFERENCES `Player`(`player_api_id`) )
table	League	League	24	CREATE TABLE `League` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `country_id` INTEGER, `name` TEXT UNIQUE, FOREIGN KEY(`country_id`) REFERENCES `country`(`id`) )
table	Country	Country	26	CREATE TABLE `Country` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT UNIQUE )
table	Team	Team	29	CREATE TABLE "Team" ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `team_api_id` INTEGER UNIQUE, `team_fifa_api_id` INTEGER, `team_long_name` TEXT, `team_short_name` TEXT )
table	Team_Attributes	Team_Attributes	2	CREATE TABLE `Team_Attributes` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `team_fifa_api_id` INTEGER, `team_api_id` INTEGER, `date` TEXT, `buildUpPlaySpeed` INTEGER, `buildUpPlaySpeedClass` TEXT, `buildUpPlayDribbling` INTEGER, `buildUpPlayDribblingClass` TEXT, `buildUpPlayPassing` INTEGER, `buildUpPlayPassingClass` TEXT, `buildUpPlayPositioningClass` TEXT, `chanceCreationPassing` INTEGER, `chanceCreationPassingClass` TEXT, `chanceCreationCrossing` INTEGER, `chanceCreationCrossingClass` TEXT, `chanceCreationShooting` INTEGER, `chanceCreationShootingClass` TEXT, `chanceCreationPositioningClass` TEXT, `defencePressure` INTEGER, `defencePressureClass` TEXT, `defenceAggression` INTEGER, `defenceAggressionClass` TEXT, `defenceTeamWidth` INTEGER, `defenceTeamWidthClass` TEXT, `defenceDefenderLineClass` TEXT, FOREIGN KEY(`team_fifa_api_id`) REFERENCES `Team`(`team_fifa_api_id`), FOREIGN KEY(`team_api_id`) REFERENCES `Team`(`team_api_id`) )

id	team_fifa_api_id	team_api_id	date	buildUpPlaySpeed	buildUpPlaySpeedClass	buildUpPlayDribbling	buildUpPlayDribblingClass	buildUpPlayPassing	buildUpPlayPassingClass	buildUpPlayPositioningClass	chanceCreationPassing	chanceCreationPassingClass	chanceCreationCrossing	chanceCreationCrossingClass	chanceCreationShooting	chanceCreationShootingClass	chanceCreationPositioningClass	defencePressure	defencePressureClass	defenceAggression	defenceAggressionClass	defenceTeamWidth	defenceTeamWidthClass	defenceDefenderLineClass
1	434	9930	2010-02-22 00:00:00	60	Balanced	None	Little	50	Mixed	Organised	60	Normal	65	Normal	55	Normal	Organised	50	Medium	55	Press	45	Normal	Cover
2	434	9930	2014-09-19 00:00:00	52	Balanced	48	Normal	56	Mixed	Organised	54	Normal	63	Normal	64	Normal	Organised	47	Medium	44	Press	54	Normal	Cover
3	434	9930	2015-09-10 00:00:00	47	Balanced	41	Normal	54	Mixed	Organised	54	Normal	63	Normal	64	Normal	Organised	47	Medium	44	Press	54	Normal	Cover
4	77	8485	2010-02-22 00:00:00	70	Fast	None	Little	70	Long	Organised	70	Risky	70	Lots	70	Lots	Organised	60	Medium	70	Double	70	Wide	Cover
5	77	8485	2011-02-22 00:00:00	47	Balanced	None	Little	52	Mixed	Organised	53	Normal	48	Normal	52	Normal	Organised	47	Medium	47	Press	52	Normal	Cover
6	77	8485	2012-02-22 00:00:00	58	Balanced	None	Little	62	Mixed	Organised	45	Normal	70	Lots	55	Normal	Organised	40	Medium	40	Press	60	Normal	Cover
7	77	8485	2013-09-20 00:00:00	62	Balanced	None	Little	45	Mixed	Organised	40	Normal	50	Normal	55	Normal	Organised	42	Medium	42	Press	60	Normal	Cover
8	77	8485	2014-09-19 00:00:00	58	Balanced	64	Normal	62	Mixed	Organised	56	Normal	68	Lots	57	Normal	Organised	41	Medium	42	Press	60	Normal	Cover
9	77	8485	2015-09-10 00:00:00	59	Balanced	64	Normal	53	Mixed	Organised	51	Normal	72	Lots	63	Normal	Free Form	49	Medium	45	Press	63	Normal	Cover
10	614	8576	2010-02-22 00:00:00	60	Balanced	None	Little	40	Mixed	Organised	45	Normal	35	Normal	55	Normal	Organised	30	Deep	70	Double	30	Narrow	Offside Trap

League	Season	Avg_PlaySpeed	Max_PlaySpeed	Min_PlaySpeed	Ranking
Premier League	2008/2009	56.77	77	25	1
Bundesliga	2008/2009	56.51	78	31	2
Serie A	2008/2009	56.01	78	26	3
League 1	2008/2009	53.57	70	30	4
La Liga	2008/2009	47.7	71	20	5
Bundesliga	2009/2010	56.39	78	31	1
Premier League	2009/2010	56.12	77	25	2
Serie A	2009/2010	55.3	78	26	3
League 1	2009/2010	53.47	70	30	4
La Liga	2009/2010	47.2	70	20	5
Bundesliga	2010/2011	56.61	78	31	1
Premier League	2010/2011	56.43	77	25	2
Serie A	2010/2011	54.82	78	26	3
League 1	2010/2011	53.21	70	23	4
La Liga	2010/2011	46.98	70	20	5
Bundesliga	2011/2012	56.75	78	31	1
Serie A	2011/2012	56.45	78	26	2
Premier League	2011/2012	55.52	75	25	3
League 1	2011/2012	53.88	70	30	4
La Liga	2011/2012	46.72	70	20	5
Bundesliga	2012/2013	57.06	78	31	1
Serie A	2012/2013	56.01	78	26	2
Premier League	2012/2013	55.57	77	25	3
League 1	2012/2013	53.18	70	34	4
La Liga	2012/2013	47.11	70	20	5
Bundesliga	2013/2014	57.0	78	31	1
Premier League	2013/2014	56.87	77	25	2
Serie A	2013/2014	55.5	78	26	3
League 1	2013/2014	53.29	70	34	4
La Liga	2013/2014	46.63	70	20	5
Bundesliga	2014/2015	57.88	78	31	1
Premier League	2014/2015	56.26	77	25	2
Serie A	2014/2015	55.64	78	26	3
League 1	2014/2015	53.11	70	30	4
La Liga	2014/2015	46.55	70	20	5
Bundesliga	2015/2016	57.56	78	31	1
Premier League	2015/2016	56.66	77	25	2
Serie A	2015/2016	56.23	80	26	3
League 1	2015/2016	52.39	70	34	4
La Liga	2015/2016	47.53	70	20	5

League	team_name	PlayPassing_Score	Ranking
Bundesliga	SV Darmstadt 98	77.0	1
Bundesliga	1. FC Köln	61.17	2
Bundesliga	DSC Arminia Bielefeld	59.33	3
Bundesliga	Eintracht Braunschweig	56.0	4
Bundesliga	FC St. Pauli	55.67	5
League 1	OGC Nice	61.83	1
League 1	Grenoble Foot 38	60.0	2
League 1	Toulouse FC	55.33	3
League 1	AJ Auxerre	55.17	4
League 1	Valenciennes FC	54.0	5
La Liga	UD Almería	60.5	1
La Liga	Levante UD	56.67	2
La Liga	Sevilla FC	56.0	3
La Liga	Elche CF	55.33	4
La Liga	Real Valladolid	53.67	5
Premier League	Stoke City	67.0	1
Premier League	Birmingham City	66.0	2
Premier League	Bolton Wanderers	63.83	3
Premier League	Blackburn Rovers	63.83	3
Premier League	Cardiff City	62.17	5
Serie A	Chievo Verona	57.67	1
Serie A	Inter	57.17	2
Serie A	Livorno	53.17	3
Serie A	Udinese	53.0	4
Serie A	Sassuolo	51.67	5

League	team_name	PlayDribbling_Score	Ranking
Bundesliga	Fortuna Düsseldorf	65.5	1
Bundesliga	SpVgg Greuther Fürth	60.0	2
Bundesliga	1. FC Nürnberg	60.0	2
Bundesliga	Hamburger SV	58.5	4
Bundesliga	VfB Stuttgart	58.0	5
League 1	Olympique de Marseille	69.5	1
League 1	Stade Rennais FC	62.0	2
League 1	FC Sochaux-Montbéliard	62.0	2
League 1	FC Nantes	61.5	4
League 1	SC Bastia	61.0	5
La Liga	RC Celta de Vigo	56.5	1
La Liga	Granada CF	56.0	2
La Liga	Real Madrid CF	55.5	3
La Liga	UD Las Palmas	55.0	4
La Liga	RC Deportivo de La Coruña	55.0	4
Premier League	Liverpool	54.0	1
Premier League	Arsenal	51.0	2
Premier League	Chelsea	46.5	3
Premier League	Blackburn Rovers	45.5	4
Premier League	Tottenham Hotspur	40.5	5
Serie A	Napoli	72.0	1
Serie A	Milan	70.0	2
Serie A	Torino	69.0	3
Serie A	Parma	68.5	4
Serie A	Sassuolo	68.0	5

season	team_api_id	League	Team	Wins	ranking
2009/2010	8634	La Liga	FC Barcelona	31	1
2009/2010	8633	La Liga	Real Madrid CF	31	1
2009/2010	10260	Premier League	Manchester United	27	3
2009/2010	8455	Premier League	Chelsea	27	3
2009/2010	8686	Serie A	Roma	24	5

season	team_api_id	League	Team	Wins	ranking
2013/2014	9885	Serie A	Juventus	33	1
2013/2014	9823	Bundesliga	FC Bayern Munich	29	2
2013/2014	9906	La Liga	Atlético Madrid	28	3
2013/2014	9847	League 1	Paris Saint-Germain	27	4
2013/2014	8634	La Liga	FC Barcelona	27	4

League	Team	Loss	speed_class	dribbling_class	passing_class	creation_passing_class	creation_shooting_class	pressure_class	agression_class	team_width_class	defender_line_class	possition_class
Bundesliga	Hannover 96	744	Balanced	Little	Long	Normal	Normal	High	Double	Wide	Cover	Organised
Bundesliga	Hamburger SV	678	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Bundesliga	VfB Stuttgart	672	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Bundesliga	TSG 1899 Hoffenheim	654	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Bundesliga	SV Werder Bremen	642	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Wide	Cover	Organised
League 1	OGC Nice	702	Balanced	Little	Mixed	Normal	Normal	Deep	Press	Normal	Cover	Organised
League 1	FC Lorient	696	Balanced	Little	Long	Safe	Normal	High	Press	Wide	Offside Trap	Organised
League 1	Toulouse FC	666	Balanced	Little	Long	Normal	Normal	Deep	Press	Narrow	Cover	Organised
League 1	FC Sochaux-Montbéliard	612	Balanced	Little	Long	Normal	Normal	Medium	Contain	Normal	Cover	Organised
League 1	Stade Rennais FC	612	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
La Liga	Getafe CF	834	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
La Liga	RCD Espanyol	804	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
La Liga	Málaga CF	720	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Organised
La Liga	Athletic Club de Bilbao	666	Balanced	Little	Mixed	Normal	Normal	Deep	Double	Normal	Cover	Organised
La Liga	Levante UD	618	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Premier League	Sunderland	804	Balanced	Little	Long	Risky	Normal	Medium	Double	Normal	Cover	Organised
Premier League	Aston Villa	780	Balanced	Little	Long	Normal	Normal	Medium	Press	Normal	Cover	Organised
Premier League	Stoke City	720	Balanced	Little	Long	Risky	Normal	Medium	Double	Normal	Cover	Organised
Premier League	Newcastle United	714	Balanced	Little	Long	Normal	Normal	Medium	Press	Normal	Cover	Organised
Premier League	West Bromwich Albion	708	Balanced	Little	Mixed	Normal	Normal	Deep	Contain	Normal	Cover	Organised
Serie A	Chievo Verona	774	Balanced	Little	Long	Normal	Normal	Deep	Press	Normal	Cover	Organised
Serie A	Genoa	702	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Serie A	Bologna	696	Balanced	Little	Mixed	Normal	Normal	Deep	Press	Normal	Cover	Organised
Serie A	Atalanta	690	Balanced	Little	Mixed	Normal	Normal	Deep	Contain	Normal	Offside Trap	Organised
Serie A	Cagliari	684	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised

League	Team	Win	speed_class	dribbling_class	passing_class	creation_passing_class	creation_shooting_class	pressure_class	agression_class	team_width_class	defender_line_class	possition_class
Bundesliga	FC Bayern Munich	1158	Balanced	Little	Mixed	Normal	Lots	High	Press	Normal	Cover	Free Form
Bundesliga	Borussia Dortmund	942	Fast	Little	Mixed	Normal	Lots	Medium	Double	Normal	Cover	Organised
Bundesliga	Bayer 04 Leverkusen	822	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Bundesliga	FC Schalke 04	762	Balanced	Little	Mixed	Normal	Lots	High	Press	Wide	Cover	Organised
Bundesliga	VfL Wolfsburg	702	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Organised
League 1	Paris Saint-Germain	1050	Balanced	Little	Mixed	Normal	Normal	High	Double	Wide	Offside Trap	Organised
League 1	Olympique Lyonnais	918	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Organised
League 1	LOSC Lille	882	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
League 1	Olympique de Marseille	858	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Organised
League 1	Girondins de Bordeaux	756	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
La Liga	FC Barcelona	1404	Balanced	Little	Mixed	Normal	Lots	High	Press	Wide	Cover	Free Form
La Liga	Real Madrid CF	1368	Balanced	Little	Mixed	Risky	Lots	High	Press	Normal	Cover	Free Form
La Liga	Atlético Madrid	1002	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Organised
La Liga	Valencia CF	852	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
La Liga	Sevilla FC	834	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Premier League	Manchester United	1152	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Organised
Premier League	Chelsea	1056	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Free Form
Premier League	Manchester City	1050	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Premier League	Arsenal	1020	Balanced	Little	Short	Safe	Normal	Deep	Press	Normal	Cover	Free Form
Premier League	Tottenham Hotspur	906	Balanced	Little	Mixed	Risky	Lots	Deep	Press	Normal	Cover	Free Form
Serie A	Juventus	1134	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Wide	Offside Trap	Free Form
Serie A	Roma	972	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Offside Trap	Free Form
Serie A	Inter	924	Balanced	Little	Mixed	Normal	Lots	Medium	Press	Normal	Cover	Free Form
Serie A	Milan	924	Balanced	Little	Mixed	Normal	Normal	Medium	Press	Normal	Cover	Organised
Serie A	Napoli	918	Balanced	Little	Mixed	Normal	Normal	Medium	Double	Normal	Cover	Free Form

season	team_api_id	League	Team	Wins	ranking
2008/2009	8456	Premier League	Manchester City	15	34
2008/2009	8535	Serie A	Fiorentina	21	10
2008/2009	8634	La Liga	FC Barcelona	27	2
2008/2009	9823	Bundesliga	FC Bayern Munich	20	14
2008/2009	9825	Premier League	Arsenal	20	14
2009/2010	8456	Premier League	Manchester City	18	21
2009/2010	8535	Serie A	Fiorentina	13	43
2009/2010	8634	La Liga	FC Barcelona	31	1
2009/2010	9823	Bundesliga	FC Bayern Munich	20	12
2009/2010	9825	Premier League	Arsenal	23	7
2009/2010	10167	Serie A	Parma	14	38
2010/2011	8456	Premier League	Manchester City	21	7
2010/2011	8535	Serie A	Fiorentina	12	46
2010/2011	8634	La Liga	FC Barcelona	30	1
2010/2011	9823	Bundesliga	FC Bayern Munich	19	15
2010/2011	9825	Premier League	Arsenal	19	15
2010/2011	10167	Serie A	Parma	11	64
2011/2012	8456	Premier League	Manchester City	28	2
2011/2012	8535	Serie A	Fiorentina	10	69
2011/2012	8634	La Liga	FC Barcelona	28	2
2011/2012	9823	Bundesliga	FC Bayern Munich	23	7
2011/2012	9825	Premier League	Arsenal	21	11
2011/2012	10167	Serie A	Parma	15	30
2012/2013	8456	Premier League	Manchester City	23	7
2012/2013	8535	Serie A	Fiorentina	21	11
2012/2013	8634	La Liga	FC Barcelona	32	1
2012/2013	9823	Bundesliga	FC Bayern Munich	29	2
2012/2013	9825	Premier League	Arsenal	21	11
2012/2013	10167	Serie A	Parma	13	42
2013/2014	8456	Premier League	Manchester City	27	4
2013/2014	8535	Serie A	Fiorentina	19	20
2013/2014	8634	La Liga	FC Barcelona	27	4
2013/2014	9823	Bundesliga	FC Bayern Munich	29	2
2013/2014	9825	Premier League	Arsenal	24	11
2013/2014	10167	Serie A	Parma	15	34
2014/2015	8456	Premier League	Manchester City	24	6
2014/2015	8535	Serie A	Fiorentina	18	22
2014/2015	8634	La Liga	FC Barcelona	30	1
2014/2015	9823	Bundesliga	FC Bayern Munich	25	5
2014/2015	9825	Premier League	Arsenal	22	10
2014/2015	10167	Serie A	Parma	6	96
2015/2016	8456	Premier League	Manchester City	19	13
2015/2016	8535	Serie A	Fiorentina	18	17
2015/2016	8634	La Liga	FC Barcelona	29	2
2015/2016	9823	Bundesliga	FC Bayern Munich	28	4
2015/2016	9825	Premier League	Arsenal	20	11

Values	Category
1 - 33	SLOW
34 - 66	BALANCED
67 - 100	FAST

Values	Category
1 - 33	SHORT
34 - 66	MIXED
67 - 100	LONG

Values	Category
1 - 33	LITTLE
34 - 66	NORMAL
67 - 100	LOTS

Values	Category
1 - 33	SAFE
34 - 66	NORMAL
67 - 100	RISKY

player_api_id	Player	Rating	Ranking
30981	Lionel Messi	92.19	1
30893	Cristiano Ronaldo	91.28	2
30924	Franck Ribery	88.46	3
30955	Andres Iniesta	88.32	4
35724	Zlatan Ibrahimovic	88.29	5
30834	Arjen Robben	87.84	6
39854	Xavi Hernandez	87.64	7
30829	Wayne Rooney	87.22	8
30657	Iker Casillas	86.95	9
30894	Philipp Lahm	86.73	10

Attribute	Player Name	Ranking of Rating	Value
Player with best ball control	Lionel Messi	1	97
Player with best ball control	Ronaldinho	70	97
Player with best dribbling	Cristiano Ronaldo	2	97
Player with best dribbling	Lionel Messi	1	97
Player with best dribbling	Ronaldinho	70	97
Player with best heading_accuracy	Nikola Zigic	2911	98
Player with higher free kick accuracy	Juninho Pernambucano,20	429	97
Player with higher potential	Lionel Messi	1	97
Player with more interceptions	Andrea Pirlo	29	96
Player with more interceptions	Timmy Simons	1132	96
Player with more penalties	Rickie Lambert	1895	96
Player with more sliding tackles	Philipp Lahm	10	95

Playstyle for Victory¶

Analysis of Football European Teams¶

Objectives¶

Main Question¶

Table Of Contents:¶

1. Extracting information from the database¶

Leagues¶

2. Wrangling Data¶

Testing the newly created views¶

END OF WRANGLING PROCEDURE¶

3.Exploratory Data Analysis¶

Classes of Attributes¶

BuildUpPlaySpeed¶

BuildUpPlayDribbling¶

BuildUpPlayPassing¶

ChanceCreationPassingClass¶

ChanceCreationCrossingClass¶

ChanceCreationShootingClass¶

DefencePressureClass¶

DefenceAggressionClass¶

DefenceTeamWidthClass¶

Summary of Classes of Attributes¶

Ranking Teams¶

Ranking Leagues according their attributes¶

Leagues with best buildUpPlaySpeed¶

Creating a top 5 teams per Attribute per league¶

Identifying winners and Lossers¶

Extracting ranking of the teams with more victories¶

Top 5 Teams with more victories per season¶

Best Teams per Season Summary¶

Difference on Attributes for teams with more wins and losses¶

Attributes with teams with more losses¶

Top 5 Teams Per League with more Wins¶

Summary: Teams with more wins and losses¶

Similarities¶

Differences¶

Attributes vs Team Victory¶

Speed Class¶

Dribbling Class¶

Passing Class¶

Positioning Class¶

Creation Passing Class¶

Creation Crossing Class¶

Creation Shooting Class¶

Creation Positioning Class¶

Defence Pressure Class¶

Defence Aggression Class¶

Defence Team Width Class¶

Defence Line Class¶

Summary of Attributes per Victory¶

Combination of Attributes vs Victory¶

Best possible combination¶

Teams which employ the best attributes¶

Analysis of Results: Combined Attributes vs Victory¶

Best Players¶

Rating of the best players¶

Creating ranking for players according their overall rating¶

Players with some of the best attributes¶

Analysis of Results for players¶

4. Analysis of Results¶

5. Limitations¶

6. Future Ideas¶