%%capture
%load_ext sql
%sql sqlite:///chinook.db
'Connected: None@chinook.db'
%%sql
SELECT
name,
type
FROM sqlite_master
WHERE type IN ("table","view");
Done.
name | type |
---|---|
album | table |
artist | table |
customer | table |
employee | table |
genre | table |
invoice | table |
invoice_line | table |
media_type | table |
playlist | table |
playlist_track | table |
track | table |
%%sql
/* This table below contains genre and track info */
WITH T1 AS
(SELECT
g.name
,t.track_id
FROM
genre as g
LEFT JOIN Track as t
ON T.genre_id = g.genre_id),
/* This table below contains genre and track and invoice info */
T2 AS
(SELECT
T1.*
,inv_l.invoice_id
,quantity as qty
,billing_country as b_c
FROM T1
LEFT JOIN invoice_line as inv_l
ON inv_l.track_id = T1.track_id
LEFT JOIN invoice
ON invoice.invoice_id = inv_l.invoice_id),
T3 AS
(SELECT
b_c,
name
,SUM(qty) as total_qty
FROM T2
GROUP BY name,b_c
HAVING b_c = 'USA'),
T4 AS
(SELECT b_c
,SUM(total_qty) as SUM_QTY
FROM t3
GROUP BY b_c)
SELECT T3.*
,CAST(total_qty as float) *100 / SUM_QTY AS PERC
FROM T3
LEFT JOIN T4
ON T3.b_c = T4.b_c
ORDER BY total_qty DESC
Done.
b_c | name | total_qty | PERC |
---|---|---|---|
USA | Rock | 561 | 53.37773549000951 |
USA | Alternative & Punk | 130 | 12.369172216936251 |
USA | Metal | 124 | 11.798287345385347 |
USA | R&B/Soul | 53 | 5.042816365366318 |
USA | Blues | 36 | 3.4253092293054235 |
USA | Alternative | 35 | 3.330161750713606 |
USA | Latin | 22 | 2.093244529019981 |
USA | Pop | 22 | 2.093244529019981 |
USA | Hip Hop/Rap | 20 | 1.9029495718363463 |
USA | Jazz | 14 | 1.3320647002854424 |
USA | Easy Listening | 13 | 1.236917221693625 |
USA | Reggae | 6 | 0.570884871550904 |
USA | Electronica/Dance | 5 | 0.47573739295908657 |
USA | Classical | 4 | 0.38058991436726924 |
USA | Heavy Metal | 3 | 0.285442435775452 |
USA | Soundtrack | 2 | 0.19029495718363462 |
USA | TV Shows | 1 | 0.09514747859181731 |
PRINT 'ROCK being the most sold genre IN USA Recommendations Red Tone Punk Meteor and the Girls Pop Slim Jim Bites Blues''
File "<ipython-input-87-813d162a25f2>", line 1 PRINT 'ROCK being the most sold genre IN USA Recommendations Red Tone Punk Meteor and the Girls Pop Slim Jim Bites Blues'' ^ SyntaxError: invalid syntax
%%capture
%load_ext sql
%sql sqlite:///chinook.db
%%sql
WITH T1 AS
(SELECT customer_id
,SUM(total) total_sales
FROM invoice
GROUP BY customer_id),
T2 AS
(SELECT T1.customer_id
,support_rep_id
,total_sales
FROM T1
LEFT JOIN customer
ON Customer.customer_id = T1.customer_id),
T3 AS
(SELECT
(first_name || ' ' || last_name) as emp_name
,employee_id
,hire_date
FROM employee),
T4 AS
(SELECT
emp_name
,hire_date
,ROUND(SUM(total_sales),2) as T_S
FROM T2
LEFT JOIN T3
ON T2.support_rep_id = T3.employee_id
GROUP BY emp_name,hire_date)
SELECT emp_name
,hire_date
,T_S
,ROUND(CAST(T_S as float)*100/(SELECT SUM(T_S) FROM T4),2) AS perc_sales
FROM T4
Done.
emp_name | hire_date | T_S | perc_sales |
---|---|---|---|
Jane Peacock | 2017-04-01 00:00:00 | 1731.51 | 36.77 |
Margaret Park | 2017-05-03 00:00:00 | 1584.0 | 33.63 |
Steve Johnson | 2017-10-17 00:00:00 | 1393.92 | 29.6 |
%%sql
WITH T1 AS
(SELECT billing_country as b_c
,COUNT(DISTINCT customer_id) AS Num_Cust
,SUM(total) AS total_sales
,COUNT(invoice_id) as num_invoices
FROM invoice
GROUP BY Billing_country),
T2 AS
(SELECT *
,CASE WHEN Num_Cust = 1 THEN 'OTHER'
ELSE b_c END AS Country_Name
FROM T1),
T3 AS
(SELECT Country_Name,
SUM(Num_Cust) AS number_of_customers,
SUM(total_sales) AS total_S,
SUM(num_invoices) AS number_of_orders
FROM T2
GROUP BY country_name),
T4 AS
(SELECT Country_Name
,CASE WHEN Country_Name = 'OTHER' THEN 0
ELSE 1 END AS rnk
,number_of_customers
,ROUND(total_S,2) AS T_S
,ROUND(CAST(total_S as float)/number_of_customers,2) AS avg_val_salepercust
,ROUND(CAST(total_S as float)/number_of_orders,2) AS avg_val_perorder
FROM T3)
SELECT Country_Name
,number_of_customers
,T_S
,avg_val_salepercust
,avg_val_perorder
FROM T4
ORDER BY rnk DESC,T_S DESC
Done.
Country_Name | number_of_customers | T_S | avg_val_salepercust | avg_val_perorder |
---|---|---|---|---|
USA | 13 | 1040.49 | 80.04 | 7.94 |
Canada | 8 | 535.59 | 66.95 | 7.05 |
Brazil | 5 | 427.68 | 85.54 | 7.01 |
France | 5 | 389.07 | 77.81 | 7.78 |
Germany | 4 | 334.62 | 83.66 | 8.16 |
Czech Republic | 2 | 273.24 | 136.62 | 9.11 |
United Kingdom | 3 | 245.52 | 81.84 | 8.77 |
Portugal | 2 | 185.13 | 92.57 | 6.38 |
India | 2 | 183.15 | 91.57 | 8.72 |
OTHER | 15 | 1094.94 | 73.0 | 7.45 |
%%sql
WITH T1 AS
(select title as album_name
,COUNT(DISTINCT name) as track_count
from album
LEFT JOIN track
ON track.album_id = album.album_id
GROUP BY title),
T2 AS
(select title as album_name
,track_id
from album
LEFT JOIN track
ON track.album_id = album.album_id),
T3 AS
(SELECT invoice_id
,album_name
,COUNT(DISTINCT i_l.track_id) as num_track_purch
FROM invoice_line as i_l
LEFT JOIN T2
ON T2.track_id = i_l.track_id
GROUP BY invoice_id,album_name),
T4 AS
(SELECT T3.*
,track_count
,CASE WHEN num_track_purch = track_count THEN 'Y'
ELSE 'N' END AS Y_N
FROM T3
LEFT JOIN T1
ON T1.album_name = T3.album_name),
T5 AS
(SELECT invoice_id
,Y_N
FROM T4
GROUP BY invoice_id,Y_N),
T6 AS
(SELECT invoice_id as qual
FROM T5
WHERE Y_N = 'Y'
EXCEPT
SELECT invoice_id
FROM T5
WHERE Y_N = 'N')
SELECT (SELECT COUNT(DISTINCT invoice_id) FROM invoice_line) AS total_invoices
,COUNT(qual) as Num_of_inv_full_alb_pur
,CAST(COUNT(qual) as float)*100/(SELECT COUNT(DISTINCT invoice_id) FROM invoice_line) AS perc
FROM t6
Done.
total_invoices | Num_of_inv_full_alb_pur | perc |
---|---|---|
614 | 114 | 18.566775244299674 |