A Data Analysis Project — Smart Phones Data Analysis.

Author(s): Kamireddy Mahendra

Originally published on Towards AI.

The more we immerse ourselves in the hands-on process of analyzing data, the more we develop our expertise in data analytics.

Here is the data analysis Project of smartphone data that I have collected from the Kaggle(Data Set) website as it is the most recently updated data to have more hands-on with great insights that we can extract and understand the business.

In the given data set, we have different information regarding different smartphone details. Here are the columns that convey several pieces of information as mentioned below.

i. Brands names of smartphones and their models.
ii. Prices of smartphones, rating, 5g, and ir blast details.
iii. Smartphone’s processor brands, number of cores that each has, and their processing speed.
iv. Battery Capacity, RAM capacity, fast charging or not, and their internal memory details.
v. Smartphone screen sizes, refresh rate, number of rear cameras, Operating System, and resolution details are given.

This is the basic information that a given data set coneys. Now we are going to investigate/analyze and extract many insights from this data. There are many insights that we can extract, Here are a few insights mentioned below that help businesses to work on sales and any product upgrading as needed to develop business.

Investigating/Data to be extracted for the following Questions.

1. Find the number of brands, and models in a store and, the number of models per brand.
2. total price of each brand’s mobiles, models, and also brands and their models, and check with each brand’s mobile price using the window function.
3. Top 3 Highest rating brands and models.
4. mobiles that do not have 5g or have ir blaster.
5. Number of processor brands are available? and number of processors per brand.
6. number of cores per model. rank them by the number of cores.
7. Highest processor speed as per model and brand.
8. Top 5 highest battery capacity models.
9. list the top 5 brands that have the highest RAM capacity and internal memory.
10. List the top 10 models that have less screen size.
11. rank brands based on their refresh rate.
12. which model and brand has the highest number of rear cameras?
13. list the number of OS types by each brand.
14. list the top 5 models that have the highest resolution.
15. rank the brands based on the highest resolution, lowest refresh rate, and highest internal memory.
16. list down the processor speed by processor brand rank them and return the top 3 brands.

Here is My YouTube channel. Subscribe To get more information and to catch my latest updates Instantly.

1. Find the number of brands, and models in a store and, the number of models per brand. check the number of models with rolling sum using the window function.

-----1. Find the number of brands, and models in a store and, the number of models per brand. 
-----check the number of models with rolling sum using window function.
select count(distinct brand_name) Number_of_brands from SmartPhones_data
select count(distinct model) number_of_models from SmartPhones_data

with cte as(
select brand_name, count(distinct model) number_of_models from SmartPhones_data 
group by brand_name)
select c.*, sum(c.number_of_models) over(order by number_of_models desc, brand_name asc) as total_number_of_models from cte c

2. total price of each brand’s mobiles, models, and also brands and their models, and check with each brand’s mobile price using the window function.

----2. 2. total price of each brand's mobiles, models, and also brands and their models, and check with each brand's mobile price using the window function.
select brand_name, sum(price) total_cost from SmartPhones_data group by brand_name order by 1 asc
select model, sum(price) total_cost from SmartPhones_data group by model order by 2 desc
select brand_name, model, sum(price) total_cost from SmartPhones_data group by brand_name,model order by 1


with cte as(
select brand_name, model, sum(price) total_cost from SmartPhones_data group by brand_name,model),
cte2 as(select c.*, sum(total_cost) over(partition by brand_name order by total_cost) as total_cost_by_brand from cte c)
,cte3 as(select c1.*, dense_rank() over(partition by brand_name order by total_cost_by_brand desc) as rnk from cte2 c1)
select brand_name, total_cost_by_brand from cte3 where rnk=1

I have checked the result using the rolling sum as the total cost by each brand using the window function. (Reverse Engineering)

3. Top 3 Highest rating brands and their models.

----3.Top 3 Highest rating brands and models.
select brand_name, model from(
select distinct brand_name, model, rating, dense_rank() over(order by rating desc) as rnk from SmartPhones_data)a 
where a.rnk<=3 order by rnk 

4. mobiles that do not have 5g or have ir blaster.

By using this data either to select which models are safer but not as fast as 5g. since these are not 5g models.

---4. mobiles that are not having 5g or has ir blaster.
select model from SmartPhones_data where has_5g = 0 and has_ir_blaster = 1

5. Number of processor brands are available? and the number of models per each brand.

select count(distinct processor_brand) number_of_processor_brands from smartphones_data

select processor_brand, count(model) from SmartPhones_data where processor_brand is not null 
group by processor_brand
order by 2 desc 

6. number of cores per model. rank them by the number of cores.

---6. number of cores per model. rank them by the number of cores.
select distinct model, num_cores as number_of_cores from SmartPhones_data

select * from (
select distinct model, num_cores as number_of_cores, dense_rank() over(order by num_cores desc) as rank from 
SmartPhones_data)a order by 3

7. Highest processor speed as per model and brand.

----7. Highest processor speed as per model and brand.
select brand_name, model, processor_speed from(
select brand_name, model, processor_speed, dense_rank() over(order by processor_speed desc) as rnk
from SmartPhones_data)a where a.rnk=1

8. Top 5 highest battery capacity models & their brands.

----8. Top 5 highest battery capacity models & their brands.
select brand_name, model, battery_capacity from (
select brand_name, model, battery_capacity, dense_rank() over(order by battery_capacity desc) as rnk 
from SmartPhones_data )a where a.rnk<=5

9. list the top 5 brands that have the highest RAM capacity and internal memory.

----9. list the top 5 brands & models that have the highest RAM capacity and internal memory.
select brand_name, model, ram_capacity, internal_memory from(
select brand_name, model, ram_capacity, internal_memory, dense_rank() over(order by ram_capacity desc, internal_memory desc) as
rnk from smartphones_data)a where rnk <=5

10. List the top 10 models that have less screen size.

---10. List the top 10 models & brands that have less screen size.
select brand_name, model, screen_size from(
select brand_name, model, screen_size, dense_rank() over(order by screen_size) as rnk from SmartPhones_data)a
where a.rnk<=10

Similarly, we can extract insights from the remaining also. I wish you could do these by taking reference as previous questions. If not you can ask me through comments if any doubts about extracting insights for the below questions.

11. rank brands based on their refresh rate.
12. which model and brand has the highest number of rear cameras?
13. list the number of OS types by each brand.
14. list the top 5 models that have the highest resolution.
15. rank the brands based on the highest resolution, lowest refresh rate, and highest internal memory.
16. list down the processor speed by processor brand rank them and return the top 3 brands.

Do you want to see more projects regarding data analysis?
You can see below data analysis projects mentioned below.

Data Analysis Project Series:

i. A Data Analysis Project — Coffee Shop Sales Analysis.

ii. A Data Analysis Project — Adidas Sales Report.

iii. A Data Analysis Project — Electric Vehicle Population data.

iv. A Data Analysis Project- Data Science Job Postings.

I hope this article is helpful for you in proceeding with data analysis using SQL and applying basic concepts to do data analysis.

Click Here to Prepare a few data analyst interview questions and use cases in my reading list focused on data analysis. You can see the data and SQL files on my GitHub.

Don’t forget to follow my Github to access all projects and to be in touch with upcoming projects as well.

I hope you will Bring your hands together to create a resounding clap to show your support and encouragement for me to share even more quality and valuable content in the future.

I am offering my services as a freelancer at minimal rates to build my portfolio and earn the trust of diverse clients.

Especially for personal (1 & 1) Training Anywhere in the world, we will connect through Zoom.

You can contact me via my Upwork page or hire me for any assistance you need in any data analytics-related work or to teach any data-related Skills (SQL, Python, Power BI, Machine Learning, and Big Data) personally as a Tutor/Trainer. For more details visit my About section.

Contact me at mahendraee204@gmail.com

Follow me on medium to catch any updates from me.

Thank you 🙂

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI