# Complete Optimization Problem and Solution: voter_1

## 1. Problem Context and Goals

### Context  
In this voting competition, contestants aim to maximize their votes across different states by strategically allocating a limited promotional budget. The decision-making process involves determining how much promotional budget to allocate to each contestant in each state, ensuring that the total spending does not exceed the available budget and that state-specific spending limits are respected. The promotional budget directly influences the number of votes received, with a fixed conversion rate that translates budget allocation into votes. The total available budget for promotional activities is set at 150,000 units, and the maximum promotional budget allowed per state is capped at 25,000 units. The conversion rate of promotional budget to votes is 0.6, meaning that every unit of promotional budget spent in a state generates 0.6 votes for the contestant. The goal is to allocate the budget in a way that maximizes the total votes received across all contestants and states, while adhering to these operational constraints.

### Goals  
The primary goal of this optimization problem is to maximize the total number of votes received by all contestants across all states. This is achieved by determining the optimal allocation of the promotional budget to each contestant in each state, ensuring that the total spending does not exceed the available budget and that state-specific spending limits are respected. Success is measured by the total votes received, which is directly influenced by the promotional budget allocation and the fixed conversion rate. The optimization process ensures that the budget is allocated efficiently and fairly, maximizing the overall impact of the promotional activities.

## 2. Constraints    

The optimization problem is subject to the following constraints:  
1. **Total Budget Constraint**: The sum of the promotional budgets allocated to all contestants across all states must not exceed the total available budget of 150,000 units. This ensures that the overall spending remains within the financial limits of the competition.  
2. **State-Level Budget Constraint**: The promotional budget allocated to any contestant in a single state must not exceed the maximum allowed budget per state, which is set at 25,000 units. This ensures that no single state receives an excessive share of the promotional resources, promoting fairness and balance in the competition.  

These constraints ensure that the promotional budget is allocated in a way that respects both the overall financial limits and the state-specific spending caps, leading to a fair and efficient distribution of resources.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraints, moving scalar parameters to business_configuration_logic.json, and updating the data dictionary to reflect optimization mappings.

CREATE TABLE PROMOTIONAL_BUDGET (
  contestant_number INTEGER,
  state STRING,
  budget FLOAT
);

CREATE TABLE VOTES_RECEIVED (
  contestant_number INTEGER,
  state STRING,
  votes INTEGER
);


```

### Data Dictionary  
- **PROMOTIONAL_BUDGET**: This table contains the promotional budget allocated to each contestant in each state.  
  - **contestant_number**: A unique identifier for each contestant.  
  - **state**: The state where the promotional budget is allocated.  
  - **budget**: The amount of promotional budget allocated to the contestant in the specified state.  

- **VOTES_RECEIVED**: This table contains the number of votes received by each contestant in each state.  
  - **contestant_number**: A unique identifier for each contestant.  
  - **state**: The state where the votes were received.  
  - **votes**: The number of votes received by the contestant in the specified state.  


### Retrieved Values

**Query 1: This is important to ensure that the total budget constraint (150,000 units) is not exceeded.**

```sql
SELECT SUM(budget) AS total_budget FROM PROMOTIONAL_BUDGET;
```

**Results (CSV format):**
```csv
total_budget
130000.0
```

**Query 2: This is important to ensure that the state-level budget constraint (25,000 units per state) is respected.**

```sql
SELECT state, SUM(budget) AS state_budget FROM PROMOTIONAL_BUDGET GROUP BY state;
```

**Results (CSV format):**
```csv
state,state_budget
CA,47000.0
NY,50000.0
TX,33000.0
```

**Query 3: This is important to understand the current allocation and to identify any potential over-allocations.**

```sql
SELECT contestant_number, state, budget FROM PROMOTIONAL_BUDGET;
```

**Results (CSV format):**
```csv
contestant_number,state,budget
1,CA,15000.0
1,TX,10000.0
1,NY,20000.0
2,CA,12000.0
2,TX,15000.0
2,NY,18000.0
3,CA,20000.0
3,TX,8000.0
3,NY,12000.0
```

**Query 4: This is important to measure the success of the promotional budget allocation in terms of votes.**

```sql
SELECT SUM(votes) AS total_votes FROM VOTES_RECEIVED;
```

**Results (CSV format):**
```csv
total_votes
65000
```

**Query 5: This is important to understand the impact of the promotional budget on votes at a granular level.**

```sql
SELECT contestant_number, state, votes FROM VOTES_RECEIVED;
```

**Results (CSV format):**
```csv
contestant_number,state,votes
1,CA,7500
1,TX,5000
1,NY,10000
2,CA,6000
2,TX,7500
2,NY,9000
3,CA,10000
3,TX,4000
3,NY,6000
```

**Query 6: This is important to analyze the relationship between budget allocation and votes received.**

```sql
SELECT pb.contestant_number, pb.state, pb.budget, vr.votes FROM PROMOTIONAL_BUDGET pb JOIN VOTES_RECEIVED vr ON pb.contestant_number = vr.contestant_number AND pb.state = vr.state;
```

**Results (CSV format):**
```csv
contestant_number,state,budget,votes
1,CA,15000.0,7500
1,TX,10000.0,5000
1,NY,20000.0,10000
2,CA,12000.0,6000
2,TX,15000.0,7500
2,NY,18000.0,9000
3,CA,20000.0,10000
3,TX,8000.0,4000
3,NY,12000.0,6000
```

**Query 7: This is important to analyze the efficiency of budget allocation at the state level.**

```sql
SELECT pb.state, SUM(pb.budget) AS total_state_budget, SUM(vr.votes) AS total_state_votes FROM PROMOTIONAL_BUDGET pb JOIN VOTES_RECEIVED vr ON pb.contestant_number = vr.contestant_number AND pb.state = vr.state GROUP BY pb.state;
```

**Results (CSV format):**
```csv
state,total_state_budget,total_state_votes
CA,47000.0,23500
NY,50000.0,25000
TX,33000.0,16500
```

**Query 8: This is important to analyze the efficiency of budget allocation at the contestant level.**

```sql
SELECT pb.contestant_number, SUM(pb.budget) AS total_contestant_budget, SUM(vr.votes) AS total_contestant_votes FROM PROMOTIONAL_BUDGET pb JOIN VOTES_RECEIVED vr ON pb.contestant_number = vr.contestant_number AND pb.state = vr.state GROUP BY pb.contestant_number;
```

**Results (CSV format):**
```csv
contestant_number,total_contestant_budget,total_contestant_votes
1,45000.0,22500
2,45000.0,22500
3,40000.0,20000
```

**Query 9: This is important to identify any violations of the state-level budget constraint.**

```sql
SELECT state, SUM(budget) AS state_budget FROM PROMOTIONAL_BUDGET GROUP BY state HAVING SUM(budget) > 25000;
```

**Results (CSV format):**
```csv
state,state_budget
CA,47000.0
NY,50000.0
TX,33000.0
```

**Query 10: This is important to identify any contestants or states that are not receiving any promotional budget.**

```sql
SELECT contestant_number, state, budget FROM PROMOTIONAL_BUDGET WHERE budget = 0;
```

**Results (CSV format):**
```csv
contestant_number,state,budget
```

**Query 11: This is important to identify any contestants or states that are not receiving any votes.**

```sql
SELECT contestant_number, state, votes FROM VOTES_RECEIVED WHERE votes = 0;
```

**Results (CSV format):**
```csv
contestant_number,state,votes
```

