Chinook Project.sql

USE chinook;

SHOW TABLES;

# Question 1 Which countries generate the highest total revenue?
SELECT 
    c.Country, SUM(i.Total) AS TotalRevenue
FROM
    Invoice i
        JOIN
    Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalRevenue DESC;

# Question 2 Who are the customers with the highest lifetime value?
SELECT 
    c.CustomerId,
    c.FirstName,
    c.LastName,
    SUM(i.Total) AS LifetimeValue
FROM
    Customer c
        JOIN
    Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId , c.FirstName , c.LastName
ORDER BY LifetimeValue DESC;

# Question 3 Which sales support employee manages the highest revenue customers?
SELECT 
    e.EmployeeId,
    e.FirstName,
    e.LastName,
    SUM(i.Total) AS ManagedRevenue
FROM
    Employee e
        JOIN
    Customer c ON e.EmployeeId = c.SupportRepId
        JOIN
    Invoice i ON c.CustomerId = i.CustomerId
GROUP BY e.EmployeeId , e.FirstName , e.LastName
ORDER BY ManagedRevenue DESC;

# Question 5 Identify the countries with the top sales. 
SELECT 
    c.Country, SUM(i.Total) AS TotalSales
FROM
    Invoice i
        JOIN
    Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalSales DESC;

# Question 6 What is the customer distribution by country?
SELECT 
    Country, COUNT(*) AS NumberOfCustomers
FROM
    Customer
GROUP BY Country
ORDER BY NumberOfCustomers DESC;

# Question 7 Average invoice total per country
SELECT 
    c.Country, AVG(i.Total) AS AverageInvoiceTotal
FROM
    Invoice i
        JOIN
    Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY AverageInvoiceTotal DESC;

# Question 8 Tracks with the highest unit price
SELECT 
    TrackId, Name, UnitPrice
FROM
    Track
ORDER BY UnitPrice DESC;

# Question 9 Retrieve all tracks that are longer than 5 minutes.
SELECT 
    TrackId, Name, Milliseconds
FROM
    Track
WHERE
    Milliseconds > 300000
ORDER BY Milliseconds DESC;

# Question 10 Show all customers who are from the USA
SELECT 
    *
FROM
    Customer
WHERE
    Country = 'USA';
    
# Question 11 Retrieve all invoices where the total amount is greater than 10.
SELECT 
    *
FROM
    Invoice
WHERE
    Total > 10;
    
# Question 12 What are the most popular music genres by number of tracks sold?
SELECT 
    g.Name AS Genre, SUM(il.Quantity) AS UnitsSold
FROM
    Genre g
        JOIN
    Track t ON g.GenreId = t.GenreId
        JOIN
    InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY g.Name
ORDER BY UnitsSold DESC;

# Question 13 Which tracks generate the most revenue?
SELECT 
    t.TrackId,
    t.Name,
    SUM(il.UnitPrice * il.Quantity) AS Revenue
FROM
    Track t
        JOIN
    InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY t.TrackId , t.Name
ORDER BY Revenue DESC;

# Question 14 Which albums sell the most tracks?
SELECT 
    a.Title, COUNT(il.InvoiceLineId) AS TracksSold
FROM
    Album a
        JOIN
    Track t ON a.AlbumId = t.AlbumId
        JOIN
    InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY a.Title
ORDER BY TracksSold DESC;

# Question 15 Which artists generate the most revenue?
SELECT 
    ar.name as Artist, SUM(il.Unitprice * il.Quantity) AS Revenue
FROM
    artist ar
        JOIN
    album a ON ar.artistid = a.ArtistId
        JOIN
    track t ON t.AlbumId = a.AlbumId
        JOIN
    invoiceline il ON t.TrackId = il.TrackId
GROUP BY Artist
ORDER BY Revenue DESC;

# Qestion 16 What is the average invoice value per country?
SELECT 
    billingcountry AS country,
    ROUND(AVG(total), 2) AS Average_Invoice_Value
FROM
    invoice
GROUP BY country
ORDER BY Average_Invoice_Value DESC;

# Question 17 What is the average track duration by genre?
SELECT 
    g.Name AS Genre,
    ROUND(AVG(t.Milliseconds), 2) AS AvgDurationMs
FROM
    Genre g
        JOIN
    Track t ON g.GenreId = t.GenreId
GROUP BY g.Name
ORDER BY AvgDurationMs DESC;

# Question 18 Which playlists contain the most tracks?
SELECT 
    p.Name, COUNT(pt.TrackId) AS NumberOfTracks
FROM
    Playlist p
        JOIN
    PlaylistTrack pt ON p.PlaylistId = pt.PlaylistId
GROUP BY p.Name
ORDER BY NumberOfTracks DESC;

# Question 19 Which media type generates the highest revenue?
SELECT 
    m.Name AS MediaType,
    SUM(il.UnitPrice * il.Quantity) AS Revenue
FROM
    MediaType m
        JOIN
    Track t ON m.MediaTypeId = t.MediaTypeId
        JOIN
    InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY m.Name
ORDER BY Revenue DESC;

# Question 20 Which customers made the most purchases?
SELECT 
    c.CustomerId,
    c.FirstName,
    c.LastName,
    COUNT(i.InvoiceId) AS NumberOfPurchases
FROM
    Customer c
        JOIN
    Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId , c.FirstName , c.LastName
ORDER BY NumberOfPurchases DESC;

# Question 21 What are the top-selling tracks per genre?
SELECT 
    g.Name AS Genre,
    t.Name AS Track,
    SUM(il.Quantity) AS UnitsSold
FROM
    Genre g
        JOIN
    Track t ON g.GenreId = t.GenreId
        JOIN
    InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY g.Name , t.Name
ORDER BY g.Name , UnitsSold DESC;

# Question 22 Which cities have the most customers?
SELECT 
    City, COUNT(*) AS NumberOfCustomers
FROM
    Customer
GROUP BY City
ORDER BY NumberOfCustomers DESC;

# Question 23 What are the most expensive tracks?
SELECT 
    Name, UnitPrice
FROM
    Track
ORDER BY UnitPrice DESC;

# Question 24 Which employees report to which managers? (hierarchy query)
SELECT e.FirstName AS Employee,
       m.FirstName AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ReportsTo = m.EmployeeId;

# Question 25 Find tracks longer than the average length
SELECT 
    *
FROM
    Track
WHERE
    Milliseconds > (SELECT 
            AVG(Milliseconds)
        FROM
            Track);

# Question 26 Revenue trend by invoice date
SELECT 
    InvoiceDate, SUM(Total) AS DailyRevenue
FROM
    Invoice
GROUP BY InvoiceDate
ORDER BY InvoiceDate;

# Question 27 Which customers haven’t made any purchases?
SELECT 
    c.*
FROM
    Customer c
        LEFT JOIN
    Invoice i ON c.CustomerId = i.CustomerId
WHERE
    i.InvoiceId IS NULL;

# Question 28 Which tracks appear in the most playlists?
SELECT 
    t.Name, COUNT(pt.PlaylistId) AS PlaylistCount
FROM
    Track t
        JOIN
    PlaylistTrack pt ON t.TrackId = pt.TrackId
GROUP BY t.Name
ORDER BY PlaylistCount DESC;

# Question 29 Total revenue per year (sales performance)
SELECT YEAR(InvoiceDate) AS Year,
       SUM(Total) AS Revenue
FROM Invoice
GROUP BY Year
ORDER BY Year;

# Question 30 Who are the top customers in each country?
SELECT 
    c.Country,
    c.FirstName,
    c.LastName,
    SUM(i.Total) AS TotalSpent
FROM
    Customer c
        JOIN
    Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.Country , c.FirstName , c.LastName
ORDER BY c.Country , TotalSpent DESC;