# Complete Optimization Problem and Solution: singer

## 1. Problem Context and Goals

### Context  
A music label is tasked with selecting a subset of singers to promote in order to maximize the total sales of their songs. The label must make these decisions while adhering to two key operational constraints: the total budget available for promotions and the maximum number of singers that can be effectively promoted. 

The decision to promote a singer is represented as a binary choice: either the singer is selected for promotion or they are not. The total sales of songs associated with the selected singers will be used to measure the success of the promotion strategy. The label has a fixed budget of 500,000 units, which limits the total net worth of the singers that can be promoted. Additionally, the label can promote no more than 3 singers at a time to ensure effective resource allocation and promotional impact.

### Goals  
The primary goal of this optimization problem is to maximize the total sales of songs by selecting the optimal set of singers for promotion. Success is measured by the cumulative sales of the songs associated with the selected singers. The label aims to achieve this goal while staying within the constraints of the available budget and the maximum number of singers that can be promoted.

## 2. Constraints  

1. **Budget Constraint**: The total net worth of the singers selected for promotion must not exceed the available budget of 500,000 units. This ensures that the label does not overspend on promotional activities.  
2. **Promotional Capacity Constraint**: The label can promote a maximum of 3 singers at a time. This ensures that the promotional efforts are focused and manageable, avoiding resource overextension.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating the song table to include sales data. Configuration logic updates include adding scalar parameters for Total_Budget and Max_Singers.

CREATE TABLE singer_selection (
  singer_id INTEGER,
  is_selected BOOLEAN
);

CREATE TABLE song (
  song_id INTEGER,
  singer_id INTEGER,
  Sales INTEGER
);
```

### Data Dictionary  
- **singer_selection**:  
  - **Business Purpose**: Represents the selection of singers for promotion.  
  - **Optimization Role**: Decision variables indicating whether a singer is selected.  
  - **Columns**:  
    - **singer_id**: Unique identifier for each singer. Links to the singer being considered for selection.  
    - **is_selected**: Binary indicator of whether the singer is selected for promotion.  

- **song**:  
  - **Business Purpose**: Represents the songs and their sales data.  
  - **Optimization Role**: Provides the sales data used to measure the success of the promotion strategy.  
  - **Columns**:  
    - **song_id**: Unique identifier for each song.  
    - **singer_id**: Identifier for the singer of the song. Links the song to the singer.  
    - **Sales**: Sales of the song. Used as the objective coefficient in the optimization problem.  


### Retrieved Values

**Query 1: This query aggregates sales data by singer_id to determine the total sales each singer contributes.**

```sql
SELECT singer_id, SUM(Sales) AS total_sales FROM song GROUP BY singer_id;
```

**Results (CSV format):**
```csv
singer_id,total_sales
1,1500
2,2500
3,1000
```

**Query 2: This query fetches the current selection status of each singer, indicating whether they are already selected for promotion.**

```sql
SELECT singer_id, is_selected FROM singer_selection;
```

**Results (CSV format):**
```csv
singer_id,is_selected
1,0
2,1
3,0
```

**Query 3: This query calculates the total sales for singers who are currently selected for promotion.**

```sql
SELECT ss.singer_id, SUM(s.Sales) AS total_sales FROM singer_selection ss JOIN song s ON ss.singer_id = s.singer_id WHERE ss.is_selected = TRUE GROUP BY ss.singer_id;
```

**Results (CSV format):**
```csv
singer_id,total_sales
2,2500
```

**Query 4: This query counts the number of singers currently selected to ensure it does not exceed the maximum allowed (3).**

```sql
SELECT COUNT(*) AS total_selected_singers FROM singer_selection WHERE is_selected = TRUE;
```

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

**Query 5: Note: Assuming net worth is stored in a hypothetical 'singer' table, which is not provided in the schema.**

```sql
SELECT SUM(net_worth) AS total_net_worth FROM singer WHERE singer_id IN (SELECT singer_id FROM singer_selection WHERE is_selected = TRUE);
```

**Error:** Execution failed on sql 'SELECT SUM(net_worth) AS total_net_worth FROM singer WHERE singer_id IN (SELECT singer_id FROM singer_selection WHERE is_selected = TRUE);': no such table: singer

**Query 6: This query identifies the top 3 singers with the highest total sales, which could be considered for promotion.**

```sql
SELECT singer_id, SUM(Sales) AS total_sales FROM song GROUP BY singer_id ORDER BY total_sales DESC LIMIT 3;
```

**Results (CSV format):**
```csv
singer_id,total_sales
2,2500
1,1500
3,1000
```

**Query 7: This query calculates the total sales for all singers, providing a baseline for comparison.**

```sql
SELECT singer_id, SUM(Sales) AS total_sales FROM song GROUP BY singer_id;
```

**Results (CSV format):**
```csv
singer_id,total_sales
1,1500
2,2500
3,1000
```

**Query 8: This query calculates the total sales for singers who are not currently selected, helping to identify potential candidates for promotion.**

```sql
SELECT s.singer_id, SUM(s.Sales) AS total_sales FROM song s LEFT JOIN singer_selection ss ON s.singer_id = ss.singer_id AND ss.is_selected = TRUE WHERE ss.singer_id IS NULL GROUP BY s.singer_id;
```

**Results (CSV format):**
```csv
singer_id,total_sales
1,1500
3,1000
```

