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']