PRACTICAL Data Analytics with SQL

🎵 Try GetSound.AI and get 50% discount using this link app.getsound.ai/vtraiz9dbsfc4f1b
SQL is the number 1 tool that you have to master as a Data Analyst (but also as a Data Scientist and Data Engineer)... In this video I'll show you the ONE SQL PROJECT YOU NEED in your data analytics portfolio. I will go through how to do exploratory data analysis and data cleaning using SQL, as well as how to find insights using different queries methods. This will help you to navigate data analytics in case you are a beginner, or sharpen your skills in case you are advanced and used SQL before. This is a simple project that you can present to recruiters during job interviews to land a job in data...
📁 DATASET
loresowhat.com/sql-app-store-...
🚨 GET MY DATA ANALYTICS COURSE AND BECOME JOB-READY IN 60 DAYS 🚨
loresowhat.com/data-analyst-m...
👨‍💻 LET ME BE YOUR MENTOR
topmate.io/loresowhat
📸 SOCIAL
/ loresowhat
/ loresowhat
📩 Get in touch:
[e-mail]
🔎 CONTENT
0:00 Intro
1:28 Project Context
2:13 Connect to the dataset
4:12 Identify the stakeholder
4:57 Exploratory data analysis
9:28 Finding the insights
15:36 Final recommendations
#datanalytics #sql #datascience

[@loresowhat]

🚀GetSound.AI 50% discount using this link app.getsound.ai/vtraiz9dbsfc4f1b

[@allahjoseph]

Extremely comprehensive with great examples of how to manipulate the dataframe. My guy made this fun to do! Good stuff Lore!!!!

[@loresowhat]

Glad you enjoyed it, really appreciate your comment 😉

[@newenglandnomad9405]

I love this video. I'll tell you why. I've never understood wtaf anyone does with a query result without making a dashboard. I finally understand the break down of how to use documentation in the query, and the purpose of the query without the dashboard. I've never under the practical purpose of finding the avg salary or missing orders sandbox queries, why would my boss ask for that... I like how you did NOT bounce around the csv files. You left that to the viewer to review and study outside the video in our own time. I'll have to review this video quite a few times and I'll practice the queries on my own until i understand every line. Thank you for keeping the video short and on point, i'll be watching it multiple times. Thank you for sharing the files. Thank you soooo much for making this video. I've never been so happy to hit the subscription button as a new hopeful data analyst. I just started a few projects like throwing spaghetti at a wall but I need to clean them up. I understand why my portfolio looks like kids crayon now. I've been using Bard to pose a question and csv file when I can't figure out why my query has mistakes. A million thank you's for making this video. I'm assuming the final list of question and the answers are something you'd present to the stakeholder as an email or Word doc or something.

[@loresowhat]

this comment made my day and I will also share it on my instagram page. thank you so much for taking the time to write such nice words 🙂

[@hopeafang9871]

Lore, you are God’s gift to entry level data analysts like me. Thank you so much for all you do.

[@loresowhat]

these are the comments that make my day, thank you so much!!!!!

[@allahjoseph]

Bro

[@victorbegnini5754]

Well done, Lore! Thanks for sharing this idea/procedure with us - beginners trying to find our spot under the Sun 😄 God bless, man, and all the best!

[@loresowhat]

Glad it was helpful and welcome to the channel Victor 😉

[@DIGI3COM]

Great Practice. Hope you continue doing more of these. Thanks!

[@loresowhat]

More to come 🙂

[@medpire113]

This is the most practical representation of data analytics I've come across. Well done man!

[@loresowhat]

oh wowwww thanks a lot for the comment🙂

[@69nukeee]

Bravissimo Lore, ottimo video! Chiarissimo e dritto al punto ;)

[@loresowhat]

Grazie mille del commento 😉

[@user-cr9gw2nb3p]

You're amazing Lore!! Thank you so much for this!

[@loresowhat]

glad it helps and thank you so much for the kind comment 🙂

[@husseinhadliye2992]

Great video and simple explanation, we expect you more 👍

[@loresowhat]

So glad it was helpful and good to see you in the channel 🙂

[@tamarainwang6362]

Lore!! I can’t thank you enough for these projects! My portfolios looking good! 👍👍

[@loresowhat]

Thank you a lot Tamara 🙏 I’m curious now, share it with me on instagram 😉

[@ltk7309]

How do you save this sql project after you're done??

[@brianbc19]

Really neat video. Very interactive and educational. Keep it up.

[@loresowhat]

Thank you very much brian and welcome to the channel 🙂

[@timianalytics7150]

Bro really responded to almost every comment in the comment section. You're such a gem

[@loresowhat]

Ahah trying my best 🙂

[@Chadwill05]

Great video. Learned a lot thanks!

[@loresowhat]

Glad you enjoyed it!

[@mohammadsadique1131]

wao so much for a fresher in a single video thanks buddy !

[@loresowhat]

Happy to help!

[@mohammadsadique1131]

@@loresowhat please make analysis on health care data set if possible

[@amishskincare12]

Awesome😍 Lore, Please keep making videos on more real time projects for data analysis job

[@loresowhat]

More to come for sure!! I know that you guys like those a lot!!!

[@darialuchko8873]

thank you thank you thank you

[@loresowhat]

You are very welcome🙂

[@adityaraina7727]

thanks!

[@loresowhat]

thanks for the comment 😉

[@Behappy-ft5yc]

Hii brother, a sincere request from your subscriber, can you please post more sql based projects whicha re actually practical in nature. you style of teaching is quite impressive and ofcourse your ideas for project really makes us stand out

[@loresowhat]

this is definitely something that I will do more about, thanks for the tip and the support!!

[@genlyai6918]

Hi Lore. Can you make a video comparing which is better to learn first, in terms of dashboard: Tableau or Power BI, ...

[@loresowhat]

Great suggestion! added to my list!

[@nopfilms249]

Hey Lore, love your videos! When I try to find an overview of the apps ratings, it come up with a syntax error for min, max, and avg. Do you know a way around this?

[@loresowhat]

are you using different tools to run the code? you can show me on instagram in case

[@eltoski01]

This is great thanks. But how do I visualize this ? Do i export the result of each query as a csv then import into power bi and visualize OR is there another way to go about it ?

[@aarshghotra475]

were you able to visualize it?

[@esther-mordecai]

Next time please increase screen resolution or enlarge the font to ensure clear visibility on Mobile Smartphone. Great job. Thanks

[@loresowhat]

will do thanks for the feedback!

[@genlyai6918]

Another video I could use is about tips for data storytelling. Sometimes I have to present my results for people with little to no interest on how I get there, but in a way that would still make sense. Any tips?

[@loresowhat]

another good suggestion too. try using gamification when you tell the story or create a plot that reasonates with your audience and their backgorund more! will try to cover that soon

[@ltk7309]

@@loresowhat When I imported the applestore_description4 (provided by you) to SQLITE, I got this error: SQLITE_ERROR: sqlite3 result code 1: all VALUES must have the same number of terms. How to fix?

[@youtobe12127]

Great video Lore! Is there anyway I can upload this project into my Github account? Straight from SQLlite there? Thanks!

[@loresowhat]

mm I dont think so but it's easy to just copy paste the code there right?

[@muhamadaslankazhim2806]

Hello, i download your file but chinese language turn into symbol after i download it. I've searched for solution but haven't found it. Do you know how to fix this?

[@shawaiz10]

What ediigng software do you use. Really love your animations

[@loresowhat]

mainly adobe👍

[@CharlesDeBarros]

I'm sorry if I missed something! I've watched the video a few times and can't find where the 'rating_count_tot' (13:45) alias was defined. It is in the Top-rated app Window function query. Cheers.

[@loresowhat]

hey I would need to revisit this. isnt that in the appstore table?

[@Mohamed-qt4vc]

Great Work! But aren't those insight might be misleading? As the groups that we are grouping by aren't equal in the count so the average is misleading in this point so the proportion would be more accurate?

[@loresowhat]

Good point! this was a simplified approach but using proportions or percentages can often be a better approach when comparing groups of different sizes. It provides a normalized view of the data, ensuring each group has equal representation in the analysis.

[@timianalytics7150]

I don't think proportion would work still cos the 'ratings' column is a numeric variable. You might have to drop columns with insufficient amount of ratings to avoid bias. But I tell, you, average (mean or median) is your best bet in this scenario

[@-alvin8561]

Hello, Could you possibly provide a link for SQlite? I'm not sure on how I can access it through browser, I only see a downloadable one.

[@loresowhat]

Here it is, let me know if it works sqliteonline.com

[@alecmckay2111]

Had the same problem. Link worked, thanks!

[@lucadelli9308]

Ciao lore! Che ne pensi di creare un server discord riguardo data science? Io sono al secondo anno di economia e big data e troverei molto utile un server discord riguardo ds e penso che magari ci siano anche altre persone interessate.

[@loresowhat]

ciao Luca grazie del messaggio! ci avevo pensato e in realta' lo avrei gia pronto per il lancio... ci ho ripensato all'ultimo perche' non so se ho il tempo di gestire anche quello. scrivimi su instagram se sei interessato ad aiutarmi magari...

[@___________mrivan___________]

Hi! Could you please provide a link to the Kaggle datasets? I want to know the origin of the data

[@loresowhat]

www.kaggle.com/datasets/ramamet4/app-store-apple-data-set-10k-apps

[@RoseShalom-ni6kt]

Please Lore,what can cause my query to take a long execution time... especially when i am doing joins,i am using SMSS 2019

[@loresowhat]

hey it really depends on the query, feel free to text me on instagram to get more details

[@shobhitagnihotri416]

PLEASE MAKE A VIDIO ABOUT USE OF GENRATIVE AI IN DATA ANALYST

[@margaret9247]

This is interesting

[@loresowhat]

absolutely!! just uploaded one on the code interpreter! check it out!

[@loresowhat]

absolutely!! just uploaded one on the code interpreter! check it out!

[@sundusmohsinkhan5498]

Hi Lore, Firstly thanks for the tutorial. I am using SQL Server Management Studio to follow along but I am facing one problem. While importing data from csv files with comma delimiter, the commas used in track_name also get parsed along and the text splits into multiple columns. Can you please help me in this regard. Thanks in advance

[@loresowhat]

Hi good question, This is a common issue when importing CSV files where the data itself contains commas. You can address this by ensuring your data is encapsulated with a text qualifier (like double quotes) during the import process. Here are the steps to handle this in SQL Server Management Studio: Start the import wizard. Select your CSV file as the data source. In the "Choose a Data Source" dialog, click on "Advanced" button. In the "Column Properties" section, find the "Text Qualifier" field and put double quotes (") in it. let me know if this helps otherwise please use sqllite cause that would work for sure

[@asopok]

I am also facing similar challenge using MYSQL workbench.

[@asrarahmad1691]

You can convert csv file into excel and then upload the data

[@technicalsuranii]

@@asrarahmad1691 This worked for me. Thanks

[@HorbachenkoVlad]

​@@loresowhat Hi. I imported CSV files to SQL Server, and I noticed that some values were displayed as question marks. For example, in the track_name column, there is a value of "Shanghai Mahjong". But in my table, it is displayed as "???-??????". For the track_name data type, I chose varchar(MAX). Do you have any idea what might be the issue? Thank you!

[@SujjuOmJadhav-ub9cc]

Can you please share the link to SQLite you are using? Thank you!

[@loresowhat]

here you go sqliteonline.com/

[@AlbouryNdiaye-ro8qz]

Hi Lore. Thank you so much for this project. When I execute the following query in Microsoft SQL Server, it generates an error. I believe it works only in SQLite. Do you know what is the syntax to create the appleStore_description_combined in Microsoft SQL Server? CREATE TABLE appleStore_description_combined AS SELECT * FROM appleStore_description1 UNION ALL SELECT * FROM appleStore_description2 UNION ALL SELECT * FROM appleStore_description3 UNION ALL SELECT * FROM appleStore_description4;

[@loresowhat]

try this SELECT * INTO appleStore_description_combined FROM appleStore_description1 -- Append data from other tables INSERT INTO appleStore_description_combined SELECT * FROM appleStore_description2 INSERT INTO appleStore_description_combined SELECT * FROM appleStore_description3 INSERT INTO appleStore_description_combined SELECT * FROM appleStore_description4;

[@AlbouryNdiaye-ro8qz]

Hi Lore - Your query works in SQL Server. Thank you so much for sharing your knowledge with us. All the best!@@loresowhat

[@loresowhat]

@@AlbouryNdiaye-ro8qz amazing 🤩

[@akiemcameron5303]

So, I have found out the hard way that checking for null values will sometimes bring an empty result so it might be safer to check " ".

[@loresowhat]

good point you raised there thanks!!

[@cgadison]

I am actually using MySQL to attempt this, but when I import the data I keep getting an Unhandled Exception error. 'charmap' codec can't decode byte 0x9d in position 7122: character maps to

[@loresowhat]

can you please try with sqliteonline.com/

[@user-dh4bq9hj1d]

Should we install the sqlite. So so please provide the platform link

[@loresowhat]

no need to install anythig, you can access it online for free

[@pratikkarande6824]

Does certificate matter or experience and skill set ?

[@loresowhat]

the more the better but experience is definitely number 1

[@aydeeeleje5620]

I am not able to download the dataset via Google drive. Anyone know how to get this done because the link doesn't let me

[@loresowhat]

can you text me on ig? I know others are able to download it

[@gozieprince9530]

Thank you for this video, currently learning to becoming a data analyst. I would like you to mentor me, I also live in London

[@loresowhat]

Thanks for the comment! Please text me on instagram 😉

[@vangough11]

Brother, please advise how to learn SQL? I need to really master it

[@loresowhat]

my course is coming soon and will cover that in detail!!!

[@k2ops376]

3rd

[@loresowhat]

and you got a bronze medal here 😉

[@syedfahad4503]

Not first but second😅

[@loresowhat]

silver medal for you 💪

[@asuzordavid2962]

How do we link the SQL to our portfolio

[@loresowhat]

you can create a text document where you summarise the purpose and results of your code and then add the whole code at the end 🙂

[@sureshr1273]

How to document this project to upload it in portfolio?? Make a video of documentation of our project...

[@loresowhat]

hey did you check my video on the portfolio that actaully works? I speak exaclty about how to present project

[@sureshr1273]

@@loresowhat yeahh i did...

[@vishwasmaliwal1500]

hey did you documanted this project?

[@loresowhat]

@@sureshr1273 curious to see if you manage to document it ?

[@TheGladiator123]

Connect SQL with power bi

[@loresowhat]

yep that's a good way to go!

[@TheGladiator123]

@@loresowhat Thanx 😊

[@akiemcameron5303]

how should you address data quality issues?

[@loresowhat]

I just uploaded a new video talking about that and data cleaning, that should help 🙂

[@akiemcameron5303]

Thank you I saw it, you are the best @@loresowhat

[@yogeshindulkar320]

WHERE THIS RATING_COUNT_TOT COMES ? PLEASE EXPLAIN

[@loresowhat]

can you expand on what is not clear please?

[@dhinakarans8602]

can i get the whole data in one file

[@loresowhat]

I dont have that but should be an easy task if you have the separate ones

[@puloii7800]

What IDE is this?

[@loresowhat]

Check video description and intro of the video, I explain all the setups and info there 🙂

[@martingarcia3999]

more more more moree... plz fix it. thank you

[@loresowhat]

what else you would like to see? still with SQL?

[@Learn_lane]

11.23

[@loresowhat]

what's up?

[@abhidipsen3674]

unable to load the data in mysql . Please help

[@loresowhat]

could you use the same tool I use in the video and see if that works?

[@abhidipsen3674]

@@loresowhat Bit of cleaning and modifications made in query and imported the data through cmd prompt

[@Srv_Sngh]

@@loresowhatwhat’s the name of this tool.

[@dolayesoul]

First

[@loresowhat]

Ahahaha love that you guys are making this a real challenge 😂✨

[@Maison1990]

EZPZ

[@loresowhat]

that was the goal ahah

[@HorbachenkoVlad]

I don't really understand this "distribution of app prices" calculation. And there is no explanation in the video. And on top of that, there is not a single comment about this:)

[@loresowhat]

can you expand on this? what is it that is not clear?