# Complete Optimization Problem and Solution: music_4

## 1. Problem Context and Goals

### Context  
A music festival organizer is tasked with curating a lineup of songs that maximizes the total number of weeks these songs have spent on top charts. The organizer must make binary decisions for each song, determining whether it is included in the festival lineup. The primary operational parameter driving this decision is the number of weeks each song has been on top charts, which directly contributes to the festival's overall success metric.  

To ensure a balanced and diverse festival, the organizer must adhere to several business configuration parameters:  
- The festival can include a maximum of 50 songs, ensuring the lineup remains manageable and focused.  
- No single artist can have more than 3 songs in the festival, promoting diversity and preventing overrepresentation of any one artist.  
- The festival must feature at least 10 unique artists, guaranteeing a broad representation of musical talent.  

These constraints are designed to align with realistic operational limitations while maintaining the festival's appeal and manageability. The decision-making process is linear, focusing solely on selecting songs based on their chart performance and ensuring compliance with the established constraints.  

### Goals  
The primary goal of this optimization problem is to maximize the total number of weeks that the selected songs have spent on top charts. This is achieved by summing the weeks on top for each song included in the festival lineup. Success is measured by the cumulative chart performance of the selected songs, ensuring the festival features the most impactful and popular tracks.  

The optimization process is designed to be straightforward and linear, avoiding complex relationships such as variable products or divisions. The focus is on making clear, binary decisions for each song while adhering to the festival's operational constraints.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure the festival lineup is both high-performing and diverse:  
1. **Total Song Capacity**: The total number of songs selected for the festival cannot exceed 50. This ensures the lineup remains manageable and aligns with the festival's capacity.  
2. **Maximum Songs Per Artist**: No single artist can have more than 3 songs included in the festival. This promotes diversity and prevents any one artist from dominating the lineup.  
3. **Minimum Number of Artists**: The festival must feature at least 10 unique artists. This guarantees a broad representation of musical talent and enhances the festival's appeal.  

These constraints are designed to be linear and straightforward, ensuring the optimization problem remains computationally efficient and aligned with the festival's operational goals.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE song_selection (
  x_song BOOLEAN
);

CREATE TABLE volume (
  Weeks_on_Top INTEGER
);
```

### Data Dictionary  
- **song_selection**:  
  - **Business Purpose**: This table contains binary decision variables indicating whether a song is selected for the festival.  
  - **Optimization Role**: Decision variables in the optimization formulation.  
  - **Columns**:  
    - **x_song**: A binary variable (0 or 1) representing whether a song is included in the festival lineup.  

- **volume**:  
  - **Business Purpose**: This table tracks the number of weeks each song has spent on top charts.  
  - **Optimization Role**: Provides coefficients for the objective function.  
  - **Columns**:  
    - **Weeks_on_Top**: The number of weeks a song has been on top charts, used to measure its impact and popularity.  


### Retrieved Values

**Query 1: This is essential for the objective function, as it provides the data needed to maximize the total number of weeks on top charts.**

```sql
SELECT s.x_song, v.Weeks_on_Top FROM song_selection s JOIN volume v ON s.x_song = v.Weeks_on_Top;
```

**Results (CSV format):**
```csv
x_song,Weeks_on_Top
```

**Query 2: This helps in understanding the total pool of songs from which the festival lineup will be curated.**

```sql
SELECT COUNT(*) AS total_songs FROM song_selection;
```

**Results (CSV format):**
```csv
total_songs
3
```

**Query 3: This provides a baseline for the maximum possible value of the objective function.**

```sql
SELECT SUM(Weeks_on_Top) AS total_weeks_on_top FROM volume;
```

**Results (CSV format):**
```csv
total_weeks_on_top
41
```

**Query 4: Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.**

```sql
SELECT artist, COUNT(*) AS songs_per_artist FROM song_selection GROUP BY artist;
```

**Error:** Execution failed on sql 'SELECT artist, COUNT(*) AS songs_per_artist FROM song_selection GROUP BY artist;': no such column: artist

**Query 5: Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.**

```sql
SELECT COUNT(DISTINCT artist) AS unique_artists FROM song_selection;
```

**Error:** Execution failed on sql 'SELECT COUNT(DISTINCT artist) AS unique_artists FROM song_selection;': no such column: artist

**Query 6: This helps in identifying the most impactful songs that could be included in the festival lineup, considering the total song capacity constraint.**

```sql
SELECT s.x_song, v.Weeks_on_Top FROM song_selection s JOIN volume v ON s.x_song = v.Weeks_on_Top ORDER BY v.Weeks_on_Top DESC LIMIT 50;
```

**Results (CSV format):**
```csv
x_song,Weeks_on_Top
```

**Query 7: This is useful for ensuring the total number of songs does not exceed the festival's capacity of 50.**

```sql
SELECT COUNT(*) AS selected_songs FROM song_selection WHERE x_song = 1;
```

**Results (CSV format):**
```csv
selected_songs
2
```

**Query 8: Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.**

```sql
SELECT artist, COUNT(*) AS selected_songs_per_artist FROM song_selection WHERE x_song = 1 GROUP BY artist;
```

**Error:** Execution failed on sql 'SELECT artist, COUNT(*) AS selected_songs_per_artist FROM song_selection WHERE x_song = 1 GROUP BY artist;': no such column: artist

**Query 9: Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.**

```sql
SELECT COUNT(DISTINCT artist) AS selected_unique_artists FROM song_selection WHERE x_song = 1;
```

**Error:** Execution failed on sql 'SELECT COUNT(DISTINCT artist) AS selected_unique_artists FROM song_selection WHERE x_song = 1;': no such column: artist

