Sales performance at Chinook (Data Analysis with SQL queries)

This is a showcase of SQL queries run on a fictious database for a record selling company called Chinook Records.

I choose a SQLite 3 database as:

• it’s really simple to run on a simple computer, not needing to install a full solution such as MySQL, PostgreSQL etc.
• although there are certain functionalities not available on SQLlite, most of the SQL queries can be run on other variants

The example database was downloaded from the website SQLite Sample Database and below is the diagram of the DB.

The database is fairly simple:

• we have songs (tracks) that we sell to customers
• songs / tracks belong to an album, an artist and a genre and they have a specific type (ex: MP3, AAC etc.)
• customers buy tracks and we invoice them
• one invoice will have one or several lines corresponding to the number of tracks that we sold to that customer.

We’ll query the DB to get some insights into this company. Note that most of the queries are short, they’re only meant to go through certain operations:

• SELECT statement
• LIMIT results
• Filter, Group and order results
• Subqueries
• JOINS
• String formatting
• Date operations
• CTE

Examples of artists and albums

Let’s see first what we’re selling and to whom we are selling to.

SELECT *
FROM artists
LIMIT 10

Some of our customers

SELECT FirstName, LastName, Country
FROM customers
LIMIT 10

How about only the customers in Brazil?

SELECT FirstName, LastName, Country
FROM customers
WHERE Country = "Brazil"
LIMIT 10

Top bands with the most number of albums

SELECT Name, total_albums
FROM artists
LEFT JOIN
	( 
	SELECT ArtistId, count(ArtistId) AS total_albums
	FROM albums
	GROUP BY ArtistId
	) AS total_albums
ON artists.ArtistId = total_albums.ArtistId
ORDER BY total_albums DESC
LIMIT 10

Top spenders

Let’s get a list of our top spenders.

SELECT Sum(I.Total) as Total_purchased,
	C.FirstName,
	UPPER(C.LastName)
FROM invoices I, customers C
WHERE I.CustomerId = C.CustomerId
GROUP BY C.LastName
ORDER BY Total_purchased DESC
LIMIT 10

Top sales by month

SELECT strftime('%Y-%m', InvoiceDate) as YearMonth,
	SUM(Total)
FROM invoices
GROUP BY YearMonth
LIMIT 10

Note: strftime('%Y-%m', InvoiceDate) is the SQLite3 equivalent of Year(InvoiceDate) and Month(InvoiceDate)

The total of 37.62 repeats as the invoices were copy-pasted in the DB and only the date changed.

Top countries by total bought

Sales per country

SELECT BillingCountry, SUM("Total") as total_per_country
FROM invoices
GROUP BY BillingCountry
ORDER BY "total_per_country" DESC
LIMIT 10

Top countries with sales higher than the average

WITH average_per_country (avg_per_country) AS (
	SELECT AVG("Total") as avg_per_country
	FROM invoices
	)

SELECT BillingCountry, AVG("Total") as total_per_country
FROM invoices, average_per_country
GROUP BY BillingCountry
HAVING (total_per_country > average_per_country.avg_per_country)