# Complete Optimization Problem and Solution: poker_player

## 1. Problem Context and Goals

### Context  
A poker tournament organizer is tasked with selecting a subset of poker players to maximize the total earnings of the selected players. The selection process must ensure diversity in nationalities, requiring that the chosen players represent at least three different countries. Additionally, the organizer aims to limit the number of selected players with low money ranks, ensuring that no more than 20% of the selected players have a money rank below 50.  

The decision to select a player is represented by a binary choice: each player is either selected (1) or not selected (0). The total earnings of the selected players are calculated by summing the earnings of each chosen player. The organizer uses the players' earnings as the primary metric to optimize, while the constraints on nationality diversity and money rank are enforced to maintain a balanced and competitive tournament lineup.  

The business logic for nationality diversity is defined by a configuration that ensures the selected players come from at least three distinct nationalities. This logic is applied as a constraint in the optimization process. Similarly, the money rank constraint is derived from the players' current money ranks, ensuring that the selection adheres to the 20% limit for low-ranked players.  

### Goals  
The primary goal of this optimization problem is to maximize the total earnings of the selected poker players. This is achieved by selecting a subset of players whose combined earnings are as high as possible, while adhering to the constraints on nationality diversity and money rank.  

Success is measured by the total earnings of the selected players, which directly corresponds to the sum of the earnings of each chosen player. The optimization process ensures that the selection meets the predefined constraints, resulting in a lineup that is both financially optimal and operationally feasible.  

## 2. Constraints  

1. **Nationality Diversity Constraint**: The selected players must represent at least three different nationalities. This ensures a diverse lineup that reflects the global nature of the tournament.  

2. **Money Rank Constraint**: No more than 20% of the selected players can have a money rank below 50. This ensures that the majority of the selected players are competitively ranked, maintaining the tournament's prestige and competitiveness.  

These constraints are designed to align with the business requirements and are expressed in a way that naturally leads to linear mathematical forms, ensuring the optimization problem remains linear and tractable.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added Nationality column to poker_player table to enforce diversity constraint. Updated business configuration logic to include nationality diversity formula.

CREATE TABLE poker_player (
  Earnings FLOAT,
  Money_Rank INTEGER,
  Selected BOOLEAN,
  Nationality STRING
);

CREATE TABLE player_selection (
  Player_ID INTEGER,
  Selected BOOLEAN
);
```

### Data Dictionary  
- **poker_player**: Stores information about poker players, including their earnings, money rank, selection status, and nationality.  
  - **Earnings**: The earnings of each poker player, used as coefficients in the objective function to maximize total earnings.  
  - **Money_Rank**: The money rank of each player, used to enforce the constraint limiting the number of low-ranked players.  
  - **Selected**: A binary indicator (true/false) representing whether the player is selected, serving as the decision variable in the optimization model.  
  - **Nationality**: The nationality of each player, used to enforce the diversity constraint requiring at least three distinct nationalities.  

- **player_selection**: Tracks the selection status of each player, linking to the poker_player table.  
  - **Player_ID**: A unique identifier for each player, linking to the poker_player table.  
  - **Selected**: A binary indicator (true/false) representing whether the player is selected, serving as the decision variable in the optimization model.  


### Retrieved Values

**Query 1: This data is essential for the optimization problem as it provides the coefficients for the objective function (Earnings) and the parameters for the constraints (Money_Rank, Nationality).**

```sql
SELECT Earnings, Money_Rank, Nationality, Selected FROM poker_player;
```

**Results (CSV format):**
```csv
Earnings,Money_Rank,Nationality,Selected
1200.0,45,USA,0
1500.0,55,Canada,0
2000.0,60,UK,0
1000.0,50,Australia,0
1800.0,58,Germany,0
```

**Query 2: This helps in understanding the potential diversity in the player pool, which is crucial for the nationality diversity constraint.**

```sql
SELECT COUNT(DISTINCT Nationality) AS Distinct_Nationalities FROM poker_player;
```

**Results (CSV format):**
```csv
Distinct_Nationalities
5
```

**Query 3: This data is important for the money rank constraint, ensuring that no more than 20% of the selected players have a money rank below 50.**

```sql
SELECT COUNT(*) AS Low_Money_Rank_Players FROM poker_player WHERE Money_Rank < 50;
```

**Results (CSV format):**
```csv
Low_Money_Rank_Players
1
```

**Query 4: This provides a baseline for understanding the maximum possible earnings that could be achieved if all players were selected.**

```sql
SELECT SUM(Earnings) AS Total_Earnings FROM poker_player;
```

**Results (CSV format):**
```csv
Total_Earnings
7500.0
```

**Query 5: This is directly related to the objective function, which aims to maximize the total earnings of the selected players.**

```sql
SELECT SUM(Earnings) AS Total_Selected_Earnings FROM poker_player WHERE Selected = TRUE;
```

**Results (CSV format):**
```csv
Total_Selected_Earnings
""
```

**Query 6: This helps in understanding the current selection size, which is necessary for calculating the 20% threshold for the money rank constraint.**

```sql
SELECT COUNT(*) AS Selected_Players FROM poker_player WHERE Selected = TRUE;
```

**Results (CSV format):**
```csv
Selected_Players
0
```

**Query 7: This is crucial for enforcing the money rank constraint, ensuring that no more than 20% of the selected players have a low money rank.**

```sql
SELECT COUNT(*) AS Selected_Low_Money_Rank_Players FROM poker_player WHERE Selected = TRUE AND Money_Rank < 50;
```

**Results (CSV format):**
```csv
Selected_Low_Money_Rank_Players
0
```

**Query 8: This helps in verifying the nationality diversity constraint, ensuring that the selected players represent at least three different nationalities.**

```sql
SELECT DISTINCT Nationality FROM poker_player WHERE Selected = TRUE;
```

**Results (CSV format):**
```csv
Nationality
```

**Query 9: This is directly related to the nationality diversity constraint, ensuring that the selected players come from at least three different countries.**

```sql
SELECT COUNT(DISTINCT Nationality) AS Selected_Distinct_Nationalities FROM poker_player WHERE Selected = TRUE;
```

**Results (CSV format):**
```csv
Selected_Distinct_Nationalities
0
```

**Query 10: This can be useful for identifying high-earning players who could significantly contribute to the total earnings if selected.**

```sql
SELECT Earnings, Money_Rank, Nationality, Selected FROM poker_player ORDER BY Earnings DESC LIMIT 10;
```

**Results (CSV format):**
```csv
Earnings,Money_Rank,Nationality,Selected
2000.0,60,UK,0
1800.0,58,Germany,0
1500.0,55,Canada,0
1200.0,45,USA,0
1000.0,50,Australia,0
```

**Query 11: This can help in identifying highly ranked players who are likely to be competitive, which is important for maintaining the tournament's prestige.**

```sql
SELECT Earnings, Money_Rank, Nationality, Selected FROM poker_player ORDER BY Money_Rank ASC LIMIT 10;
```

**Results (CSV format):**
```csv
Earnings,Money_Rank,Nationality,Selected
1200.0,45,USA,0
1000.0,50,Australia,0
1500.0,55,Canada,0
1800.0,58,Germany,0
2000.0,60,UK,0
```

**Query 12: This links the poker_player table with the player_selection table, providing a comprehensive view of player selection status.**

```sql
SELECT p.Player_ID, pp.Earnings, pp.Money_Rank, pp.Nationality, pp.Selected FROM player_selection p JOIN poker_player pp ON p.Player_ID = pp.Player_ID;
```

**Error:** Execution failed on sql 'SELECT p.Player_ID, pp.Earnings, pp.Money_Rank, pp.Nationality, pp.Selected FROM player_selection p JOIN poker_player pp ON p.Player_ID = pp.Player_ID;': no such column: pp.Player_ID

**Query 13: This provides context for the size of the player pool, which is useful for understanding the scale of the optimization problem.**

```sql
SELECT COUNT(*) AS Total_Players FROM poker_player;
```

**Results (CSV format):**
```csv
Total_Players
5
```

