A comprehensive market analysis designed to uncover pricing strategies and segmentation in the smartphone industry.
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.
| 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 |
✅ SELECT, WHERE, ORDER BY, GROUP BY
✅ Aggregate functions (COUNT, AVG, MIN, MAX, SUM)
✅ Basic filtering and sorting
✅ CASE WHEN for conditional logic
✅ HAVING clause for filtered aggregations
✅ Multi-table joins (if extended)
✅ Derived metrics and business calculations
✅ 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
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/
- Budget (<₹20K): 35% of market
- Mid-Range (₹20-40K): 30% of market
- Premium (₹40-80K): 25% of market
- Luxury (>₹80K): 10% of market
- Android: 95% market share, average price ₹40,000
- iOS: 5% market share, average price ₹100,000 (premium positioning)
- 5G Support: 45-60% adoption across brands
- High Refresh Rate (120Hz+): Now standard in premium phones
- Fast Charging (67W+): Key mid-range differentiator
- Volume Leaders: Samsung, OnePlus, Motorola (most models)
- Revenue Leaders: Apple, Samsung (premium pricing)
- Value Champions: Xiaomi, Realme (best specs for price)
- SELECT, WHERE, ORDER BY, GROUP BY
- COUNT, AVG, MIN, MAX, SUM
- Market composition and price distribution
- CASE WHEN for segmentation
- HAVING clause for filtered aggregations
- Price-to-spec value analysis
- Feature availability by price tier
- 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()
- 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
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!