def get_best_batting_strike_rate(self, team_name, venue, format, squad): team_id = self.__get_team_id(team_name) if team_id is None: return None sql = """WITH matches AS (SELECT id, date, teams FROM match WHERE %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20), batsmen AS (SELECT batsman_id, innings_number, runs_scored, balls_played, matches.date, matches.teams FROM batting_stats JOIN matches ON matches.id = match_id WHERE team_id = %s AND balls_played != 0) SELECT player.name as batsman, COUNT(innings_number) AS innings, SUM(runs_scored) AS runs, SUM(balls_played) AS balls, (SUM(runs_scored)*100/SUM(balls_played)) AS strike_rate FROM batsmen JOIN player ON player.id = batsman_id WHERE player.name IN %s GROUP BY player.name ORDER BY strike_rate DESC""" sql_venue = """WITH matches AS (SELECT id, date, teams FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), batsmen AS (SELECT batsman_id, innings_number, runs_scored, balls_played, matches.date, matches.teams FROM batting_stats JOIN matches ON matches.id = match_id WHERE team_id = %s AND balls_played != 0) SELECT player.name as batsman, COUNT(innings_number) AS innings, SUM(runs_scored) AS runs, SUM(balls_played) AS balls, (SUM(runs_scored)*100/SUM(balls_played)) AS strike_rate FROM batsmen JOIN player ON player.id = batsman_id WHERE player.name IN %s GROUP BY player.name ORDER BY strike_rate DESC""" self.cursor.execute(sql, (team_id, format, team_id, tuple(squad))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, format, venue, team_id, tuple(squad))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def get_most_50s(self, team_name, venue, format, squad): team_id = self.__get_team_id(team_name) if team_id is None: return None sql = """WITH matches AS (SELECT id, date, teams FROM match WHERE %s = ANY(teams) and format = %s ORDER BY date DESC LIMIT 20), batsmen AS (SELECT batsman_id, innings_number, runs_scored, balls_played, (runs_scored >= 50) AS is_fifty FROM batting_stats JOIN matches ON matches.id = match_id WHERE team_id = %s), fifty_batsmen AS (SELECT player.name as batsman, COUNT(innings_number) AS innings, SUM(runs_scored) AS runs, SUM(balls_played) AS balls, SUM(is_fifty::int) AS fifties FROM batsmen JOIN player ON player.id = batsman_id WHERE player.name IN %s GROUP BY player.name ORDER BY fifties DESC, runs DESC) SELECT * FROM fifty_batsmen WHERE fifties != 0""" sql_venue = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), batsmen AS (SELECT batsman_id, innings_number, runs_scored, balls_played, (runs_scored >= 50) AS is_fifty FROM batting_stats JOIN matches ON matches.id = match_id WHERE team_id = %s), fifty_batsmen AS (SELECT player.name as batsman, COUNT(innings_number) AS innings, SUM(runs_scored) AS runs, SUM(balls_played) AS balls, SUM(is_fifty::int) AS fifties FROM batsmen JOIN player ON player.id = batsman_id WHERE player.name IN %s GROUP BY player.name ORDER BY fifties DESC, runs DESC) SELECT * FROM fifty_batsmen WHERE fifties != 0""" self.cursor.execute(sql, (team_id, format, team_id, tuple(squad))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, format, venue, team_id, tuple(squad))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def get_runs_against_bowlers(self, team_name, venue, format, batsmen, opp_team, bowlers): sql = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20) SELECT bowler.name as bowler, COUNT(DISTINCT matches.id) as matches, SUM(balls) AS balls, SUM(runs) AS runs, SUM(wickets) AS wickets, (100*SUM(runs)/SUM(balls)) AS strike_rate FROM head_to_head_stats JOIN matches ON matches.id = match_id JOIN player AS batsman ON batsman.id = batsman_id JOIN player AS bowler ON bowler.id = bowler_id WHERE batsman.name IN %s AND bowler.name IN %s GROUP BY bowler.name ORDER BY wickets DESC, strike_rate""" sql_venue = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20) SELECT bowler.name as bowler, COUNT(DISTINCT matches.id) AS matches, SUM(balls) AS balls, SUM(runs) AS runs, SUM(wickets) AS wickets, (100*SUM(runs)/SUM(balls)) AS strike_rate FROM head_to_head_stats JOIN matches ON matches.id = match_id JOIN player AS batsman ON batsman.id = batsman_id JOIN player AS bowler ON bowler.id = bowler_id WHERE batsman.name IN %s AND bowler.name IN %s GROUP BY bowler.name ORDER BY wickets DESC, strike_rate""" team_id = self.__get_team_id(team_name) opp_team_id = self.__get_team_id(opp_team) if team_id is None or opp_team_id is None: return None self.cursor.execute( sql, (team_id, opp_team_id, format, tuple(batsmen), tuple(bowlers))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, opp_team_id, format, venue, tuple(batsmen), tuple(bowlers))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def __get_player_data(self, id): sql = """SELECT * FROM player WHERE player.id = %s""" self.cursor.execute(sql, (id,)) if self.cursor.rowcount > 0: results = Common.extract_query_results(self.cursor) return results[0] return None
def __get_team_id(self, team_name): sql = """SELECT id FROM team WHERE name = %s""" self.cursor.execute(sql, (team_name, )) query_results = Common.extract_query_results(self.cursor) if len(query_results) != 0: return query_results[0]['id'] return None
def get_most_maidens(self, team_name, venue, format, squad): team_id = self.__get_team_id(team_name) if team_id is None: return None sql = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, innings_number, wickets_taken, maidens FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s) SELECT player.name, COUNT(innings_number) AS innings, SUM(wickets_taken) AS wickets , SUM(maidens) AS maidens FROM bowlers JOIN player ON player.id = bowler_id WHERE player.name IN %s GROUP BY player.name ORDER BY maidens DESC""" sql_venue = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, innings_number, wickets_taken, maidens FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s) SELECT player.name, COUNT(innings_number) AS innings, SUM(wickets_taken) AS wickets , SUM(maidens) AS maidens FROM bowlers JOIN player ON player.id = bowler_id WHERE player.name IN %s GROUP BY player.name ORDER BY maidens DESC""" self.cursor.execute(sql, (team_id, format, team_id, tuple(squad))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, format, venue, team_id, tuple(squad))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def get_best_bowling_figure_in_innings(self, team_name, venue, format, squad): sql = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, wickets_taken, overs_bowled, runs_given FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s) SELECT player.name as bowler, wickets_taken AS wickets, runs_given AS runs, overs_bowled::text AS overs FROM bowlers JOIN player ON player.id = bowler_id WHERE player.name IN %s ORDER BY wickets DESC, runs LIMIT 20""" sql_venue = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, wickets_taken, overs_bowled, runs_given FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s) SELECT player.name as bowler, wickets_taken AS wickets, runs_given AS runs, overs_bowled::text AS overs FROM bowlers JOIN player ON player.id = bowler_id WHERE player.name IN %s ORDER BY wickets DESC, runs LIMIT 20""" team_id = self.__get_team_id(team_name) if team_id is None: return None self.cursor.execute(sql, (team_id, format, team_id, tuple(squad))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, format, venue, team_id, tuple(squad))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def get_recent_match_scores(self, team_name, format, venue): sql = """WITH matches AS (SELECT id, date, venue, outcome, winning_team_id FROM match WHERE %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20), matches_scores AS (SELECT innings_stats.match_id, matches.date, matches.venue, matches.winning_team_id, matches.outcome, innings_stats.innings_number,team.name || '@' || runs || '-' || wickets || ' (' || overs || ')' AS score FROM innings_stats JOIN matches on matches.id = innings_stats.match_id JOIN team on team.id = innings_stats.batting_team_id ORDER BY date DESC, innings_number) SELECT matches_scores.date::text AS match_date, matches_scores.venue as match_venue, matches_scores.winning_team_id AS winning_team_id, matches_scores.outcome as match_outcome, string_agg(matches_scores.score, ', ') AS match_score FROM matches_scores GROUP BY matches_scores.date, matches_scores.venue, matches_scores.winning_team_id, matches_scores.outcome ORDER BY matches_scores.date DESC""" sql_venue = """WITH matches AS (SELECT id, date, venue, outcome, winning_team_id FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), matches_scores AS (SELECT innings_stats.match_id, matches.date, matches.venue, matches.winning_team_id, matches.outcome, innings_stats.innings_number,team.name || '@' || runs || '-' || wickets || ' (' || overs || ')' AS score FROM innings_stats JOIN matches on matches.id = innings_stats.match_id JOIN team on team.id = innings_stats.batting_team_id ORDER BY date DESC, innings_number) SELECT matches_scores.date::text AS match_date, matches_scores.venue as match_venue, matches_scores.winning_team_id AS winning_team_id, matches_scores.outcome as match_outcome, string_agg(matches_scores.score, ', ') AS match_score FROM matches_scores GROUP BY matches_scores.date, matches_scores.venue, matches_scores.winning_team_id, matches_scores.outcome ORDER BY matches_scores.date DESC""" team_id = self.__get_team_id(team_name) if team_id is None: return None self.cursor.execute(sql, (team_id, format)) results = self.__process_match_scores( team_id, Common.extract_query_results(self.cursor)) self.cursor.execute(sql_venue, (team_id, format, venue)) results_venue = self.__process_match_scores( team_id, Common.extract_query_results(self.cursor)) print(results) return {"overall": results, 'atVenue': results_venue}
def get_most_5_plus_wickets(self, team_name, venue, format, squad): team_id = self.__get_team_id(team_name) if team_id is None: return None sql = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, innings_number, wickets_taken, economy, (wickets_taken >= 5) AS is_five_plus FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s), five_plus_bowlers AS (SELECT player.name as bowler, COUNT(innings_number) AS innings, SUM(wickets_taken) AS wickets , ROUND(AVG(economy),1)::text as economy, SUM(is_five_plus::int) as five_plus FROM bowlers JOIN player ON player.id = bowler_id WHERE player.name IN %s GROUP BY player.name ORDER BY five_plus DESC) SELECT * FROM five_plus_bowlers WHERE five_plus != 0 """ sql_venue = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, innings_number, wickets_taken, economy, (wickets_taken >= 5) AS is_five_plus FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s), five_plus_bowlers AS (SELECT player.name as bowler, COUNT(innings_number) AS innings, SUM(wickets_taken) AS wickets , ROUND(AVG(economy),1)::text as economy, SUM(is_five_plus::int) as five_plus FROM bowlers JOIN player ON player.id = bowler_id WHERE player.name IN %s GROUP BY player.name ORDER BY five_plus DESC) SELECT * FROM five_plus_bowlers WHERE five_plus != 0""" self.cursor.execute(sql, (team_id, format, team_id, tuple(squad))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, format, venue, team_id, tuple(squad))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def get_best_bowling_strike_rate(self, team_name, venue, format, squad): team_id = self.__get_team_id(team_name) if team_id is None: return None sql = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, innings_number, wickets_taken, balls_bowled, economy FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s), players AS (SELECT player_id, COUNT(innings_number) AS innings, SUM(wickets_taken) AS wickets , ROUND(AVG(economy),1) as economy, SUM(balls_bowled) as balls FROM bowlers WHERE wickets != 0) SELECT player.name as bowler, innings, wickets, economy, (balls/wickets) AS strike_rate FROM players JOIN player ON player.id = player_id WHERE player.name IN %s GROUP BY player.name ORDER BY strike_rate""" sql_venue = """WITH matches AS (SELECT id FROM match WHERE %s = ANY(teams) AND format = %s AND venue = %s ORDER BY date DESC LIMIT 20), bowlers AS (SELECT bowler_id, innings_number, wickets_taken, balls_bowled, economy FROM bowling_stats JOIN matches ON matches.id = match_id WHERE team_id = %s), players AS (SELECT player_id, COUNT(innings_number) AS innings, SUM(wickets_taken) AS wickets , ROUND(AVG(economy),1) as economy, SUM(balls_bowled) as balls FROM bowlers WHERE wickets != 0) SELECT player.name as bowler, innings, wickets, economy, (balls/wickets) AS strike_rate FROM players JOIN player ON player.id = player_id WHERE player.name IN %s GROUP BY player.name ORDER BY strike_rate""" self.cursor.execute(sql, (team_id, format, team_id, tuple(squad))) results = Common.extract_query_results(self.cursor) self.cursor.execute(sql_venue, (team_id, format, venue, team_id, tuple(squad))) results_venue = Common.extract_query_results(self.cursor) return {"overall": results, "atVenue": results_venue}
def batting_stats_overall(self, batsman_name, format, num_of_matches): # TODO In TEST stats, we should consider showing latest match second innings_scores as latest score sql = """select batting_stats.runs_scored, batting_stats.balls_played from batting_stats join player on player.id = batting_stats.batsman_id join match on match.id = batting_stats.match_id where player.name = %s and match.format = %s order by match.date desc limit %s""" self.cursor.execute(sql, (batsman_name, format, num_of_matches)) query_results = Common.extract_query_results(self.cursor) return query_results
def bowling_stats_overall(self, bowler_name, format, num_of_matches): # TODO In TEST stats, we should consider showing latest match second innings_scores as latest score sql = """select bowling_stats.wickets_taken, bowling_stats.runs_given, bowling_stats.overs_bowled, bowling_stats.economy from bowling_stats join player on player.id = bowling_stats.bowler_id join match on match.id = bowling_stats.match_id where player.name = %s and match.format = %s order by match.date desc limit %s""" self.cursor.execute(sql, (bowler_name, format, num_of_matches)) query_results = Common.extract_query_results(self.cursor) return query_results
def bowling_stats_against_batting_style(self, bowler_name, batting_style, format, num_of_matches): sql = """select head_to_head_stats.runs, head_to_head_stats.balls, head_to_head_stats.wickets from head_to_head_stats join player as bowler on bowler.id = head_to_head_stats.bowler_id join player as batsman on batsman.id = head_to_head_stats.batsman_id join match on match.id = head_to_head_stats.match_id where bowler.name = %s and batsman.batting_style = %s and match.format = %s order by match.date desc limit %s""" self.cursor.execute( sql, (bowler_name, batting_style, format, num_of_matches)) query_results = Common.extract_query_results(self.cursor) return query_results
def __get_team_matches_list(self, team_id, format): sql = """SELECT * FROM match WHERE outcome != 'NO RESULT' and %s = ANY(teams) and format = %s ORDER BY date desc LIMIT %s""" self.cursor.execute(sql, (team_id, format, 20)) return Common.extract_query_results(self.cursor)
def __get_team_short_name_from_id(self, team_id): sql = """SELECT short_name FROM team WHERE id = %s""" self.cursor.execute(sql, (team_id, )) query_results = Common.extract_query_results(self.cursor) return query_results[0]['short_name']