Basic CTE: Artist Album Counts

Common Table Expressions (CTEs) let you define named subqueries that make complex queries more readable. Let's start with a simple CTE.

Write a query using a CTE to count albums per artist, then select artists with more than 5 albums.

Schema: Artist

Schema: Album

Requirements:

• Define a CTE named "ArtistAlbumCounts" that counts albums per artist
• In the main query, filter for artists with more than 5 albums
• Return ArtistName and AlbumCount
• Order by AlbumCount descending, then ArtistName ascending to break ties

CTE Syntax:

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name WHERE ...;