📱 Smartphone Market SQL Analytics Project

A comprehensive market analysis designed to uncover pricing strategies and segmentation in the smartphone industry.

🎯 Project Overview

This project analyzes 1,963 real smartphone records across 12 major brands (Apple, Samsung, Xiaomi, Google, OnePlus, etc.) for 25 features using SQL to solve real-world business problems faced by e-commerce platforms, market intelligence firms, and product teams.

Business Context:
This analysis is framed from the perspective of a data analyst at a consumer electronics platform, evaluating competitive pricing, feature trends, and market segmentation to support inventory planning, marketing strategy, and product decision-making.

📊 Dataset Highlights

Metric	Value
Total Records	1,963 smartphones
Brands Analyzed	12 (Apple, Samsung, Xiaomi, Google, OnePlus, Motorola, Realme, Vivo, Oppo, Nokia, etc.)
Specifications Tracked	25 (RAM, camera, battery, screen size, price, OS, network support, etc.)
Price Range	₹5,309 - ₹147,005 (Budget → Ultra-Premium)
Time Period	2015-2025 (10-year market window)
OS Coverage	Android & iOS

🛠️ SQL Skills Demonstrated

Beginner Level

✅ SELECT, WHERE, ORDER BY, GROUP BY
✅ Aggregate functions (COUNT, AVG, MIN, MAX, SUM)
✅ Basic filtering and sorting

Intermediate Level

✅ CASE WHEN for conditional logic
✅ HAVING clause for filtered aggregations
✅ Multi-table joins (if extended)
✅ Derived metrics and business calculations

Advanced Level

✅ Window Functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
✅ Percentile Analysis: PERCENTILE_CONT()
✅ Common Table Expressions (CTEs): Complex multi-step queries
✅ Value Scoring Models: Custom weighted algorithms
✅ Market Segmentation: Clustering logic in SQL

📂 Repository Structure

smartphone-sql-analytics/
├── README.md
├── data/
│   └── smartphone.csv
├── schema/
│   ├── create_tables.sql
│   └── load_data.sql
├── queries/
│   ├── 01_beginner_queries.sql
│   ├── 02_intermediate_queries.sql
│   ├── 03_advanced_queries.sql
│   └── screenshots/
│       └── query_result_images/
└── insights/
    ├── business_insights.md
    └── Basic_pgadmin_infographics/
        └── charts_and_visuals/

📈 Key Business Insights

1. Market Segmentation

Budget (<₹20K): 35% of market
Mid-Range (₹20-40K): 30% of market
Premium (₹40-80K): 25% of market
Luxury (>₹80K): 10% of market

2. Operating System Dynamics

Android: 95% market share, average price ₹40,000
iOS: 5% market share, average price ₹100,000 (premium positioning)

3. Technology Adoption

5G Support: 45-60% adoption across brands
High Refresh Rate (120Hz+): Now standard in premium phones
Fast Charging (67W+): Key mid-range differentiator

📶 Average 5G Smartphone Price by Brand

4. Competitive Positioning

Volume Leaders: Samsung, OnePlus, Motorola (most models)
Revenue Leaders: Apple, Samsung (premium pricing)
Value Champions: Xiaomi, Realme (best specs for price)

📊 Price Distribution (Percentiles) Across Brands

💡 Query Highlights

Beginner Queries (1-8)

SELECT, WHERE, ORDER BY, GROUP BY
COUNT, AVG, MIN, MAX, SUM
Market composition and price distribution

Intermediate Queries (9-13)

CASE WHEN for segmentation
HAVING clause for filtered aggregations
Price-to-spec value analysis
Feature availability by price tier

Advanced Queries (14-19)

Window Functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
Percentiles: PERCENTILE_CONT() for quartile analysis
CTEs: Complex multi-step queries
Custom Scoring: Weighted algorithms for value ranking
Trend Analysis: Year-over-year comparisons with LAG()

💼 Resume Bullet Point

Analyzed 1,963 smartphones across 12 brands using PostgreSQL
Built 19 SQL queries from basic aggregation to advanced window functions
Applied PERCENTILE_CONT, NTILE, LAG for market segmentation and trend analysis
Generated insights: market leaders, 5G adoption (45-60%), value opportunities using weighted scoring algorithm

📧 Connect

Yashpal Suwansia
IIT Bombay Alumnus Passionate about bridging Business Strategy with Data Science.

📧 Email: ysuwansia@gmail.com
💼 LinkedIn: https://www.linkedin.com/in/yashpal-suwansia-a45a73260
📞 Contact: +91-7976009985

⭐ If this helped you, please star this repo!

yashpals1986/Smartphone-SQL-Analytics-pgadmin-PostgreSQL

📱 Smartphone Market SQL Analytics Project

🎯 Project Overview

📊 Dataset Highlights