Going fast! #DataquestChallenge Premium Annual Offer:
500 get 50% & the next 1000 get 40% off.
GET OFFER CODE

Guided Project_ Answering Business Questions using SQL

Hi everyone,

Here is my another guided project using SQL. This time I finally managed to insert some graphs into it, thanks to a valuable advice of @WilfriedF (well, Wilfried, it’s already the second time when your suggestions help me to significantly improve my projects, many thanks! :star_struck:)

After my first SQL project, the queries of this second one seemed not so easy at all. Then, this time I tried to follow more strictly the SQL style guide, so I’m more happy with how my code looks now, much tidier.

This time a surprising finding for me was that almost half of the Chinook store’s unique tracks were never sold. Also, that there are artists who are present in many playlists, but whose tracks are related to one of the least popular genre. Chinook definitely has to do something to improve this situation and to increase their profit. Well, I remember that the store is actually fictional, but I’m impressed anyway! :grinning:

As usual, any feedback and positive criticism from you is very welcome. Please let me know whether you agree with my findings, what can be improved and if there’s something I’m missing in this project.

Looking forward to seeing your comments and suggestons.

Thanks a lot!

Answering Business Questions using SQL.ipynb (260.6 KB)

Click here to view the jupyter notebook file in a new tab

8 Likes

Hi @Elena_Kosourova,congratultions for the well organized and presented project. I have gone through your project and I have learned a lot. Inserting graph while working on a project using SQL to be honest I didn’t see that coming, but now I am getting back to my first project " analyzing world factbook" to see if I can insert some graphs. Congratulations! once more for the good work.

3 Likes

Thank you @brayanopiyo18 for you positive feedback! Well, I actually have to do the same thing: to return to my previous SQL project and insert some graphs also there :grinning: I just didn’t know yet that it was so easy, and before I had found only some complicated ways to do it on Google.

1 Like

Hi, @Elena_Kosourova. I have learned a few things from your project including the importance of adding data visualizations to a data analysis project – graphs make it easier to communicate insights well. Plus, they add some aesthetic value to the overall project.

I also saw how useful it was to include and visualize the number of customers along with the sales value for the sales reps. It provided more insight into the employees’ performance.

I’ll be sure to apply some of the lessons learned in my projects.

Congratulations on a job well done.

1 Like

Hi @Yeside,

That’s great, I’m happy that my project was helpful and that you found some good ideas there! Well, in the instructions to this project the graphs were not mentioned, but I decided that with them it will be much more beautiful and informative :star_struck: And yes, it’s always the best way to communicate the results to the readers by means of visualizations.

Happy learning and happy holidays! :christmas_tree:

2 Likes

Thank you. Happy holidays!

1 Like

Hi @Elena_Kosourova,

Your project is great and is serving as a benchmark for me to learn how to write SQL more effectively. I am currently in the middle of this project and noticed something that I want to share since you have it in your project too. This is for Step 4 of the mission (Analyzing Employee Sales Performance) and cell 5 in your project.

I also chose to include the number of customers that each employee has as a measure of employee performance as an other metric of performance. I was getting two different results for this as I was experimenting on my own and I think I found out why. Consider this code:

The first starts at customer and then inner joins employee. The second also starts at customer but inner joins invoice in addition to employee. The aggregate calculation COUNT(c.customer_id) AS total_customers is the same but returning different results. I think this is because by inner joining invoice you are actually returning the amount of invoices that each customer has (aka how many times they bought something). So in other words Jane Peacock has 21 customers who made a total of 212 purchases between them. Either way the pattern is the same.

Is my thinking correct on this?

Thanks!

3 Likes

Hi @gosaints,

Very good observation indeed! To avoid confusion, it’s better to rename the resulting column in “num_of_visits” or something like this. Let’s say, in the second case (your code cell [95]) we’re actually counting the number of times the customers bought something, not a physical number of customers. So yes, you’re absolutely right! And thank you for ponting that out, it’s an interesting consideration to take into account.

2 Likes

Hello Elena, your work is great like usual, reading your work has broadened my mind. But there is 1 thing I would like to ask how did you create a schema diagram? Thank you a lot .

2 Likes

Hi @TrnTrMi,

Glad that my work was useful! :blush: As for the schema diagram, you should do the following:

  • Create a new markdown cell
  • Copy-paste there the following text:
![Chinook schema diagram](https://s3.amazonaws.com/dq-content/189/chinook-schema.svg)

Side note: don’t worry that it looks strange above. I wrote it in a code mode, because if I write it in a markdown mode, it will be automatically rendered as a link, and you won’t see the actual text. You should write it in a normal markdown mode, of course.

  • Run the cell.
2 Likes

Thank you so much Elena for your kindness <3

1 Like

awesome work. Insightful to know i can create graphs with my results. However, I have a question

Is it possible to work with dates in SQL to generate ages like in python. (perform calculations with dates)?

1 Like

Hi @frimstheshrimp ,

Thank you for your appreciation! :smiling_face_with_three_hearts: Indeed, combining SQL with pandas was a great idea, especially because of the visualization power.

As for your question - yes, it’s definitely possible! You can find numerous techniques in this tutorial. However, remember that the syntax of these operations can be different if you’re using another SQL flavour rather than SQLite.

1 Like