<jupyter_start><jupyter_text>1. Which team scored the most points when playing at home?<jupyter_code>query = """
SELECT home_team_api_id,team_long_name, SUM(home_team_goal) FROM(
    SELECT * FROM Match
    JOIN Team
    ON Match.home_team_api_id = Team.team_api_id)
GROUP BY home_team_api_id
ORDER BY SUM(home_team_goal) DESC
"""

df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>2.Did this team also score the most points when playing away?<jupyter_code>query = """
SELECT away_team_api_id,team_long_name, SUM(away_team_goal) FROM(
    SELECT * FROM Match
    JOIN Team
    ON Match.away_team_api_id = Team.team_api_id)
GROUP BY home_team_api_id
ORDER BY SUM(home_team_goal) DESC
"""

df = pd.read_sql_query(query, conn)
df
# Yes!<jupyter_output><empty_output><jupyter_text>3. How many matches resulted in a tie?<jupyter_code>query = """
SELECT COUNT(*)
FROM Match
WHERE home_team_goal = away_team_goal
"""

df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>4. How many players have Smith for their last name? How many have 'smith' anywhere in their name?<jupyter_code>query = """
SELECT COUNT(*)
FROM Player
WHERE player_name LIKE '_smith%'
"""

df = pd.read_sql_query(query, conn)
df
query = """
SELECT COUNT(*)
FROM Player
WHERE player_name LIKE '%smith%'
"""

df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>5. What was the median tie score? Use the value determined in the previous question for the number of tie games. Hint: PostgreSQL does not have a median function. Instead, think about the steps required to calculate a median and use the WITH command to store stepwise results as a table and then operate on these results.<jupyter_code>query = """
WITH ordered_match AS (
  SELECT
      home_team_goal,
      row_number() OVER (ORDER BY home_team_goal) AS row_id,
      (SELECT COUNT(*) FROM Match) AS ct
  FROM Match
)

SELECT AVG(home_team_goal) AS median
FROM ordered_match
WHERE row_id BETWEEN ct/2.0 AND ct/2.0 + 1
"""

df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>6. What percentage of players prefer their left or right foot? Hint: Calculate either the right or left foot, whichever is easier based on how you setup the problem.<jupyter_code>query = """
SELECT DISTINCT preferred_foot,
        COUNT(player_fifa_api_id) OVER(PARTITION by preferred_foot) AS "count"
FROM Player_Attributes

"""

df = pd.read_sql_query(query, conn)
df
query = """
WITH stats AS (
    SELECT 
        DISTINCT preferred_foot,
        COUNT(player_fifa_api_id) OVER(PARTITION by preferred_foot) AS foot_count,
        (SELECT COUNT(*) FROM Player_Attributes) AS total
    FROM Player_Attributes
    )
    
SELECT preferred_foot,
       foot_count*1.0/total AS probability
FROM stats

"""

df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08852005532503458, 'fraction_numerical': 0.004495159059474412, 'mean_word_length': 5.014553014553014, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '199823', 'license': 'MIT', 'n_tokens_mistral': 1015, 'n_tokens_neox': 970, 'n_words': 296, 'path': 'SQL practice (project_3_starter)/soccer.ipynb', 'repo_name': 'katiehuang1221/onl_ds5_project_3', 'size': '2892'}