ananyadas777
Answered

1. Artist Play Time

In a music streaming company, there is a table that records each time a song is played. It records the song ID and the number of seconds that the song was played.

Table Name: `song_play_history`

Sample Data:
\begin{tabular}{|l|l|}
\hline SONG_ID & PLAY_TIME \\
\hline S1 & 100 \\
\hline S2 & 130 \\
\hline
\end{tabular}

There is another table that maps each song to the artist.

Table Name: `songs`

Sample Data:
\begin{tabular}{|l|l|}
\hline SONG_ID & ARTIST_ID \\
\hline S1 & A2 \\
\hline S2 & A6 \\
\hline
\end{tabular}

Order the artists in descending order of the total playtime of their songs.

Sample Output:
\begin{tabular}{|l|l|}
\hline ARTIST_ID & TOTAL_PLAY_TIME \\
\hline A1 & 1900 \\
\hline A2 & 1850 \\
\hline
\end{tabular}



Answer :

GinnyAnswer
Sure, let's go through a detailed step-by-step solution to determine the total playtime for each artist and then order them in descending order.

### Example Data Provided:

1. song_play_history Table
```
+---------+-----------+
| SONG_ID | PLAY_TIME |
+---------+-----------+
| S1 | 100 |
| S2 | 130 |
+---------+-----------+
```

2. songs Table
```
+---------+-----------+
| SONG_ID | ARTIST_ID |
+---------+-----------+
| S1 | A2 |
| S2 | A6 |
+---------+-----------+
```

### Step-by-Step Solution:

1. Identify the Play Times:
- From the `song_play_history` table:
- Song S1 was played for 100 seconds.
- Song S2 was played for 130 seconds.

2. Identify the Mapping of Songs to Artists:
- From the `songs` table:
- Song S1 is associated with Artist A2.
- Song S2 is associated with Artist A6.

3. Calculate Total Play Times Per Artist:
- For Artist A2:
- Only Song S1 is associated, which was played for 100 seconds.
- Total play time for Artist A2 = 100 seconds.

- For Artist A6:
- Only Song S2 is associated, which was played for 130 seconds.
- Total play time for Artist A6 = 130 seconds.

4. Prepare the Resulting Data:
- After summing up the playtimes, we get:
- Artist A2 has 100 seconds of total play time.
- Artist A6 has 130 seconds of total play time.

5. Sort the Results in Descending Order of Play Time:
- Comparing the total play times:
- Artist A6 with 130 seconds comes before Artist A2 with 100 seconds.

### Final Ordered Output:
```
+-----------+----------------+
| ARTIST_ID | TOTAL_PLAY_TIME|
+-----------+----------------+
| A6 | 130 |
| A2 | 100 |
+-----------+----------------+
```

Hence, the artists ordered by their total playtime in descending order are:

1. Artist A6 with a total playtime of 130 seconds.
2. Artist A2 with a total playtime of 100 seconds.

Other Questions