In this project We'll be working with a modified version of a database called Chinook. The Chinook database is provided as a SQLite database file called chinook.db
The aim of this project analysis is to get a approximate answer or understanding of the following questions or points:
To achive that we are using analysis techniques applied to sql, as we said the database has SQLite engine.
After the analysis we conclude that:
The Chinook database is provided as a SQLite database file called chinook.db . The Chinook database contains information about a fictional digital music shop - kind of like a mini-iTunes store.
The Chinook database contains information about the artists, songs, and albums from the music shop, as well as information on the shop's employees, customers, and the customers purchases. This information is contained in eleven tables. You can find more about Chinook here.
%%capture
%load_ext sql
%sql sqlite:///chinook.db
%%sql
SELECT
name,
type
FROM sqlite_master
WHERE type IN ("table","view");
* sqlite:///chinook.db 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
SELECT
il.invoice_id,
g.name AS genre
FROM invoice_line il
INNER JOIN track t ON il.track_id = t.track_id
INNER JOIN genre g ON t.genre_id = g.genre_id
WHERE
genre LIKE '%Hip%'
OR genre LIKE '%Punk%'
OR genre LIKE '%Pop%'
OR genre LIKE '%Blues%';
* sqlite:///chinook.db Done.
invoice_id | genre |
---|---|
2 | Pop |
2 | Alternative & Punk |
2 | Alternative & Punk |
2 | Blues |
7 | Pop |
7 | Alternative & Punk |
9 | Blues |
11 | Alternative & Punk |
11 | Alternative & Punk |
14 | Alternative & Punk |
17 | Alternative & Punk |
19 | Alternative & Punk |
20 | Blues |
20 | Alternative & Punk |
21 | Alternative & Punk |
25 | Alternative & Punk |
25 | Alternative & Punk |
28 | Alternative & Punk |
29 | Alternative & Punk |
30 | Blues |
30 | Alternative & Punk |
30 | Alternative & Punk |
33 | Hip Hop/Rap |
35 | Pop |
36 | Alternative & Punk |
37 | Alternative & Punk |
38 | Pop |
38 | Alternative & Punk |
40 | Alternative & Punk |
42 | Blues |
43 | Blues |
43 | Alternative & Punk |
45 | Alternative & Punk |
47 | Alternative & Punk |
51 | Alternative & Punk |
51 | Alternative & Punk |
55 | Alternative & Punk |
59 | Blues |
60 | Alternative & Punk |
63 | Pop |
65 | Alternative & Punk |
65 | Alternative & Punk |
66 | Alternative & Punk |
67 | Alternative & Punk |
67 | Pop |
68 | Blues |
68 | Blues |
78 | Alternative & Punk |
81 | Blues |
86 | Alternative & Punk |
87 | Alternative & Punk |
88 | Alternative & Punk |
91 | Alternative & Punk |
91 | Alternative & Punk |
91 | Alternative & Punk |
94 | Alternative & Punk |
95 | Alternative & Punk |
95 | Alternative & Punk |
95 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
99 | Alternative & Punk |
102 | Pop |
102 | Alternative & Punk |
106 | Pop |
106 | Alternative & Punk |
108 | Alternative & Punk |
109 | Alternative & Punk |
110 | Alternative & Punk |
111 | Alternative & Punk |
113 | Alternative & Punk |
117 | Hip Hop/Rap |
117 | Alternative & Punk |
117 | Alternative & Punk |
117 | Hip Hop/Rap |
120 | Alternative & Punk |
124 | Alternative & Punk |
124 | Alternative & Punk |
124 | Alternative & Punk |
125 | Blues |
127 | Alternative & Punk |
129 | Alternative & Punk |
131 | Blues |
134 | Blues |
136 | Hip Hop/Rap |
139 | Alternative & Punk |
140 | Alternative & Punk |
143 | Blues |
143 | Alternative & Punk |
143 | Alternative & Punk |
143 | Alternative & Punk |
144 | Blues |
145 | Pop |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
146 | Alternative & Punk |
147 | Alternative & Punk |
148 | Alternative & Punk |
151 | Pop |
154 | Hip Hop/Rap |
155 | Alternative & Punk |
155 | Alternative & Punk |
158 | Alternative & Punk |
158 | Alternative & Punk |
160 | Pop |
160 | Alternative & Punk |
161 | Alternative & Punk |
161 | Blues |
162 | Alternative & Punk |
162 | Blues |
162 | Hip Hop/Rap |
163 | Alternative & Punk |
163 | Alternative & Punk |
164 | Blues |
165 | Hip Hop/Rap |
165 | Alternative & Punk |
166 | Pop |
166 | Alternative & Punk |
167 | Alternative & Punk |
167 | Alternative & Punk |
168 | Pop |
169 | Alternative & Punk |
169 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
171 | Alternative & Punk |
172 | Alternative & Punk |
172 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
173 | Alternative & Punk |
174 | Blues |
179 | Alternative & Punk |
179 | Blues |
179 | Alternative & Punk |
180 | Alternative & Punk |
181 | Alternative & Punk |
182 | Alternative & Punk |
187 | Alternative & Punk |
192 | Alternative & Punk |
194 | Alternative & Punk |
195 | Alternative & Punk |
195 | Blues |
195 | Blues |
197 | Alternative & Punk |
197 | Pop |
199 | Alternative & Punk |
202 | Alternative & Punk |
203 | Blues |
204 | Alternative & Punk |
206 | Alternative & Punk |
206 | Alternative & Punk |
207 | Alternative & Punk |
208 | Alternative & Punk |
209 | Alternative & Punk |
210 | Alternative & Punk |
211 | Blues |
215 | Alternative & Punk |
216 | Alternative & Punk |
218 | Blues |
218 | Blues |
219 | Blues |
224 | Alternative & Punk |
224 | Alternative & Punk |
224 | Alternative & Punk |
226 | Alternative & Punk |
227 | Alternative & Punk |
227 | Alternative & Punk |
227 | Alternative & Punk |
228 | Alternative & Punk |
235 | Alternative & Punk |
235 | Alternative & Punk |
241 | Alternative & Punk |
241 | Alternative & Punk |
242 | Alternative & Punk |
243 | Pop |
243 | Alternative & Punk |
245 | Alternative & Punk |
246 | Alternative & Punk |
246 | Alternative & Punk |
249 | Alternative & Punk |
250 | Alternative & Punk |
250 | Blues |
252 | Alternative & Punk |
254 | Alternative & Punk |
254 | Alternative & Punk |
255 | Alternative & Punk |
255 | Hip Hop/Rap |
256 | Pop |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
257 | Alternative & Punk |
261 | Alternative & Punk |
265 | Alternative & Punk |
266 | Alternative & Punk |
268 | Alternative & Punk |
268 | Blues |
270 | Alternative & Punk |
272 | Alternative & Punk |
272 | Alternative & Punk |
272 | Alternative & Punk |
272 | Alternative & Punk |
275 | Alternative & Punk |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
276 | Hip Hop/Rap |
278 | Alternative & Punk |
278 | Alternative & Punk |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
282 | Blues |
283 | Blues |
283 | Alternative & Punk |
283 | Alternative & Punk |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
284 | Blues |
285 | Alternative & Punk |
285 | Alternative & Punk |
288 | Alternative & Punk |
288 | Alternative & Punk |
289 | Alternative & Punk |
292 | Pop |
292 | Alternative & Punk |
293 | Alternative & Punk |
294 | Alternative & Punk |
294 | Alternative & Punk |
294 | Alternative & Punk |
294 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
296 | Alternative & Punk |
297 | Pop |
298 | Pop |
298 | Alternative & Punk |
302 | Alternative & Punk |
303 | Blues |
303 | Alternative & Punk |
303 | Alternative & Punk |
304 | Alternative & Punk |
307 | Pop |
314 | Alternative & Punk |
314 | Blues |
314 | Blues |
315 | Pop |
318 | Alternative & Punk |
318 | Blues |
318 | Alternative & Punk |
319 | Hip Hop/Rap |
319 | Alternative & Punk |
321 | Alternative & Punk |
322 | Alternative & Punk |
323 | Alternative & Punk |
325 | Alternative & Punk |
326 | Alternative & Punk |
328 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
330 | Alternative & Punk |
331 | Alternative & Punk |
332 | Alternative & Punk |
332 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
336 | Alternative & Punk |
337 | Alternative & Punk |
337 | Alternative & Punk |
339 | Alternative & Punk |
345 | Alternative & Punk |
345 | Alternative & Punk |
346 | Alternative & Punk |
348 | Alternative & Punk |
350 | Alternative & Punk |
351 | Alternative & Punk |
353 | Alternative & Punk |
353 | Alternative & Punk |
353 | Alternative & Punk |
353 | Alternative & Punk |
354 | Alternative & Punk |
356 | Alternative & Punk |
357 | Alternative & Punk |
357 | Alternative & Punk |
360 | Alternative & Punk |
364 | Pop |
364 | Alternative & Punk |
364 | Alternative & Punk |
365 | Alternative & Punk |
365 | Alternative & Punk |
366 | Alternative & Punk |
366 | Blues |
367 | Alternative & Punk |
370 | Pop |
370 | Alternative & Punk |
370 | Blues |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
372 | Alternative & Punk |
374 | Alternative & Punk |
374 | Alternative & Punk |
375 | Blues |
375 | Blues |
375 | Blues |
375 | Blues |
375 | Blues |
375 | Blues |
375 | Blues |
375 | Blues |
375 | Blues |
377 | Alternative & Punk |
378 | Alternative & Punk |
380 | Alternative & Punk |
380 | Alternative & Punk |
380 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
381 | Alternative & Punk |
383 | Alternative & Punk |
383 | Alternative & Punk |
384 | Alternative & Punk |
384 | Alternative & Punk |
385 | Blues |
385 | Blues |
390 | Alternative & Punk |
393 | Blues |
393 | Alternative & Punk |
395 | Blues |
395 | Pop |
396 | Blues |
396 | Pop |
401 | Alternative & Punk |
402 | Alternative & Punk |
402 | Alternative & Punk |
405 | Alternative & Punk |
408 | Alternative & Punk |
409 | Pop |
409 | Alternative & Punk |
409 | Alternative & Punk |
415 | Alternative & Punk |
419 | Blues |
423 | Alternative & Punk |
423 | Pop |
426 | Alternative & Punk |
428 | Alternative & Punk |
429 | Alternative & Punk |
431 | Pop |
431 | Alternative & Punk |
431 | Alternative & Punk |
431 | Alternative & Punk |
432 | Alternative & Punk |
435 | Alternative & Punk |
435 | Blues |
437 | Alternative & Punk |
438 | Alternative & Punk |
438 | Alternative & Punk |
438 | Alternative & Punk |
442 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
447 | Alternative & Punk |
448 | Blues |
448 | Pop |
450 | Blues |
452 | Blues |
452 | Alternative & Punk |
453 | Blues |
455 | Alternative & Punk |
456 | Alternative & Punk |
456 | Blues |
458 | Pop |
460 | Alternative & Punk |
461 | Alternative & Punk |
462 | Alternative & Punk |
467 | Alternative & Punk |
471 | Alternative & Punk |
474 | Alternative & Punk |
474 | Alternative & Punk |
474 | Alternative & Punk |
474 | Alternative & Punk |
475 | Alternative & Punk |
481 | Alternative & Punk |
482 | Blues |
482 | Alternative & Punk |
484 | Alternative & Punk |
484 | Alternative & Punk |
490 | Alternative & Punk |
490 | Blues |
492 | Alternative & Punk |
493 | Alternative & Punk |
495 | Alternative & Punk |
497 | Hip Hop/Rap |
497 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
499 | Alternative & Punk |
500 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
501 | Alternative & Punk |
503 | Alternative & Punk |
508 | Blues |
508 | Pop |
508 | Pop |
509 | Blues |
509 | Blues |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
510 | Alternative & Punk |
513 | Alternative & Punk |
513 | Alternative & Punk |
517 | Blues |
517 | Blues |
517 | Blues |
519 | Alternative & Punk |
520 | Hip Hop/Rap |
520 | Alternative & Punk |
524 | Blues |
527 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
533 | Alternative & Punk |
534 | Alternative & Punk |
534 | Alternative & Punk |
534 | Blues |
536 | Alternative & Punk |
538 | Alternative & Punk |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
540 | Pop |
543 | Pop |
544 | Alternative & Punk |
544 | Pop |
545 | Alternative & Punk |
546 | Alternative & Punk |
546 | Alternative & Punk |
547 | Blues |
547 | Alternative & Punk |
547 | Pop |
549 | Alternative & Punk |
549 | Hip Hop/Rap |
552 | Alternative & Punk |
552 | Blues |
554 | Alternative & Punk |
554 | Blues |
554 | Blues |
554 | Alternative & Punk |
557 | Alternative & Punk |
557 | Alternative & Punk |
557 | Pop |
557 | Blues |
558 | Alternative & Punk |
558 | Alternative & Punk |
559 | Blues |
559 | Hip Hop/Rap |
560 | Pop |
560 | Alternative & Punk |
561 | Blues |
564 | Alternative & Punk |
566 | Pop |
567 | Alternative & Punk |
572 | Alternative & Punk |
572 | Alternative & Punk |
574 | Blues |
575 | Blues |
577 | Hip Hop/Rap |
579 | Alternative & Punk |
579 | Alternative & Punk |
579 | Alternative & Punk |
580 | Alternative & Punk |
580 | Alternative & Punk |
580 | Pop |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
582 | Blues |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
583 | Pop |
585 | Blues |
586 | Alternative & Punk |
586 | Pop |
586 | Alternative & Punk |
586 | Alternative & Punk |
591 | Alternative & Punk |
591 | Alternative & Punk |
591 | Alternative & Punk |
592 | Alternative & Punk |
593 | Alternative & Punk |
594 | Alternative & Punk |
594 | Alternative & Punk |
595 | Blues |
596 | Alternative & Punk |
601 | Alternative & Punk |
602 | Alternative & Punk |
603 | Pop |
603 | Alternative & Punk |
605 | Alternative & Punk |
609 | Alternative & Punk |
%%sql
WITH
invoice_usa AS
(
SELECT
c.country,
i.invoice_id,
i.total
FROM customer c
INNER JOIN invoice i on c.customer_id = i.customer_id
WHERE c.country = 'USA'
),
top_genres AS
(
SELECT
il.invoice_id,
g.name AS genre
FROM invoice_line il
INNER JOIN track t ON il.track_id = t.track_id
INNER JOIN genre g ON t.genre_id = g.genre_id
WHERE
genre LIKE '%Hip%'
OR genre LIKE '%Punk%'
OR genre LIKE '%Pop%'
OR genre LIKE '%Blues%'
)
SELECT
tg.genre AS Genre,
ROUND(SUM(iu.total), 0) Tracks_Sold,
ROUND((SUM(iu.total) / (SELECT SUM(total) FROM invoice_usa)), 2) AS Percentage,
iu.country AS Country
FROM invoice_usa iu
INNER JOIN top_genres tg ON iu.invoice_id = tg.invoice_id
GROUP BY 1
ORDER BY 2 DESC
* sqlite:///chinook.db Done.
Genre | Tracks_Sold | Percentage | Country |
---|---|---|---|
Alternative & Punk | 1235.0 | 1.19 | USA |
Blues | 453.0 | 0.44 | USA |
Hip Hop/Rap | 366.0 | 0.35 | USA |
Pop | 202.0 | 0.19 | USA |
Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase, let us analyze the purchases of customers belonging to each employee to see if any sales support agent is performing either better or worse than the others.
%%sql
SELECT *
FROM employee
LIMIT 3;
* sqlite:///chinook.db Done.
employee_id | last_name | first_name | title | reports_to | birthdate | hire_date | address | city | state | country | postal_code | phone | fax | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Adams | Andrew | General Manager | None | 1962-02-18 00:00:00 | 2016-08-14 00:00:00 | 11120 Jasper Ave NW | Edmonton | AB | Canada | T5K 2N1 | +1 (780) 428-9482 | +1 (780) 428-3457 | andrew@chinookcorp.com |
2 | Edwards | Nancy | Sales Manager | 1 | 1958-12-08 00:00:00 | 2016-05-01 00:00:00 | 825 8 Ave SW | Calgary | AB | Canada | T2P 2T3 | +1 (403) 262-3443 | +1 (403) 262-3322 | nancy@chinookcorp.com |
3 | Peacock | Jane | Sales Support Agent | 2 | 1973-08-29 00:00:00 | 2017-04-01 00:00:00 | 1111 6 Ave SW | Calgary | AB | Canada | T2P 5M5 | +1 (403) 262-3443 | +1 (403) 262-6712 | jane@chinookcorp.com |
%%sql
WITH
sales_employees AS
(
SELECT
e.employee_id,
e.first_name || " " || e.last_name AS employee_name,
e.title,
e.hire_date,
e.reports_to
FROM employee e
WHERE e.title = 'Sales Support Agent'
),
sales_performance AS
(
SELECT
se.employee_id,
COUNT(c.customer_id) AS customer_number,
COUNT(DISTINCT(c.country)) AS number_countries,
ROUND(SUM(i.total), 2) AS total_sales
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
INNER JOIN sales_employees se ON se.employee_id = c.support_rep_id
GROUP BY 1
ORDER BY 3 DESC
),
sales_managers AS
(
SELECT
e.employee_id,
e.first_name || " " || e.last_name AS employee_name,
e.title,
e.hire_date,
e.reports_to
FROM employee e
WHERE e.title = 'Sales Manager'
)
SELECT
se.employee_name,
se.title,
se.hire_date,
sm.employee_name AS manager,
sp.total_sales,
ROUND((sp.total_sales / (SELECT SUM(total_sales) FROM sales_performance)),2) 'sales_%',
sp.customer_number,
sp.number_countries
FROM sales_performance sp
INNER JOIN sales_employees se ON se.employee_id = sp.employee_id
INNER JOIN sales_managers sm ON sm.employee_id = se.reports_to
ORDER BY 5 DESC, 6 DESC;
* sqlite:///chinook.db Done.
employee_name | title | hire_date | manager | total_sales | sales_% | customer_number | number_countries |
---|---|---|---|---|---|---|---|
Jane Peacock | Sales Support Agent | 2017-04-01 00:00:00 | Nancy Edwards | 1731.51 | 0.37 | 212 | 10 |
Margaret Park | Sales Support Agent | 2017-05-03 00:00:00 | Nancy Edwards | 1584.0 | 0.34 | 214 | 12 |
Steve Johnson | Sales Support Agent | 2017-10-17 00:00:00 | Nancy Edwards | 1393.92 | 0.3 | 188 | 13 |
Jane Peacock has the maximum total sales but we should take into consideration that he is the oldest one on the department. Margaret Park has an extra number of customer & country also Steve Johnson the newest one have customers more countries. Another observation is that the Top 3 Sales support agents are in the same team or at least report to Nancy Edwards whose job to keep them on track and in a healthy competitive environment may be essential.
We are going to make sales analysis for customers from each different country (countries with only one customer, will be grouped in "Other" category). The analysis will include the following points:
%%sql
WITH
customers_info AS
(
SELECT
c.customer_id,
c.country,
i.total,
i.invoice_id
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
GROUP BY 1
ORDER BY 1
),
country_sales AS
(
SELECT
COUNT(ci.customer_id) AS number_of_customers,
COUNT(DISTINCT(ci.invoice_id)) AS number_of_orders,
ROUND(SUM(ci.total), 2) AS total_sales,
CASE
WHEN COUNT(DISTINCT(ci.customer_id)) > 1
THEN ci.country
ELSE 'Other'
END AS country
FROM customers_info ci
GROUP BY ci.country
ORDER BY 1 DESC
),
country_sales_summary AS
(
SELECT
cs.country,
SUM(cs.number_of_customers) AS total_customers,
SUM(cs.number_of_orders) AS total_orders,
SUM(cs.total_sales) AS total_sales,
CASE
WHEN cs.country = 'Other' THEN 1
ELSE 0
END AS sort
FROM country_sales cs
GROUP BY 1
ORDER BY sort ASC, total_sales DESC
)
SELECT
country,
total_customers,
total_orders,
ROUND(total_sales, 2) AS total_sales,
ROUND((total_sales / total_customers), 2) AS average_sales_per_customer,
ROUND((total_sales / total_orders), 2) AS average_order_value
FROM country_sales_summary;
* sqlite:///chinook.db Done.
country | total_customers | total_orders | total_sales | average_sales_per_customer | average_order_value |
---|---|---|---|---|---|
USA | 13 | 13 | 117.81 | 9.06 | 9.06 |
Canada | 8 | 8 | 68.31 | 8.54 | 8.54 |
Germany | 4 | 4 | 42.57 | 10.64 | 10.64 |
Brazil | 5 | 5 | 36.63 | 7.33 | 7.33 |
France | 5 | 5 | 32.67 | 6.53 | 6.53 |
United Kingdom | 3 | 3 | 30.69 | 10.23 | 10.23 |
Czech Republic | 2 | 2 | 25.74 | 12.87 | 12.87 |
India | 2 | 2 | 13.86 | 6.93 | 6.93 |
Portugal | 2 | 2 | 5.94 | 2.97 | 2.97 |
Other | 15 | 15 | 115.83 | 7.72 | 7.72 |
%%sql
WITH
invoice_info AS
(
SELECT
t.album_id,
il.track_id,
il.invoice_id
FROM track t
INNER JOIN invoice_line il ON il.track_id = t.track_id
GROUP BY 3
)
SELECT
whole_album,
count(distinct invoice_id) number_invoices,
cast(count(distinct invoice_id) as Float)/
(SELECT count(distinct invoice_id) FROM invoice) perc_inv
FROM
(
SELECT inv.*,
CASE
WHEN
(
SELECT t2.track_id /* whole album */
FROM track t2
WHERE t2.album_id = inv.album_id
EXCEPT
SELECT il2.track_id /* only purchased tracks */
FROM invoice_line il2
WHERE il2.invoice_id = inv.invoice_id
) IS NULL
AND
(
SELECT il2.track_id
FROM invoice_line il2
WHERE il2.invoice_id = inv.invoice_id
EXCEPT
SELECT t2.track_id
FROM track t2
WHERE t2.album_id = inv.album_id
) IS NULL
THEN 'Yes'
ELSE 'No'
END AS whole_album
FROM invoice_info AS inv
)
GROUP BY whole_album
* sqlite:///chinook.db Done.
whole_album | number_invoices | perc_inv |
---|---|---|
No | 500 | 0.8143322475570033 |
Yes | 114 | 0.18566775244299674 |
%%sql
WITH
play_lists_tracks AS
(
SELECT
pl.playlist_id,
pl.track_id,
t.album_id,
al.artist_id
FROM playlist_track pl
INNER JOIN track t ON t.track_id = pl.track_id
INNER JOIN album al ON al.album_id = t.album_id
ORDER BY al.artist_id
)
SELECT
ar.artist_id AS ARTIST_ID,
ar.name AS ARTIST_NAME,
COUNT(plt.track_id) AS PLAYLIST_APPEARANCE
FROM artist ar
INNER JOIN play_lists_tracks plt ON plt.artist_id = ar.artist_id
GROUP BY ar.artist_id
ORDER BY 3 DESC
LIMIT 3;
* sqlite:///chinook.db Done.
ARTIST_ID | ARTIST_NAME | PLAYLIST_APPEARANCE |
---|---|---|
90 | Iron Maiden | 516 |
150 | U2 | 333 |
50 | Metallica | 296 |
The artist that most appeared in playlistst is Iron Maiden followed by U2 and Metallica.
%%sql
WITH
purchase_info AS
(
SELECT
t.track_id,
CASE
WHEN
(
t.track_id IN (SELECT il.track_id FROM invoice_line il)
)
THEN "Yes"
ELSE "No"
END AS purchased
FROM track t
)
SELECT
pi.purchased,
COUNT(pi.track_id) AS number_of_tracks,
ROUND((CAST(COUNT(pi.track_id) AS FLOAT) * 100) / (SELECT COUNT(*) FROM track t), 2) AS percentage
FROM purchase_info pi
GROUP BY 1
* sqlite:///chinook.db Done.
purchased | number_of_tracks | percentage |
---|---|---|
No | 1697 | 48.44 |
Yes | 1806 | 51.56 |
Only the 51.56 of the tracks have been purchased.
For each genre we will extract the total percentage of tracks in store and the percentage sold.
%%sql
WITH
number_of_tracks_in_store AS
(
SELECT COUNT(t.track_id) AS total
FROM track t
),
number_of_tracks_sold AS
(
SELECT COUNT(il.track_id) AS total
FROM invoice_line il
),
store_tracks AS
(
SELECT
t.genre_id,
g.name AS genre_name,
COUNT(t.track_id) total_tracks_in_store,
ROUND(CAST(COUNT(t.track_id)AS FLOAT)*100/(SELECT COUNT(t.track_id)FROM track t),2) AS percentage_of_tracks_in_store
FROM track t
INNER JOIN genre g ON g.genre_id =t.genre_id
GROUP BY 2
),
store_tracks_purchased AS
(
SELECT
t.genre_id,
g.name AS genre_name,
COUNT(il.track_id) total_tracks_sold,
ROUND(CAST(COUNT(il.track_id)AS FLOAT)*100/(SELECT COUNT(il.track_id)FROM invoice_line il),2) AS percentage_of_tracks_sold
FROM invoice_line il
INNER JOIN track t ON il.track_id =t.track_id
INNER JOIN genre g ON g.genre_id = t.genre_id
GROUP BY 2
)
SELECT
st.genre_name AS genre,
st.percentage_of_tracks_in_store,
stp.percentage_of_tracks_sold
FROM store_tracks st
INNER JOIN store_tracks_purchased stp ON stp.genre_id = st.genre_id
ORDER BY 3 DESC
* sqlite:///chinook.db Done.
genre | percentage_of_tracks_in_store | percentage_of_tracks_sold |
---|---|---|
Rock | 37.03 | 55.39 |
Metal | 10.68 | 13.01 |
Alternative & Punk | 9.48 | 10.34 |
Latin | 16.53 | 3.51 |
R&B/Soul | 1.74 | 3.34 |
Blues | 2.31 | 2.61 |
Jazz | 3.71 | 2.54 |
Alternative | 1.14 | 2.46 |
Easy Listening | 0.69 | 1.56 |
Pop | 1.37 | 1.32 |
Electronica/Dance | 0.86 | 1.16 |
Classical | 2.11 | 0.99 |
Reggae | 1.66 | 0.74 |
Hip Hop/Rap | 1.0 | 0.69 |
Heavy Metal | 0.8 | 0.17 |
Soundtrack | 1.23 | 0.11 |
TV Shows | 2.65 | 0.04 |
Drama | 1.83 | 0.02 |
Firstly, we categorised the tracks by their genre to represent the range of tracks that are available. From the results, we can see that the top 4 genres that have the highest percentage of tracks available are also the same 4 genres that have the highest percentages of sales. These genres are Rock, Metal, Alternative & Punk and Latin. While the rest of the range of tracks in store are pretty reflective of their sales popularity, there are a couple of genres that we should take note of to reduce loss to the Chinook Store.
For answering this question, first let's see the media_type content.
%%sql
SELECT *
FROM media_type
* sqlite:///chinook.db Done.
media_type_id | name |
---|---|
1 | MPEG audio file |
2 | Protected AAC audio file |
3 | Protected MPEG-4 video file |
4 | Purchased AAC audio file |
5 | AAC audio file |
From the previous table we can group the media types as it follows:
Non Protected Files
Protected Files
%%sql
WITH in_store_track AS
(
SELECT COUNT(t.track_id) tracks_in_store,
t.media_type_id,
mt.name
FROM track t
INNER JOIN media_type mt ON mt.media_type_id = t.media_type_id
GROUP BY 3
),
track_sold AS
(
SELECT COUNT(il.track_id) tracks_sold,
t.media_type_id,
mt.name
FROM invoice_line il
INNER JOIN track t ON t.track_id = il.track_id
INNER JOIN media_type mt ON mt.media_type_id = t.media_type_id
GROUP BY 3
),
store_vs_sold AS
(
SELECT it.name media_type,
it.tracks_in_store,
ts.tracks_sold
FROM in_store_track it
INNER JOIN track_sold ts On ts.media_type_id = it.media_type_id
),
protected_vs_nonprotected AS
(
SELECT SUM(ss.tracks_in_store) tracks_in_store,
SUM(ss.tracks_sold) tracks_sold,
CASE
WHEN
(
ss.media_type like "%Protected%"
)THEN "Protected"
ELSE "Non_protected"
END AS Media_type
FROM store_vs_sold ss
GROUP BY 3
)
SELECT pn.Media_type,
pn.tracks_in_store,
ROUND(CAST(pn.tracks_in_store AS FLOAT)*100/(SELECT COUNT(t.track_id) FROM track t),2) PERCENTAGE_TRACKS_in_STORE,
pn.tracks_sold,
ROUND(CAST(pn.tracks_sold AS FLOAT)*100/(SELECT COUNT(il.track_id) FROM invoice_line il),2) PERCENTAGE_TRACKS_SOLD
FROM protected_vs_nonprotected pn
* sqlite:///chinook.db Done.
Media_type | tracks_in_store | PERCENTAGE_TRACKS_in_STORE | tracks_sold | PERCENTAGE_TRACKS_SOLD |
---|---|---|---|---|
Non_protected | 3052 | 87.13 | 4315 | 90.71 |
Protected | 451 | 12.87 | 442 | 9.29 |
From the previous results table, non-protected media type is the most popular in sales with a percentage of 87.13%.
After the analysis we conclude that: