Given a small DB schema, write SQL queries (joins) to answer several questions. Asked in the LLD stage.
show_name, host_name, episode_count, published_episode_count, and total_listens.episode_count should count all episodes linked to the show, published_episode_count should count only published episodes, and total_listens should sum listens across all episodes for that show.total_listens descending, then show_name ascending.| Column | Type | Description |
|---|---|---|
| host_idPK | INT | Unique host identifier |
| host_name | VARCHAR(100) | Host display name |
| region | VARCHAR(50) | Host region |
| Column | Type | Description |
|---|---|---|
| show_idPK | INT | Unique show identifier |
| show_name | VARCHAR(120) | Show title |
| host_id | INT | Host linked to the show |
| is_active | BOOLEAN | Whether the show is active |
| Column | Type | Description |
|---|---|---|
| episode_idPK | INT | Unique episode identifier |
| show_id | INT | Show linked to the episode |
| episode_title | VARCHAR(150) | Episode title |
| status | VARCHAR(20) | Episode publication status |
| listen_count | INT | Number of listens recorded for the episode |