Project 2: The Business Analyst

As a Business Analyst for “Chinook,” a global digital music store, you’ve been tasked with uncovering insights about customer behavior, product performance, and sales trends to guide the marketing strategy for the next quarter.

Scenario

The management team wants to know which countries have the most potential for growth, who their most loyal customers are, and which music genres are declining in popularity.

The Database Schema (Chinook)

The Chinook database is a standard industry sample that includes:

• Invoices: Sales data with billing addresses and totals.
• InvoiceLines: The specific tracks purchased in each invoice.
• Customers: Customer names, locations, and assigned support reps.
• Tracks: Track names, lengths, and genres.
• Genres: Music categories (Rock, Jazz, etc.).

Your Tasks

1. Revenue by Country
   Calculate the total sales amount for each country. Identify the top 3 countries.

2. Customer Churn Risk
   Find customers who have not made a purchase in the last 6 months. (Use MAX(InvoiceDate) and compare it to the “Current” date of the database).

3. Genre Popularity Shift
   Rank genres by the number of tracks sold. Use a window function to show each genre’s rank within its country.

4. High-Value Support Reps
   Which Employee (Support Rep) has generated the most revenue for the company? Join Employees to Customers to Invoices.

Key Skills Required

• Aggregation: Using SUM() and COUNT() for totals.
• Window Functions: Ranking genres and identifying the latest purchase date.
• CTEs: Organizing your revenue calculations before joining to employee names.

The SQL Challenge: “The Rock Star”

Question:

Which artist has the most tracks in the ‘Rock’ genre and has generated the most total revenue?

Advanced Analysis Template

WITH RockTracks AS (
  -- Your logic to filter tracks by Rock genre
),
ArtistRevenue AS (
  -- Your logic to sum revenue per artist
)
SELECT ...

Final Deliverable

Produce a report (a series of SQL queries) that answers the four tasks above, formatted as a clean, commented script that management can run at any time.