Query.sql

/* Q1: Who is the senior most employee based on job title? */

select title, first_name, last_name,levels 
from employee
order by levels desc
limit 1;

/* Q2: Which countries have the most Invoices? */

select billing_country, count(invoice_id) as most_no_invoices 
from invoice
group by billing_country 
order by most_no_invoices desc;

/* Q3: What are top 3 values of total invoice? */
select total 
from invoice
order by total desc
limit 3;

/* Q4: Which city has the best customers? 
We would like to throw a promotional Music Festival in the city we made the most money. 
Write a query that returns one city that has the highest sum of invoice totals. 
Return both the city name & sum of all invoice totals */

select billing_city, sum(total) as total_invoices 
from invoice
group by billing_city
order by total_invoices desc
limit 1;


/* Q5: Who is the best customer? 
The customer who has spent the most money will be declared the best customer. 
Write a query that returns the person who has spent the most money.*/

SELECT customer.customer_id, first_name, last_name, SUM(invoice.total) AS total_spending
FROM customer
JOIN invoice ON customer.customer_id = invoice.customer_id
GROUP BY customer.customer_id
order by total_spending desc
limit 1;

/* Q1: Write query to return the email, first name, last name, & Genre of all Rock Music listeners. 
Return your list ordered alphabetically by email starting with A. */

select distinct email, first_name, last_name 
from customer
join invoice on customer.customer_id = invoice.customer_id
join invoice_line on invoice.invoice_id = invoice_line.invoice_id
	where track_id IN(
	select track_id from track 
	join genre on track.genre_id = genre.genre_id
	where genre.name like 'Rock'
	)
order by email;


select distinct email, first_name, last_name 
from customer
join invoice on customer.customer_id = invoice.customer_id
join invoice_line on invoice.invoice_id = invoice_line.invoice_id
join track on invoice_line.track_id = track.track_id 
join genre on track.genre_id = genre.genre_id 
where genre.name ='Rock'
order by email;

/* Q2: Let's invite the artists who have written the most rock music in our dataset. 
Write a query that returns the Artist name and total track count of the top 10 rock bands. */

SELECT artist.artist_id, artist.name, COUNT(artist.artist_id) as trackCount
from track
join album on track.album_id = album.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
order by trackCount desc
limit 10;

SELECT artist.artist_id, artist.name, COUNT(track.track_id) as trackCount
from track
join album on track.album_id = album.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
order by trackCount desc
limit 10;


/* Q3: Return all the track names that have a song length longer than the average song length. 
Return the Name and Milliseconds for each track. 
Order by the song length with the longest songs listed first. */

select name, milliseconds 
from track
where milliseconds > (
	select avg(milliseconds) as avg_song_len from track)
order by milliseconds desc;

/* Q1: Find how much amount spent by each customer on artists? 
Write a query to return customer name, artist name and total spent */