Songs that stay in the chars for a while
SQL MediumAssume the following schema:
durationInCharts greater than the all the durationInCharts of songs of genre Breaks. Order the results by their durationInCharts in decreasing order. Your result should look like the following:
| trackId | durationInCharts |
|---|---|
| 6166032 | 407 |
| 6706800 | 397 |
| 5971424 | 362 |
| 5954022 | 326 |
| 6329956 | 313 |
One way to do this is to use the keyword
ALL and a subquery:
SELECT trackId,durationInCharts
FROM charts
WHERE durationInCharts > ALL
(SELECT durationInCharts FROM charts c
INNER JOIN songs s ON s.trackid = c.trackid WHERE genre = 'Breaks')
ORDER BY durationInCharts DESC
LIMIT 5;| trackId | durationInCharts |
|---|---|
| 6166032 | 407 |
| 6706800 | 397 |
| 5971424 | 362 |
| 5954022 | 326 |
| 6329956 | 313 |
Of course, we can also use a subquery along with max():
SELECT trackId,durationInCharts
FROM charts
WHERE durationInCharts > (SELECT max(durationInCharts) FROM charts c
INNER JOIN songs s ON s.trackid = c.trackid WHERE genre = 'Breaks')
ORDER BY durationInCharts DESC
LIMIT 5;| trackId | durationInCharts |
|---|---|
| 6166032 | 407 |
| 6706800 | 397 |
| 5971424 | 362 |
| 5954022 | 326 |
| 6329956 | 313 |
If we wanted to do this with CTEs and window functions, one way would have been the following:
WITH max_breaks as
(
SELECT c.trackId, max(IF(genre= 'Breaks',durationInCharts,NULL)) OVER() as max_breaks
FROM charts c INNER JOIN songs s ON c.trackId = s.trackid
)
SELECT c.trackId,durationInCharts
FROM charts c
INNER JOIN max_breaks m ON m.trackid = c.trackid AND durationInCharts > max_breaks
ORDER BY durationInCharts DESC
LIMIT 5;| trackId | durationInCharts |
|---|---|
| 6166032 | 407 |
| 6706800 | 397 |
| 5971424 | 362 |
| 5954022 | 326 |
| 6329956 | 313 |
Subquery, CTE, Join, ALL, Window functions