%%capture
%load_ext sql
%sql sqlite:///chinook.db
%%sql
SELECT
name,
type
FROM sqlite_master
WHERE type in ("table", "view");
%%sql
SELECT * FROM customer
LIMIT 5;
%%sql
SELECT *
FROM invoice
LIMIT 5;
%%sql
WITH total_sales_usa AS
(
SELECT SUM(quantity)
FROM invoice_line
INNER JOIN invoice ON invoice_line.invoice_id = invoice.invoice_id
WHERE invoice.billing_country = "USA"
)
SELECT g.name, SUM(il.quantity) total_sales, (CAST(SUM(il.quantity) AS FLOAT)/(SELECT * FROM total_sales_usa)) percentage_sales
FROM invoice i
INNER JOIN invoice_line il ON i.invoice_id = il.invoice_id
INNER JOIN track t ON il.track_id = t.track_id
INNER JOIN genre g ON t.genre_id = g.genre_id
WHERE i.billing_country = "USA"
GROUP BY g.genre_id
ORDER BY 2 DESC;
2nd) Alternative and Punk
5th) Blues
8th) Pop
1) Red Tone
2) Slim Jim Bites
3) Meteor and the Girls
%%sql
SELECT DISTINCT title
FROM employee;
%%sql
SELECT e.employee_id employee_id,
e.first_name || " " || e.last_name employee_name,
e.hire_date employee_hire_date,
e.country employee_country,
TOTAL(i.total) total_sales
FROM employee e
LEFT JOIN customer c ON e.employee_id = c.support_rep_id
INNER JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY 1
ORDER BY 5 DESC;
We can see that Jane had the highest sales, but she is also the oldest employee
We can also see that Steve has a sales amount close to Margaret despite the fact that he was hired 5 months after her.
%%sql
WITH sales_by_country AS
(
SELECT c.country country,
COUNT(DISTINCT(c.customer_id)) total_customers,
TOTAL(i.total) total_sales,
(TOTAL(i.total)/COUNT(DISTINCT(c.customer_id))) average_sales_pc,
AVG(i.total) average_order_value,
0 country_or_other
FROM customer c
INNER JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY c.country
HAVING total_customers > 1
UNION
SELECT country,
SUM(total_customers) total_customers,
TOTAL(total_sales) total_sales,
TOTAL(total_sales)/SUM(total_customers) average_sales_pc,
AVG(average_order_value) average_order_value,
country_or_other
FROM(
SELECT "Other" country,
COUNT(DISTINCT(c.customer_id)) total_customers,
TOTAL(i.total) total_sales,
(TOTAL(i.total)/COUNT(DISTINCT(c.customer_id))) average_sales_pc,
AVG(i.total) average_order_value,
1 country_or_other
FROM customer c
INNER JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY c.country
HAVING total_customers = 1
)
GROUP BY country
ORDER BY country_or_other ASC, total_sales DESC
)
SELECT country,
total_customers,
total_sales,
average_sales_pc,
average_order_value
FROM sales_by_country;
%%sql
WITH
album_distinct_tracks AS
(
SELECT a.album_id album_id,
a.title title,
COUNT(DISTINCT(t.track_id)) album_track_num
FROM album a
INNER JOIN track t ON a.album_id = t.album_id
GROUP BY 1
),
album_purchase_invoice AS
(
SELECT il.invoice_id invoice_id,
COUNT(DISTINCT(t.album_id)) invoice_album_count,
COUNT(DISTINCT(t.track_id)) invoice_track_count,
adt.album_track_num,
t.album_id album_id,
a.title album_title
FROM invoice_line il
INNER JOIN track t ON il.track_id = t.track_id
INNER JOIN album a ON t.album_id = a.album_id
INNER JOIN album_distinct_tracks adt ON adt.album_id = a.album_id
GROUP BY 1
HAVING ((invoice_album_count = 1) AND (invoice_track_count = adt.album_track_num))
),
total_invoice_count AS
(
SELECT COUNT(i.invoice_id)
FROM invoice i
),
album_invoice_count AS
(
SELECT COUNT(api.invoice_id)
FROM album_purchase_invoice api
),
non_album_invoice_count AS
(
SELECT album_invoice_count.*
FROM album_invoice_count
)
SELECT COUNT(api.invoice_id) album_invoices,
CAST(COUNT(api.invoice_id) AS FLOAT)/(SELECT * FROM total_invoice_count) percentage_album_invoices,
(SELECT * FROM total_invoice_count) - COUNT(api.invoice_id) non_album_invoices,
(SELECT * FROM total_invoice_count) total_invoice_count,
(((SELECT * FROM total_invoice_count) - COUNT(api.invoice_id))/CAST((SELECT * FROM total_invoice_count) AS FLOAT)) percentage_non_album_invoices
FROM album_purchase_invoice api;