Advanced SQL Queries for Database Analytics

Posted on Sep 11, 2025 in Computing and Communications

This document presents a collection of practical SQL queries designed to extract valuable insights from a database, likely a music store schema such as Chinook. Each query demonstrates different SQL concepts, from basic data retrieval to complex joins, subqueries, and aggregations, providing solutions for common business questions.

Top 10 Customers by Spending (Over $40)

Identifies the top 10 customers who have spent more than $40 in total, ordered by their total expenditure in descending order.

SELECT c.customerid, c.firstname, c.lastname, SUM(i.total) AS total_spent
FROM customer c
JOIN invoice i ON c.customerid = i.customerid
GROUP BY c.customerid, c.firstname, c.lastname
HAVING SUM(i.total) > 40
ORDER BY total_spent DESC
LIMIT 10;

Customers in Cities with Invoices
Retrieves all customer details for individuals residing in cities where at least one invoice has been issued.
```
SELECT *
FROM customer c
WHERE c.city IN ( SELECT DISTINCT billingcity
FROM invoice
);
```

Tracks by The Beatles from ‘WHITE’ Album

Finds all track details for songs by ‘The Beatles’ specifically from their album titled ‘WHITE’.

SELECT *
FROM track t
JOIN album al ON t.albumid = al.albumid
JOIN artist ar ON al.artistid = ar.artistid
WHERE al.title = 'WHITE' AND ar.name = 'The Beatles'
;

Customers Who Purchased ‘Grunge’ Playlist Tracks

Lists distinct customers who have purchased any track that is part of the ‘Grunge’ playlist.

SELECT DISTINCT c.customerid, c.firstname, c.lastname
FROM customer c
JOIN invoice i ON c.customerid = i.customerid
JOIN invoiceline il ON i.invoiceid = il.invoiceid
JOIN track t ON il.trackid = t.trackid
JOIN playlisttrack pt ON t.trackid = pt.trackid
JOIN playlist pl ON pt.playlistid = pl.playlistid
WHERE pl.name = 'Grunge'
;

Composer Song Counts
Counts the number of songs for each composer, excluding tracks where the composer is not specified.
```
SELECT composer, COUNT(*) AS num_songs
FROM track
WHERE composer IS NOT NULL
GROUP BY composer
;
```

Albums with Over 10 Tracks

Identifies albums that contain more than 10 tracks, displaying the album title and the total count of tracks.

SELECT DISTINCT a.title, COUNT(t.trackid) AS num_songs
FROM album a
JOIN track t ON a.albumid = t.albumid
GROUP BY a.albumid , a.title
HAVING COUNT(t.trackid) > 10
;

Composer Song Counts by Genre

Provides a breakdown of song counts for each composer, categorized by genre, for tracks with a specified composer.

SELECT t.composer, g.name, COUNT(*) AS num_songs
FROM track t
JOIN genre g ON t.genreid = g.genreid
WHERE t.composer IS NOT NULL
GROUP BY t.composer, g.name
;

Top Selling Tracks by Composer

Lists tracks, their composers, and the total quantity sold, ordered by total sales in descending order.

SELECT t.name, t.composer, SUM(il.quantity) AS total_sales
FROM track t
JOIN invoiceline il ON t.trackid = il.trackid
WHERE t.composer IS NOT NULL
GROUP BY t.trackid, t.name, t.composer
ORDER BY total_sales DESC
;

Total Sales by Country

Calculates the aggregate quantity of products sold for each customer’s country.

SELECT c.country, SUM(il.quantity) AS total_sales
FROM customer c
JOIN invoice i ON c.customerid = i.customerid
JOIN invoiceline il ON i.invoiceid = il.invoiceid
GROUP BY c.country
;

Employees Supporting Max Invoice Customers

Retrieves details of employees who support customers with the highest single invoice total.

SELECT e.*, i.total
FROM employee e
JOIN customer c ON e.employeeid = c.supportrepid
JOIN invoice i ON c.customerid = i.customerid
WHERE i.total = (
SELECT MAX(total)
FROM invoice)
;

Playlists with Most Expensive or Longest Track

Identifies playlists that contain either the single most expensive track or the single longest track (by milliseconds).

SELECT pl.name
FROM playlist pl
JOIN playlisttrack pt ON pl.playlistid = pt.playlistid
JOIN track t ON pt.trackid = t.trackid
WHERE t.trackid = (
SELECT trackid
FROM track
ORDER BY unitprice DESC
LIMIT 1
)
OR t.trackid = (
SELECT trackid
FROM track
ORDER BY milliseconds DESC
LIMIT 1
)
;

Unsold Tracks with Discounted Price

Lists tracks that have never been sold, showing their original price and a new price with a 20% discount.

SELECT t.trackid, t.name, t.unitprice AS original_price, ROUND(t.unitprice * 0.8, 2) AS discounted_price
FROM track t
LEFT JOIN invoiceline il ON t.trackid = il.trackid
WHERE il.trackid IS NULL
;

Customers with a Single Invoice

Retrieves customer details and their invoice dates for customers who have only one invoice recorded.

SELECT c.customerid, CONCAT(c.lastname, ', ', c.firstname) AS customer_details, i.invoicedate
FROM customer c
JOIN invoice i ON c.customerid = i.customerid
WHERE c.customerid IN (
SELECT customerid
FROM invoice
GROUP BY customerid
HAVING COUNT(*) = 1
)
;

Customers and Employees Outside UK

Combines a list of customers and employees who are not from the United Kingdom, indicating their type (Customer/Employee).

SELECT c.firstname AS first_name,
        c.lastname AS last_name,
        'Customer' AS person_type
FROM customer c
WHERE c.country NOT LIKE 'United Kingdom'

UNION

SELECT e.firstname AS first_name,
        e.lastname AS last_name,
        'Employee' AS person_type
FROM employee e
WHERE e.country NOT LIKE 'UK'

ORDER BY first_name, last_name
;

Employee Sales Performance (May 2025)

Analyzes employee sales performance for May 2025, counting distinct invoices and tracks sold, for employees with more than 5 distinct invoices.

SELECT e.firstname, e.lastname, COUNT(DISTINCT i.invoiceid) AS num_invoices,
COUNT(DISTINCT il.trackid) AS num_distinct_tracks
FROM employee e
JOIN customer c ON e.employeeid = c.supportrepid
JOIN invoice i ON c.customerid = i.customerid
JOIN invoiceline il ON i.invoiceid = il.invoiceid
WHERE i.invoicedate BETWEEN '2025-05-01' AND '2025-05-31'
GROUP BY e.employeeid, e.firstname, e.lastname
HAVING COUNT(DISTINCT i.invoiceid) > 5
;

Top 10 Customers by Products & Spending

Identifies the top 10 customers based on total products purchased and total spending, for those with at least 6 invoices and over $40 total spent.

SELECT c.firstname, c.lastname, SUM(il.quantity) AS total_products, SUM(i.total) AS total_spent
FROM customer c
JOIN invoice i ON c.customerid = i.customerid
JOIN invoiceline il ON i.invoiceid = il.invoiceid
GROUP BY c.customerid, c.firstname, c.lastname
HAVING COUNT(DISTINCT i.invoiceid) >= 6
AND SUM(i.total) > 40
ORDER BY total_products DESC, total_spent DESC
LIMIT 10
;

Customers with Invoices After John Smith’s First

Lists customers who have invoices dated after the earliest invoice date of ‘John Smith’.

SELECT c.customerid, CONCAT(c.firstname, ', ', c.lastname) AS full_name, c.country, c.state, c.city
FROM customer c
JOIN invoice i ON c.customerid = i.customerid
WHERE i.invoicedate >
(SELECT MIN(i2.invoicedate)
FROM customer c2
JOIN invoice i2 ON c2.customerid = i2.customerid
WHERE c2.firstname = 'John' AND c2.lastname = 'Smith')
GROUP BY c.customerid, c.firstname, c.lastname, c.country, c.state, c.city
ORDER BY c.country, c.state, c.city
;