Data exploration / SQL

SQL Portfolio Project

See how I answer two business questions using SQL queries in a dataset.

Role: Data Analyst
Status: Complete
Skills used: SQL
Joins
Aggregation
Filtering
Sorting

Business question 01

Which media format makes up most of the store's inventory and revenue?

Selecting columns

This selects the number of sales in each category, the total revenue for each media type, and each type’s share of all revenue as a percent rounded to 2 decimal places. A subquery calculates total revenue across all sales, which each category’s revenue is divided by to get its percent.

SELECT
    Track.UnitPrice,
    COUNT(*) AS 'Prodcut_Count',
    SUM(InvoiceLine.UnitPrice * InvoiceLine.Quantity) AS 'Value ($)',
    ROUND(SUM(InvoiceLine.UnitPrice * InvoiceLine.Quantity) * 100.0 / (SELECT SUM(UnitPrice * Quantity) FROM InvoiceLine), 2) AS 'Percent_Of_Revenue (%)',

Case

This is a simple if/else that labels each sale “Video” if its media type ID is 3 and “Audio” for any other ID. The labels go into a new column named Media_Category.

CASE
    WHEN MediaType.MediaTypeId = 3 THEN 'Video'
    ELSE 'Audio'
END AS Media_Category
FROM MediaType

Joining & Filtering

The joins connect MediaType to Track and Track to InvoiceLine through their shared ID keys, so each sale is connected to its media type. GROUP BY then combines all the sales into one row for Audio and one row for Video.

JOIN Track ON MediaType.MediaTypeId = Track.MediaTypeId
JOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackId
GROUP BY Media_Category

Business question 01 · output

The result

Audio makes up most of the store's inventory (2,129 products) and revenue (90.51%).

Query output for Business question 01
Media category	Percent of revenue	Product count	Value	Unit price
Audio	90.51%	2,129	$2,107.71	$0.99
Video	9.49%	111	$220.89	$1.99

SQL query and two-row result showing Audio with 2,129 sales and 90.51 percent of revenue, and Video with 111 sales and 9.49 percent. — Raw output from DB Browser for SQLite

Business question 02

What are the top 5 highest grossing genres in the United States?

I compare revenue by genre to see which categories generate the most sales in the United States.

Selecting the relevant columns

I select the genre name and use the aggregate function SUM to total revenue (unit price × quantity) for each genre.

SELECT
    Genre.Name AS 'Genre',
    SUM(InvoiceLine.UnitPrice * InvoiceLine.Quantity) AS 'Revenue_USD'
FROM Track

Joining

I join the tables by matching each foreign key to the primary key it references, linking the sales data back to the genre.

JOIN Genre ON Track.GenreId = Genre.GenreId
JOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackId
JOIN Invoice ON InvoiceLine.InvoiceId = Invoice.InvoiceId

Filtering

WHERE filters to US invoices only. GROUP BY collapses the rows into one row per genre. ORDER BY sorts genres by revenue, highest first, and LIMIT keeps the top 5.

WHERE Invoice.BillingCountry = 'USA'
GROUP BY Genre
ORDER BY Revenue_USD DESC
LIMIT 5;

Business question 02 · output

The result

Rock is the highest grossing genre in the United States with $155.43 in revenue.

Query output for Business question 02
Genre	Revenue
Rock	$155.43
Latin	$90.09
Metal	$63.36
Alternative & Punk	$49.50
TV Shows	$27.86

DB Browser for SQLite showing the SQL query and its five-row results table for the highest grossing genres in the United States, led by Rock at $155.43. — Raw output from DB Browser for SQLite

See the process

Walkthrough

Walkthrough videoComing soon

The source

The database

Chinook is a digital media store database with 11 tables covering music, playlists, customers, employees, and sales.

Artist

Column	Type	Key
ArtistId	INT	PK
Name	NVARCHAR(120)	—

Album

Column	Type	Key
AlbumId	INT	PK
Title	NVARCHAR(160)	—
ArtistId	INT	FK

Track

Column	Type	Key
TrackId	INT	PK
Name	NVARCHAR(200)	—
AlbumId	INT	FK
MediaTypeId	INT	FK
GenreId	INT	FK
Composer	NVARCHAR(220)	—
Milliseconds	INT	—
Bytes	INT	—
UnitPrice	NUMERIC(10,2)	—

MediaType

Column	Type	Key
MediaTypeId	INT	PK
Name	NVARCHAR(120)	—

Genre

Column	Type	Key
GenreId	INT	PK
Name	NVARCHAR(120)	—

Playlist

Column	Type	Key
PlaylistId	INT	PK
Name	NVARCHAR(120)	—

PlaylistTrack

Column	Type	Key
PlaylistId	INT	PK, FK
TrackId	INT	PK, FK

InvoiceLine

Column	Type	Key
InvoiceLineId	INT	PK
InvoiceId	INT	FK
TrackId	INT	FK
UnitPrice	NUMERIC(10,2)	—
Quantity	INT	—

Invoice

Column	Type	Key
InvoiceId	INT	PK
CustomerId	INT	FK
InvoiceDate	DATETIME	—
BillingAddress	NVARCHAR(70)	—
BillingCity	NVARCHAR(40)	—
BillingState	NVARCHAR(40)	—
BillingCountry	NVARCHAR(40)	—
BillingPostalCode	NVARCHAR(10)	—
Total	NUMERIC(10,2)	—

Customer

Column	Type	Key
CustomerId	INT	PK
FirstName	NVARCHAR(40)	—
LastName	NVARCHAR(20)	—
Company	NVARCHAR(80)	—
Address	NVARCHAR(70)	—
City	NVARCHAR(40)	—
State	NVARCHAR(40)	—
Country	NVARCHAR(40)	—
PostalCode	NVARCHAR(10)	—
Phone	NVARCHAR(24)	—
Fax	NVARCHAR(24)	—
Email	NVARCHAR(60)	—
SupportRepId	INT	FK

Employee

Column	Type	Key
EmployeeId	INT	PK
LastName	NVARCHAR(20)	—
FirstName	NVARCHAR(20)	—
Title	NVARCHAR(30)	—
ReportsTo	INT	FK
BirthDate	DATETIME	—
HireDate	DATETIME	—
Address	NVARCHAR(70)	—
City	NVARCHAR(40)	—
State	NVARCHAR(40)	—
Country	NVARCHAR(40)	—
PostalCode	NVARCHAR(10)	—
Phone	NVARCHAR(24)	—
Fax	NVARCHAR(24)	—
Email	NVARCHAR(60)	—

lerocha / chinook-database (https://github.com/lerocha/chinook-database)

Next projectML Used Vehicle Regression Project Back to work