SQLQueries.sql

-------------------------------- Chinook Music Store Analytics Dashboard -----------------------------------------

-- SQL Queries

-- Basic Queries (for data exploration)
-- 1. Total Sales by Country
SELECT c.Country, SUM(i.Total) AS TotalSales
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.Country
ORDER BY TotalSales DESC

-- 2. Top 10 Customers by Total Spend
SELECT TOP 10 
       c.FirstName + ' ' + c.LastName AS CustomerName,
       SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.FirstName, c.LastName
ORDER BY TotalSpent DESC

-- Intermediate Queries
-- 3. Most Popular Genres (by Sales Quantity)
SELECT g.Name AS Genre,
       SUM(il.Quantity) AS TotalSold
FROM InvoiceLine il
JOIN Track t ON il.TrackId = t.TrackId
JOIN Genre g ON t.GenreId = g.GenreId
GROUP BY g.Name
ORDER BY TotalSold DESC

-- 4. Monthly Revenue Trend
SELECT 
    FORMAT(i.InvoiceDate, 'yyyy-MM') AS Month,
    SUM(i.Total) AS MonthlySales
FROM Invoice i
GROUP BY FORMAT(i.InvoiceDate, 'yyyy-MM')
ORDER BY Month

-- 5. Revenue per Employee (via Customer Support Rep)
SELECT 
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    SUM(i.Total) AS TotalSales
FROM Employee e
JOIN Customer c ON e.EmployeeId = c.SupportRepId
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY e.FirstName, e.LastName
ORDER BY TotalSales DESC

-- Advanced Queries
-- 6. Top Artists by Revenue
SELECT 
    ar.Name AS Artist,
    SUM(il.UnitPrice * il.Quantity) AS TotalRevenue
FROM InvoiceLine il
JOIN Track t ON il.TrackId = t.TrackId
JOIN Album al ON t.AlbumId = al.AlbumId
JOIN Artist ar ON al.ArtistId = ar.ArtistId
GROUP BY ar.Name
ORDER BY TotalRevenue DESC

-- 7. Customer Lifetime Value (CLV)
SELECT 
    c.CustomerId,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    SUM(i.Total) AS LifetimeValue,
    COUNT(i.InvoiceId) AS PurchaseCount,
    AVG(i.Total) AS AvgOrderValue
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY LifetimeValue DESC

-- 8. Ranking Top Genres per Country (Window Function)
WITH GenreSales AS (
    SELECT 
        c.Country,
        g.Name AS Genre,
        SUM(il.Quantity) AS TotalSold
    FROM InvoiceLine il
    JOIN Invoice i ON il.InvoiceId = i.InvoiceId
    JOIN Customer c ON i.CustomerId = c.CustomerId
    JOIN Track t ON il.TrackId = t.TrackId
    JOIN Genre g ON t.GenreId = g.GenreId
    GROUP BY c.Country, g.Name
)
SELECT *
FROM (
    SELECT 
        Country, Genre, TotalSold,
        RANK() OVER (PARTITION BY Country ORDER BY TotalSold DESC) AS Rank
    FROM GenreSales
) Ranked
WHERE Rank = 1

-- 9. Revenue Growth Rate (Year-over-Year)
WITH YearlySales AS (
    SELECT 
        YEAR(InvoiceDate) AS SalesYear,
        SUM(Total) AS TotalSales
    FROM Invoice
    GROUP BY YEAR(InvoiceDate)
)
SELECT 
    SalesYear,
    TotalSales,
    LAG(TotalSales) OVER (ORDER BY SalesYear) AS PrevYearSales,
    ROUND(((TotalSales - LAG(TotalSales) OVER (ORDER BY SalesYear)) / 
           LAG(TotalSales) OVER (ORDER BY SalesYear)) * 100, 2) AS YoYGrowth
FROM YearlySales