SQLite query for Top spender 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 topCustomers for the customer (named as TopSpender) that spent the most (based on quantity * unitprice, named as TotalSpending) on each genre of music with sales. Columns : Genre, TopSpender, TotalSpending

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

Customers table :

    [CustomerId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [FirstName] NVARCHAR(40)  NOT NULL,
    [LastName] NVARCHAR(20)  NOT NULL,
    [Company] NVARCHAR(80),
    [Address] NVARCHAR(70),
    [City] NVARCHAR(40),
    [State] NVARCHAR(40),
    [Country] NVARCHAR(40),
    [PostalCode] NVARCHAR(10),
    [Phone] NVARCHAR(24),
    [Fax] NVARCHAR(24),
    [Email] NVARCHAR(60)  NOT NULL,
    [SupportRepId] INTEGER,
    FOREIGN KEY ([SupportRepId]) REFERENCES "employees" ([EmployeeId])
                ON DELETE NO ACTION ON UPDATE NO ACTION

invoice table :

[InvoiceId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [CustomerId] INTEGER  NOT NULL,
    [InvoiceDate] DATETIME  NOT NULL,
    [BillingAddress] NVARCHAR(70),
    [BillingCity] NVARCHAR(40),
    [BillingState] NVARCHAR(40),
    [BillingCountry] NVARCHAR(40),
    [BillingPostalCode] NVARCHAR(10),
    [Total] NUMERIC(10,2)  NOT NULL,
    FOREIGN KEY ([CustomerId]) REFERENCES "customers" ([CustomerId])
                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 tables and aggregate to get the total spendings per genre/customer.
Then use FIRST_VALUE() and MAX() window functions to get the top spenders in each genre:

CREATE VIEW topCustomers AS
SELECT DISTINCT 
       g.Name Genre, 
       FIRST_VALUE(c.LastName || ' ' || c.FirstName) OVER (PARTITION BY g.GenreId ORDER BY SUM(ii.Quantity * ii.UnitPrice) DESC) TopSpender,
       MAX(SUM(ii.Quantity * ii.UnitPrice)) OVER (PARTITION BY g.GenreId) TotalSpending
FROM genres g
INNER JOIN tracks t ON t.GenreId = g.GenreId
INNER JOIN invoice_items ii ON ii.TrackId = t.TrackId
INNER JOIN invoices i ON i.InvoiceId = ii.InvoiceId
INNER JOIN customers c ON c.CustomerId = i.CustomerId
GROUP BY g.GenreId, i.CustomerId;