Müzik Veri Analizi

Bu projede kullandığım Müzik verilerine buradaki linkten tıklayarak ulaşabilirsiniz.

Projenin SQL sorguları ve her sorgunun tablo hali aşağıdaki gibidir.

Tablolarımıza göz attık
select * from dbo.album$
select * from dbo.artist$
select * from customer$
select * from employee$
select * from genre$
select * from invoice$
select * from invoice_line$
select * from media_type$
select * from playlist$
select * from playlist_track$
select * from track$

1.Soru:İş unvanına göre en kıdemli çalışan kimdir?

SELECT TOP 1 title, last_name, first_name
FROM employee$
ORDER BY levels DESC

2.Soru:En çok faturası olan ülke hangisi?

SELECT COUNT(*) AS Toplam, billing_country
FROM invoice$
GROUP BY billing_country
ORDER BY Toplam DESC

3.Soru:En çok ödenen ilk 3 fatura tutarı

SELECT total
FROM invoice$
ORDER BY total DESC

4.Soru:En yüksek fatura toplamına sahip bir şehri döndüren bir sorgu yazın. Hem şehir adını hem de tüm fatura toplamlarının toplamını döndürün

SELECT TOP 1 billing_city, SUM(total) AS InvoiceTotal
FROM invoice$
GROUP BY billing_city
ORDER BY InvoiceTotal DESC

5.Soru:En iyi müşteri kimdir? En çok para harcayan müşteri en iyi müşteri ilan edilecektir. En çok para harcayan kişiyi döndüren bir sorgu yazın

SELECT TOP 1 customer$.customer_id, first_name, last_name, SUM(total) AS total_spending
FROM customer$
JOIN invoice$ ON customer$.customer_id = invoice$.customer_id
GROUP BY customer$.customer_id, first_name, last_name
ORDER BY total_spending DESC

6.Soru: Tüm Rock Müzik dinleyicilerinin e-postasını, adını, soyadını ve Türünü döndürmek için sorgu yazın. Listenizi e-posta kolonunu alfabetik olarak sıralayın

SELECT DISTINCT email AS Email,first_name AS FirstName, last_name AS LastName, genre$.name AS Name
FROM customer$
JOIN invoice$ ON invoice$.customer_id = customer$.customer_id
JOIN invoice_line$ ON invoice_line$.invoice_id = invoice$.invoice_id
JOIN track$ ON track$.track_id = invoice_line$.track_id
JOIN genre$ ON genre$.genre_id = track$.genre_id
WHERE genre$.name LIKE 'Rock'
ORDER BY email ASC

7.Soru: Veri kümemizde en çok rock müzik yapan sanatçıları davet edelim. En iyi 10 rock grubunun Sanatçı adını ve toplam parça sayısını döndüren bir sorgu yazın

SELECT TOP 10 artist$.artist_id, artist$.name, COUNT(artist$.artist_id) AS number_of_songs
FROM track$
JOIN album$ ON album$.album_id = track$.album_id
JOIN artist$ ON artist$.artist_id = album$.artist_id
JOIN genre$ ON genre$.genre_id = track$.genre_id
WHERE genre$.name LIKE 'Rock'
GROUP BY artist$.artist_id, artist$.name
ORDER BY number_of_songs DESC

8.Soru:Şarkı uzunluğu ortalama şarkı uzunluğundan daha uzun olan tüm parça adlarını döndürür. Her parça için Adı ve Milisaniyeyi döndürün. En uzun şarkılar ilk sırada olacak şekilde şarkı uzunluğuna göre sıralayın

SELECT name, milliseconds
FROM track$
WHERE milliseconds > (
SELECT AVG(milliseconds) AS avg_track_length
FROM track$ )
ORDER BY milliseconds DESC

9.Soru:Her müşterinin sanatçılara ne kadar harcadığını bulun. Müşteri adını, sanatçı adını ve toplam harcanan değeri döndürmek için bir sorgu yazın

WITH best_selling_artist AS (
SELECT TOP 1 artist$.artist_id AS artist_id, artist$.name AS artist_name, SUM(invoice_line$.unit_price * invoice_line$.quantity) AS total_sales
FROM invoice_line$
JOIN track$ ON track$.track_id = invoice_line$.track_id
JOIN album$ ON album$.album_id = track$.album_id
JOIN artist$ ON artist$.artist_id = album$.artist_id
GROUP BY artist$.artist_id, artist$.name
ORDER BY total_sales DESC
)
SELECT c.customer_id, c.first_name, c.last_name, bsa.artist_name, SUM(il.unit_price * il.quantity) AS amount_spent
FROM invoice$ i
JOIN customer$ c ON c.customer_id = i.customer_id
JOIN invoice_line$ il ON il.invoice_id = i.invoice_id
JOIN track$ t ON t.track_id = il.track_id
JOIN album$ alb ON alb.album_id = t.album_id
JOIN best_selling_artist bsa ON bsa.artist_id = alb.artist_id
GROUP BY c.customer_id, c.first_name, c.last_name, bsa.artist_name
ORDER BY amount_spent DESC

10.Soru:Her ülke için en popüler müzik türünü bulmak istiyoruz. Her ülke için en iyi müzik türü ile birlikte maksimum satın alma sayılarını veren sorguyu yazın

WITH sales_per_country AS (
SELECT COUNT(*) AS purchases_per_genre, customer$.country, genre$.name, genre$.genre_id
FROM invoice_line$
JOIN invoice$ ON invoice$.invoice_id = invoice_line$.invoice_id
JOIN customer$ ON customer$.customer_id = invoice$.customer_id
JOIN track$ ON track$.track_id = invoice_line$.track_id
JOIN genre$ ON genre$.genre_id = track$.genre_id
GROUP BY customer$.country, genre$.name, genre$.genre_id
),
max_genre_per_country AS (
SELECT MAX(purchases_per_genre) AS max_genre_number, country
FROM sales_per_country
GROUP BY country
)
SELECT sales_per_country.*
FROM sales_per_country
JOIN max_genre_per_country ON sales_per_country.country = max_genre_per_country.country
WHERE sales_per_country.purchases_per_genre = max_genre_per_country.max_genre_number;

11.Soru:Her ülke için müziğe en çok para harcayan müşteriyi belirleyen bir sorgu yazın.

WITH Customter_with_country AS (
SELECT customer$.customer_id,
first_name,
last_name,
billing_country,
SUM(total) AS total_spending,
ROW_NUMBER() OVER(PARTITION BY billing_country ORDER BY SUM(total) DESC) AS RowNo
FROM invoice$
JOIN customer$ ON customer$.customer_id = invoice$.customer_id
GROUP BY customer$.customer_id, first_name, last_name, billing_country
)
SELECT * FROM Customter_with_country WHERE RowNo <= 1

Soruların altındaki tablolara aşağıdaki dosyayı indirerek ulaşabilirsiniz.