%%capture
%load_ext sql
%sql sqlite:///chinook.db
%%sql
SELECT
name,
type
FROM sqlite_master
WHERE type in ("table", "view");
Done.
%%sql
WITH total_track_sold AS
(
SELECT il.* FROM invoice AS i
JOIN invoice_line AS il ON il.invoice_id = i.invoice_id
JOIN track AS t ON t.track_id = il.track_id
WHERE billing_country = "USA"
)
SELECT
g.name AS genre_name,
COUNT(*) AS tracks_sold,
ROUND(
(CAST(COUNT(*) AS float) / (SELECT Count(*) FROM total_track_sold)
), 2) * 100 AS percentage
FROM invoice AS i
JOIN invoice_line AS il ON il.invoice_id = i.invoice_id
JOIN track AS t ON t.track_id = il.track_id
JOIN genre AS g on g.genre_id = t.genre_id
WHERE billing_country = "USA"
GROUP BY g.name
ORDER BY percentage DESC
LIMIT 5;
Done.
%%sql
SELECT
e.first_name || " " || e.last_name AS employee_name,
hire_date,
"$" || Round(SUM(total), 2) AS total_sales
FROM employee AS e
JOIN customer AS c ON c.support_rep_id = e.employee_id
JOIN invoice AS i ON i.customer_id = c.customer_id
GROUP BY e.employee_id
Done.
%%sql
WITH
num_customers AS
(
SELECT
country,
COUNT(*) as num_customers
FROM customer
GROUP BY country
),
total_sales AS
(
SELECT
country,
ROUND(SUM(total), 2) as total_sales
FROM customer AS c
JOIN invoice AS i ON c.customer_id = i.customer_id
GROUP BY country
),
avg_sales AS
(
SELECT
ts.country,
ts.total_sales / nc.num_customers as avg_sales
FROM total_sales AS ts
JOIN num_customers AS nc ON ts.country = nc.country
),
total_orders AS
(
SELECT
country,
COUNT(i.invoice_id) as total_orders
FROM customer AS c
JOIN invoice AS i ON c.customer_id = i.customer_id
GROUP BY country
),
avg_order_value AS
(
SELECT
ts.country,
ROUND(ts.total_sales / t.total_orders, 2) as avg_order_value
FROM total_sales AS ts
JOIN total_orders AS t ON ts.country = t.country
)
SELECT
nc.country,
nc.num_customers,
ts.total_sales,
a.avg_sales,
aov.avg_order_value
FROM num_customers AS nc
JOIN total_sales AS ts ON nc.country = ts.country
JOIN avg_sales AS a ON a.country = nc.country
JOIN avg_order_value AS aov ON aov.country = a.country
WHERE nc.num_customers > 1
ORDER BY total_sales DESC
Done.
%%sql
WITH invoice_first_track AS
(
SELECT
il.invoice_id AS invoice_id,
MIN(il.track_id) AS first_track_id
FROM invoice_line AS il
GROUP BY 1
)
SELECT
album_purchase,
COUNT(invoice_id) AS number_of_invoices,
CAST(count(invoice_id) AS FLOAT) / (SELECT COUNT(*) FROM invoice) AS percent
FROM
(
SELECT
ifs.*,
CASE
WHEN
(
SELECT t.track_id FROM track AS t
WHERE t.album_id = (
SELECT t2.album_id FROM track AS t2
WHERE t2.track_id = ifs.first_track_id
)
EXCEPT
SELECT il2.track_id FROM invoice_line AS il2
WHERE il2.invoice_id = ifs.invoice_id
) IS NULL
AND
(
SELECT il2.track_id FROM invoice_line AS il2
WHERE il2.invoice_id = ifs.invoice_id
EXCEPT
SELECT t.track_id FROM track AS t
WHERE t.album_id = (
SELECT t2.album_id FROM track AS t2
WHERE t2.track_id = ifs.first_track_id
)
) IS NULL
THEN "yes"
ELSE "no"
END AS "album_purchase"
FROM invoice_first_track AS ifs
)
GROUP BY album_purchase;
Done.