Udacity.com Business Analytics Nanodegree

SQL for Data Analysis | Query a Digital Music Store Database

Per ottenere la nanodegree in Business Analytics su Udacity.com è necessario inviare 4 progetti più una revisione del proprio curriculum vitae:

• Welcome to nanodegree program – working with data
• Introdution to data – Analyze NYSE Data
• SQL for Data Analysis – Query a Digital Music Store Database
• Data Visualization – Data Visualization project

Con il documento allegato ho passato il terzo step. Puoi usare questo file come guida inserendo le tue idee, ricorda che Udacity.com verifica l’unicità dei progetti.

QUERIES

/* Query 1 */
SELECT e.Firstname || ‘ ‘ || e.lastname as “Agent”, SUM(i.total) as “Total in USD”
FROM invoice i
JOIN Customer c
ON i.CustomerId=c.CustomerId
JOIN Employee e
ON c.SupportRepId=e.EmployeeId
where date(i.InvoiceDate) BETWEEN ‘2010-01-01’ and ‘2010-12-31’
group by Agent;

/* Query 2 */
SELECT m.Name as “Type of Media”, count(*) as “Number of Tracks”, round(avg(t.UnitPrice),2) as “Avg Unit Price”
FROM Track t
join MediaType m
on t.MediaTypeId=m.MediaTypeId
group by m.name;

/* Query 3 */
SELECT c.Firstname || ‘ ‘ || c.LastName as “Customer”, sum(i.total) as “Total”
FROM invoice i
join Customer c
ON i.CustomerId=c.CustomerId
group by Customer
order by total DESC
limit 10;

/* Query 4 */
SELECT strftime(‘%Y’, i.InvoiceDate)as Year, i.billingcountry as Country, g.name as “Genre”, sum(inv.Quantity*inv.UnitPrice) as “gTotal”
FROM invoice i
join invoiceline inv
ON i.InvoiceId=inv.InvoiceId
JOIN track t
ON inv.TrackId = t.TrackId
JOIN genre g
ON t.GenreId = g.GenreId
where year = “2010” and Country = “USA”
group by Genre
order by gTotal DESC;