Music-Store-Analysis-SQL/Project-Output(Text Format).txt at main · aakashgoel2002/Music-Store-Analysis-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
Insights:
** For this project, I am only considering the 11 questions to answer to keep it easier for viewers to read everything and get the overall idea. **

--Not Full Outputs are shown to keep everything limited--


Q.1 Who is the Senior Most Employee based on Job Title?
Ans. Andrew Adams

Q.2 Which countries have the most invoices?
Ans. 'USA' with 131 invoices

Q.3 What are top 3 value of Total invoice?
Ans. The top 3 values are as follows:
23.75
19.80
19.80

Q.4 Which city has highest amount of invoices?
Ans. 'Prague' with 273.24 invoices

Q.5 Which customer has spent the most money?
Ans. Customer ID 5 with a total amount of 144.54

Q.6 Write Query to return email, first name, last name of all Rock Genre Music listeners, order by email starting with A?
Ans.
'aaronmitchell@yahoo.ca', 'Aaron', 'Mitchell'
'alero@uol.com.br', 'Alexandre', 'Rocha'
'astrid.gruber@apple.at', 'Astrid', 'Gruber'

Q.7 Write Query that that returns the artist name and total track count of top 10 rock bands?
Ans. AC/DC	18
Aerosmith	15
Audioslave	14
Led Zeppelin	14
Alanis Morissette	13
Alice In Chains	12
Frank Zappa & Captain Beefheart	9
Accept	4

Q.8 Return all the track name that have length longer than the average song length, return name and millisecond, and order by song length as longest first?
Ans. How Many More Times	711836
Advance Romance	677694
Sleeping Village	644571

Q.9 Find how much amount spent by each customer on artists? Write a query to return customer name, artist name and total spent?
Ans.
54	Steve	Murray	AC/DC	17.82
53	Phil	Hughes	AC/DC	10.89
21	Kathy	Chase	AC/DC	10.89

Q.10 Write a query that returns each country along with the top Genre. For countries where the maximum number of purchases is shared return all Genres?
Ans.
1	Argentina	Rock	1	1
18	Australia	Rock	1	1
6	Austria		Rock	1	1

Q.11 Write a query that determines the customer that has spent the most on music for each country. Write a query that returns the country along with the top customer and how much they spent. For countries where the top amount spent is shared, provide all customers who spent this amount?
Ans.
56	Diego	GutiÃ©rrez	Argentina	39.6	1
55	Mark	Taylor	Australia	81.18	1
7	Astrid	Gruber	Austria	69.3	1


Tools/ Skills used in the Project:
- SQL
- SQL Workbench
- CTEs (Common Table Expressions)
- Conditional Statements
- Aggregate Functions
- Window Functions
- Data Manipulation Functions
- Joins