sqlite query for the best selling album in each genre-CodePudding

I am using the basic chinook database, but i can't figure out how to write a SQLite query to create a view called BestSeller for the bestselling album in each genre with sales (based on the quantity of tracks sold, named as Sales) with the columns: Genre, Album, Artist, Sales.

The schema for the album table:

 [Title] NVARCHAR(160)  NOT NULL,
 [ArtistId] INTEGER  NOT NULL,
 FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])

artists table :

[ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)

tracks table schema:

 [TrackId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(200)  NOT NULL,
    [AlbumId] INTEGER,
    [MediaTypeId] INTEGER  NOT NULL,
    [GenreId] INTEGER,
    [Composer] NVARCHAR(220),
    [Milliseconds] INTEGER  NOT NULL,
    [Bytes] INTEGER,
    [UnitPrice] NUMERIC(10,2)  NOT NULL,
    FOREIGN KEY ([AlbumId]) REFERENCES "albums" ([AlbumId])
                ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([GenreId]) REFERENCES "genres" ([GenreId])
                ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([MediaTypeId]) REFERENCES "media_types" ([MediaTypeId])
                ON DELETE NO ACTION ON UPDATE NO ACTION

table invoice_items:


    [InvoiceLineId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [InvoiceId] INTEGER  NOT NULL,
    [TrackId] INTEGER  NOT NULL,
    [UnitPrice] NUMERIC(10,2)  NOT NULL,
    [Quantity] INTEGER  NOT NULL,
    FOREIGN KEY ([InvoiceId]) REFERENCES "invoices" ([InvoiceId])
                ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([TrackId]) REFERENCES "tracks" ([TrackId])
                ON DELETE NO ACTION ON UPDATE NO ACTION

I have all these other tables as well.

albums          employees       invoices        playlists
artists         genres          media_types     tracks
customers       invoice_items   playlist_track

Any help is appreciated.

CodePudding user response：

Join the relevant tables and group by genre and album to get the quantity of tracks sold of each album.
Then use FIRST_VALUE() and MAX() window functions to get the album with the most sales in each genre and the the number of sales:

CREATE VIEW BestSeller AS
SELECT DISTINCT 
       g.Name Genre, 
       FIRST_VALUE(a.Title) OVER (PARTITION BY g.GenreId ORDER BY COUNT(*) DESC) Album, 
       FIRST_VALUE(r.Name) OVER (PARTITION BY g.GenreId ORDER BY COUNT(*) DESC) Artist, 
       MAX(COUNT(*)) OVER (PARTITION BY g.GenreId) Sales
FROM genres g
INNER JOIN tracks t ON t.GenreId = g.GenreId
INNER JOIN albums a ON a.AlbumId = t.AlbumId
INNER JOIN artists r ON r.ArtistId = a.ArtistId
INNER JOIN invoice_items i ON i.TrackId = t.TrackId
GROUP BY g.GenreId, a.AlbumId;

See the demo