IPL Auction Assignment

Motive

Developing auction strategy for new IPL franchise by analyzing past IPL data to create a strong and balanced squad

Background story

Indian Premier League (IPL) is a professional Twenty20 cricket league in India contested during March or April and May of every year by eight teams representing eight different cities or states in India. The league was founded by the Board of Control for Cricket in India (BCCI) in 2007.

The IPL is the most-attended cricket league in the world and in 2014 ranked sixth by average attendance among all sports leagues. In 2010, the IPL became the first sporting event in the world to be broadcast live on YouTube. The brand value of IPL in 2020 was estimated to be around ?475 billion (US$6.7 billion), according to Duff & Phelps.

The IPL tournament involves each team playing every other team twice in a home-and-away, double round-robin format. At the conclusion of the double round-robin league, on the basis of aggregate points, the top four teams qualify for the playoffs. In this stage, the top two teams compete with each other (in a match titled "Qualifier 1"), as do the remaining two teams (in a match titled "Eliminator").

While the winner of Qualifier 1 directly qualifies for the final match, the losing team gets another chance to qualify for the final match by playing the winning team of the Eliminator match; this match is titled Qualifier 2. The winner of this subsequent Qualifier 2 match moves onto the final match. The team that wins the final match is crowned the Indian Premier League champion.

Altogether, thirteen teams have played in the past ten seasons of the IPL tournament.] Of these, five teams are no longer a part of the tournament. In 2011, BCCI terminated the franchise of Kochi Tuskers Kerala for contractual breach Similarly, BCCI terminated the franchise of Deccan Chargers in 2012 for backing off from its commitments. In 2013, Pune Warriors India exited IPL after it had a franchise valuation disagreement with BCCI. Chennai Super Kings and Rajasthan Royals were suspended in 2015 & 2016 following a betting controversy. However, in July 2017, BCCI announced that both Chennai Super Kings and Rajasthan Royals would be allowed back into the IPL competition from the 2018 season. The tournament featured eight teams for the 2020 season, including Delhi Capitals. Punjab Kings, Kolkata Knight Riders, Royal Challengers Bangalore, Rajasthan Royals, Chennai Super Kings, Sunrisers Hyderabad and Mumbai Indians.

Mumbai Indians have won five titles. Chennai Super Kings have won four titles and Kolkata Knight Riders have won two titles, Sunrisers Hyderabad, Gujarat Titans and Rajasthan Royals, apart from former team Deccan Chargers, are the other teams to have won the tournament title.

In a coming season a new team is being added to the Indian Premier League (IPL) and a mega auction is being held to build the team's squad, there are a few factors that the team's management and auction strategy would likely consider:

1. Budget: The team would need to allocate a budget for the auction and decide how much money to spend on each player.
2. Team needs: The team would need to identify the positions and types of players they need to fill out their squad and target those players in the auction.
3. Player availability: The team would need to assess the availability of players, including their current contracts with other teams and their international commitments.
4. Player form: The team would need to consider the recent form and performances of the players they are targeting.
5. Player value: The team would need to consider the market value of the players they are interested in and decide how much they are willing to pay for them.

To build a strong and balanced squad, the team's management would likely need to strike a balance between all of these factors and come up with a well-thought-out auction strategy. This could involve targeting a few key players they consider to be game-changers, while also filling out the rest of the squad with more affordable players who can add value to the team. It could also involve making use of the various player retention and right to match options available to them.

As the data analysis team leader of this new team, your first step would be to gather and analyze all available data on the players who are eligible for selection in the auction. This could include data on their past performance in domestic and international cricket, their current form and fitness, and any other relevant metrics such as fielding ability, bowling economy, and strike rate.

Once you have gathered and analyzed this data, You can use it to create player profiles and rankings for all of the eligible players. This would involve assigning a value or rating to each player based on their performance and potential impact on the team.

Using these player profiles and rankings, You can work with the team's management to create a list of target players for the auction. This list would include players who we believe would be the best value for money and who would fill the specific needs of our team.

Finally, You would work with the team's auction strategy group to come up with a plan for the auction itself. This would involve deciding on the order in which we want to bid for players, setting limits on the amount we are willing to pay for each player, and making use of any player retention or right to match options we may have.

DATA

You are provided with the ipl data from the first season to the 13th season which was held in 2020. Read and analyze the data properly before loading it into your sql server.

Bidding on batters

In T20 cricket, it is important for a team to have a balance of different types of batsmen in order to be successful in big tournaments.

1. Aggressive batsmen: These batsmen play aggressively and look to score runs quickly by taking risks and hitting shots all around the ground. They are important because they can provide a fast start to the team's innings and put pressure on the opposition's bowlers. This can set the tone for the rest of the innings and give the team a good platform to build on.
2. Anchor batsmen: These batsmen play a more defensive role, looking to hold one end up and build a solid foundation for the team's innings. They are important because they can provide stability to the team's innings and allow the other batsmen to play their natural game. They also play a crucial role in building partnerships and helping the team score consistently throughout the innings.
3. Finishers: These batsmen come in to bat towards the end of the innings and look to hit big shots to help the team achieve a challenging total or chase down a target. They are crucial in the closing stages of the game because they have the ability to score runs quickly and take the pressure off the other batsmen.
4. Big-hitters: These batsmen have the ability to hit the ball a long way and are capable of scoring runs quickly. They are valuable in the middle overs of the game because they have the ability to take the attack to the opposition's bowlers and put pressure on them.
5. Rotators of strike: These batsmen are good at finding gaps in the field and rotating the strike to keep the scoreboard ticking over. They are important because they can keep the run rate up and ensure that the team is able to maintain a good scoring rate throughout the innings. They also play a crucial role in building partnerships and helping the team score consistently.

There are several strategies that an IPL team could use to try and acquire a batsman during the auction:

1. Target a specific player: If a team has identified a specific batsman that they want to target, they could focus their efforts on bidding for that player and try to secure his services.
2. Bid aggressively: If a team is keen to acquire a batsman, they could choose to bid aggressively and try to outbid other teams for the player's services.
3. Use the Right to Match (RTM) card: If a batsman that a team is interested in is bought by another team, the team could use their Right to Match (RTM) card to match the other team's bid and retain the player's services.
4. Build a strong squad and use it as a selling point: If a team has already built a strong squad and is looking to add another batsman to it, they could use this as a selling point to try and convince the player to join their team.
5. Look for value picks: Instead of targeting the most expensive and high-profile aggressive batsmen, a team could look for value picks and try to acquire an aggressive batsman who is available at a lower price.

Overall, the key to acquiring a batsman during the IPL auction is to have a clear strategy and be prepared to bid aggressively if necessary.

Your first priority is to get 2-3 players with high S.R who have faced at least 500 balls.And to do that you have to make a list of 10 players you want to bid in the auction so that when you try to grab them in auction you should not pay the amount greater than you have in the purse for a particular player.

(strike rate is total runs scored by batsman divided by number of balls faced but remember when extras_type is 'wides' it is not counted as a ball faced neither counted as batsmen runs)

After getting some Aggressive batters you need a few anchor batsmen in your team. In T20 cricket, players with a good average are important for a number of reasons:

1. Consistency: Players with a good average are generally more consistent performers, which is crucial in a format where a single player can have a big impact on the outcome of the game.
2. Ability to score runs quickly: Players with a good average are often capable of scoring runs quickly, which is important in a format where teams are looking to score as many runs as possible in a limited amount of time.
3. Ability to build partnerships: Players with a good average are also generally good at building partnerships and helping the team score consistently throughout the innings.
4. Ability to absorb pressure: Players with a good average are often able to absorb pressure and perform well under challenging circumstances, which is important in a format where there is always a lot of pressure on the batsmen.

Overall, players with a good average are valuable assets to any T20 team because of their consistency, ability to score runs quickly, and ability to absorb pressure.

Now you need to get 2-3 players with good Average who have played more than 2 ipl seasons. And to do that you have to make a list of 10 players you want to bid in the auction so that when you try to grab them in auction you should not pay the amount greater than you have in the purse for a particular player.

(Average is calculated as total runs scored divided by number of times batsman has been dismissed which can be calculated using wicket_ball field as 1 indicates out and 0 indicates not out, a batsman should’ve been dismissed at least once to calculate the sr i.e., you can exclude those players who have not been dismissed once )

After getting some Aggressive batsmen and anchor batsmen you need a few Hard hitters in your team. Hard-hitters are important in T20 cricket because they have the ability to score runs quickly and take the attack to the opposition's bowlers. Some of the key ways in which hard-hitters can impact a T20 game are:

1. Change the momentum of the game: Hard-hitters have the ability to hit the ball a long way and score runs quickly, which can help to change the momentum of the game in their team's favor.
2. Apply pressure on the opposition: Hard-hitters can put pressure on the opposition's bowlers by scoring runs quickly and forcing them to bowl to their strengths.
3. Score big runs in the death overs: Hard-hitters are particularly valuable in the death overs, when teams are looking to score as many runs as possible. They have the ability to hit big shots and help the team post a challenging total or chase down a target.
4. Create excitement: Hard-hitters can also create excitement among fans and make T20 cricket more entertaining to watch.

Overall, hard-hitters are important in T20 cricket because of their ability to score runs quickly, apply pressure on the opposition, and create excitement.

Now you need to get 2-3 Hard-hitting players who have scored most runs in boundaries and have played more the 2 ipl season. To do that you have to make a list of 10 players you want to bid in the auction so that when you try to grab them in auction you should not pay the amount greater than you have in the purse for a particular player.

(only 4 and 6 will be counted as boundaries so calculate how many 4 and 6 has been hit by each batsman and also calculate total runs scored to get the output as boundary percentage which will be runs in boundary divided by total runs scored)

Bidding on Bowlers

After making a list of batsmans we want to bid in the auction now we need to find some good bowlers as well.

Bowlers are important in an IPL team for several reasons some of them are:

1. Wicket-taking ability: Good bowlers have the ability to take wickets and break partnerships, which can be crucial in a format where a single player can have a big impact on the outcome of the game. Taking wickets helps to put pressure on the opposition and disrupt their batting lineup, making it easier for the team to defend a low total or chase down a target.
2. Control: Good bowlers are able to maintain a good level of control over their deliveries, which allows them to keep the scoring rate in check and prevent the opposition from scoring too many runs. This is important because it gives the team a better chance of winning the game by limiting the opposition's scoring opportunities.
3. Ability to bowl in different situations: Good bowlers have the ability to bowl effectively in different situations, whether it be at the start of the innings, in the middle overs, or in the death overs. This is important because it gives the team flexibility in terms of their bowling options and allows them to choose the best bowlers for the specific situation.
4. Ability to bowl in different conditions: Good bowlers are also able to adapt to different conditions and bowl effectively in a range of environments. This is important because the IPL is played in different venues across the country, each of which has its own unique pitch and weather conditions. Bowlers who are able to adapt to these conditions are more valuable to the team.

Economical bowlers are important in T20 cricket because they have the ability to restrict the opposition's scoring and put pressure on the batsmen. Some of the key ways in which economical bowlers can impact a T20 game are:

1. Control the run rate: Economical bowlers are able to maintain a good level of control over their deliveries and limit the number of runs that are scored off them. This helps to keep the run rate in check and makes it more difficult for the opposition to score runs quickly.
2. Force the batsmen to take risks: Economical bowlers can also force the batsmen to take risks in order to score runs, which can lead to wickets as they try to hit out.
3. Create pressure on the opposition: Economical bowlers are able to create pressure on the opposition by making it difficult for them to score runs. This can lead to mistakes and wickets as the batsmen try to score too quickly.
4. Allow the team to defend a low total: Economical bowlers can also be crucial in helping a team defend a low total, as they are able to restrict the opposition's scoring and make it more difficult for them to chase down the target.

Overall, economical bowlers are important in T20 cricket because of their ability to control the run rate, force the batsmen to take risks, create pressure on the opposition, and help the team defend a low total.Talking about the bowlers tally In cricket, a bowler's tally includes all of the deliveries that they have bowled, regardless of whether they have resulted in a wicket or not.

This includes legitimate balls (which are bowled in accordance with the rules of the game), as well as no balls (which are illegal deliveries) and wides (which are balls that are too wide or high to be hit by the batsman) Therefore, a bowler's tally includes all of the balls that they have bowled, whether they have been hit for runs or not, and whether they have resulted in a wicket or not. It is a measure of the bowler's overall performance and gives an indication of how many deliveries they have bowled in their career.

Your first priority is to get 2-3 bowlers with good economy who have bowled at least 500 balls in IPL so far.To do that you have to make a list of 10 players you want to bid in the auction so that when you try to grab them in auction you should not pay the amount greater than you have in the purse for a particular player.(economy can be calculated by dividing total runs conceded with total overs bowled)

After adding Economical bowlers into your team you need to find some wicket taking bowlers.

Wicket-taking bowlers are important in the IPL for several reasons some of them are:

1. Ability to break partnerships: Wicket-taking bowlers have the ability to take wickets and break partnerships, which can be crucial in a format where a single player can have a big impact on the outcome of the game. Taking wickets helps to put pressure on the opposition and disrupt their batting lineup, making it easier for the team to defend a low total or chase down a target.
2. Ability to create pressure: Wicket-taking bowlers are also able to create pressure on the opposition by making it difficult for them to score runs. This can lead to mistakes and wickets as the batsmen try to score too quickly. This is because wicket-taking bowlers have the ability to take wickets at regular intervals, which puts pressure on the batsmen to score runs and can lead to mistakes.
3. Contribution to team's success: Wicket-taking bowlers are an important contributor to a team's success in the IPL, as they have the ability to turn a match in the team's favor by taking wickets at crucial times. For example, if a team is defending a low total, wicket-taking bowlers can take wickets at regular intervals to put pressure on the opposition and make it more difficult for them to chase down the target. Similarly, if a team is chasing a target, wicket-taking bowlers can take wickets to disrupt the opposition's batting lineup and make it easier for the team to chase down the target.
4. Role in building a strong team: Wicket-taking bowlers are also an important part of building a strong team in the IPL, as they provide the team with a valuable wicket-taking option and help to balance the team's attack. In order to be successful in the IPL, teams need to have a good mix of bowlers who can take wickets, as well as bowlers who can maintain a good level of control and restrict the opposition's scoring. Wicket-taking bowlers are an important part of this mix, as they provide the team with a wicket-taking option that can be used to put pressure on the opposition and disrupt their batting lineup.

Now you need to get 2-3 bowlers with the best strike rate and who have bowled at least 500 balls in IPL so far.To do that you have to make a list of 10 players you want to bid in the auction so that when you try to grab them in auction you should not pay the amount greater than you have in the purse for a particular player.

(strike rate of a bowler can be calculated by number of balls bowled divided by total wickets taken )

All-Rounders

After getting the best bowlers and batters you need few all rounders in your team. In T20 cricket, all-rounders are players who are skilled at both batting and bowling. They are valuable to a team because they provide flexibility to the lineup and can contribute in multiple ways.

One of the main ways that all-rounders can contribute in T20 cricket is by scoring runs quickly and effectively. In T20 cricket, teams often need to score runs at a fast pace, and all-rounders who can hit the ball hard and rotate the strike are valuable assets. They can also provide a steadying influence if the team is under pressure, as they have the experience and skills to build an innings.

All-rounders are also valuable because they can bowl effectively in different situations. In T20 cricket, teams often have to make quick adjustments to their game plan, and all-rounders who can bowl a variety of deliveries and execute their skills under pressure can be very useful. They can bowl in the powerplay, in the middle overs, or at the death, and they can be used to exploit the opposition's weaknesses or contain the scoring.

All-rounders can also provide insurance for a team in case of injuries or poor form by other players. For example, if a team's main fast bowler is injured, an all-rounder who can bowl at pace can help fill the void. Similarly, if a team's top order batsmen are struggling, an all-rounder who can score runs and provide stability to the innings can be valuable.

Overall, all-rounders are valuable in T20 cricket because they provide teams with versatility and options, and they can be key contributors to a team's success in the format.

Now you need to get 2-3 All_rounders with the best batting as well as bowling strike rate and who have faced at least 500 balls in IPL so far and have bowled minimum 300 balls.To do that you have to make a list of 10 players you want to bid in the auction so that when you try to grab them in auction you should not pay the amount greater than you have in the purse for a particular player.

( strike rate of an all rounder can be calculated using the same criteria of batsman similarly the bowling strike rate can be calculated using the criteria of a bowler)

Wicketkeeper

Now, after adding all these players, this team is still incomplete because we don’t have any wicketkeeper. In T20 cricket, the role of the wicketkeeper is slightly different from that in longer forms of the game. In T20, the emphasis is on scoring runs quickly, so the wicketkeeper is often expected to contribute to the team's run total with aggressive batting. They may also be called upon to bowl a few overs of spin or medium pace.

Defensively, the wicketkeeper still has an important role to play, as they are responsible for catching and stumping the batsman. In T20 cricket, where every run and wicket is crucial, a good wicket keeper can make a big difference to the team's chances of winning.

Overall, the wicketkeeper is an important member of a T20 team and can have a significant impact on the outcome of a match. Now suppose you are provided with a list of wicketkeeper names, and your task is to define the criteria for choosing the two best wicket keepers from all those names.Try to define a criteria best suited for a wicketkeeper required in a t20 team so that your team will be complete and have everything required for winning this year’s IPL.

After doing all that you have the list of all the players you are going to bid in the auction so create a visual representation in the form of graphs , tables and charts to present in front of team management before the auction.

Additional Questions for Final Assessment

NOTE:-Deliveries is the table created using the IPL_Ball data whereas the Matches table has been created using the IPL_Matches data

1. Get the count of cities that have hosted an IPL match
2. Create table deliveries_v02 with all the columns of the table ‘deliveries’ and an additional column ball_result containing values boundary, dot or other depending on the total_run (boundary for >= 4, dot for 0 and other for any other number)
   (Hint 1 : CASE WHEN statement is used to get condition based results)
   (Hint 2: To convert the output data of the select statement into a table, you can use a subquery. Create table table_name as [entire select statement].
3. Write a query to fetch the total number of boundaries and dot balls from the deliveries_v02 table.
4. Write a query to fetch the total number of boundaries scored by each team from the deliveries_v02 table and order it in descending order of the number of boundaries scored.
5. Write a query to fetch the total number of dot balls bowled by each team and order it in descending order of the total number of dot balls bowled.
6. Write a query to fetch the total number of dismissals by dismissal kinds where dismissal kind is not NA
7. Write a query to get the top 5 bowlers who conceded maximum extra runs from the deliveries table
8. Write a query to create a table named deliveries_v03 with all the columns of deliveries_v02 table and two additional column (named venue and match_date) of venue and date from table matches
9. Write a query to fetch the total runs scored for each venue and order it in the descending order of total runs scored.
10. Write a query to fetch the year-wise total runs scored at Eden Gardens and order it in the descending order of total runs scored.

NOTE: You need to share the create table queries as well.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank