Chinook数据库SQLite查询：创建topCustomers视图获取各流派最高消费用户

Here's the SQLite query to create your desired view, which will show the top spender per genre along with their total spending:

CREATE VIEW topCustomers AS
WITH CustomerGenreSpending AS (
    -- Calculate total spending per customer per genre
    SELECT
        g.Name AS Genre,
        c.FirstName || ' ' || c.LastName AS TopSpender,
        SUM(ii.Quantity * ii.UnitPrice) AS TotalSpending,
        g.GenreId,
        c.CustomerId
    FROM genres g
    INNER JOIN tracks t ON g.GenreId = t.GenreId
    INNER JOIN invoice_items ii ON t.TrackId = ii.TrackId
    INNER JOIN invoices i ON ii.InvoiceId = i.InvoiceId
    INNER JOIN customers c ON i.CustomerId = c.CustomerId
    GROUP BY g.GenreId, g.Name, c.CustomerId, c.FirstName, c.LastName
),
MaxGenreSpending AS (
    -- Find the maximum total spending for each genre
    SELECT
        GenreId,
        Genre,
        MAX(TotalSpending) AS MaxSpending
    FROM CustomerGenreSpending
    GROUP BY GenreId, Genre
)
-- Join to get the top spender(s) for each genre
SELECT
    cgs.Genre,
    cgs.TopSpender,
    cgs.TotalSpending
FROM CustomerGenreSpending cgs
INNER JOIN MaxGenreSpending mgs 
    ON cgs.GenreId = mgs.GenreId 
    AND cgs.TotalSpending = mgs.MaxSpending
ORDER BY cgs.Genre;

How This Works: #

1. CustomerGenreSpending CTE: This first common table expression joins all relevant tables to calculate how much each customer spent on tracks from each genre. We group by GenreId (to avoid conflicts if genres share the same name) and CustomerId (to handle duplicate customer names) to ensure accurate totals.
2. MaxGenreSpending CTE: This CTE finds the highest total spending amount for each genre using the MAX() function.
3. Final Join: We join the two CTEs to match each genre's maximum spending with the corresponding customer(s) who achieved that total. This handles cases where multiple customers tie for the top spot in a genre—all tied customers will appear as separate rows.

Key Notes: #

• Excludes Genres With No Sales: The inner joins ensure that only genres with at least one track sold are included in the view.
• Total Spending Calculation: Uses SUM(ii.Quantity * ii.UnitPrice) to get the exact total spent by each customer on tracks from the genre, rather than relying on the Total field in the invoices table (which aggregates all items on an invoice).

内容的提问来源于stack exchange，提问作者sun_dance