This project is a Mobile Network Business Analysis case study developed using MySQL. The objective is to analyze customer behavior, recharge patterns, revenue generation, mobile data usage, and operator performance using SQL queries.
The project simulates a real-world telecom business database and demonstrates how SQL can be used to extract meaningful business insights from relational datasets.
The database consists of 5 related tables containing customer information, operators, recharge plans, recharge transactions, and usage details.
- Analyze customer demographics and registrations
- Evaluate operator-wise performance
- Identify the most popular recharge plans
- Analyze company revenue and recharge trends
- Study customer data consumption behavior
- Perform city-wise and state-wise analysis
- Practice advanced SQL concepts using real-world business scenarios
The project contains 5 relational tables.
| Table | Rows | Description |
|---|---|---|
| customers_ntr | 500 | Customer master data |
| operator | 4 | Mobile network operators |
| plans | 16 | Recharge plan details |
| recharges | 2,000 | Recharge transaction records |
| usage_details | 2,000 | Mobile usage statistics |
Dataset sizes and table counts are verified in the project SQL scripts. :contentReference[oaicite:0]{index=0} :contentReference[oaicite:1]{index=1}
Stores customer profile information.
Columns
- customer_id (Primary Key)
- customer_name
- phone_number
- city
- state
- age_group
- registration_date
Rows: 500
Contains mobile operator information.
Columns
- operator_id (Primary Key)
- operator_name
- founded_year
- headquarters
- customer_base_million
Rows: 4
Contains recharge plan information.
Columns
- plan_id (Primary Key)
- operator_id (Foreign Key)
- plan_name
- validity_day
- data_gb_per_day
- calls
- sms_per_day
- price_rs
- plan_type
Rows: 16
Stores recharge transactions.
Columns
- recharge_id (Primary Key)
- customer_id (Foreign Key)
- plan_id (Foreign Key)
- recharge_date
- expiry_date
- amount_paid_rs
- payment_method
- status
Rows: 2,000
Contains customer usage information.
Columns
- usage_id (Primary Key)
- recharge_id (Foreign Key)
- customer_id (Foreign Key)
- data_used_gb
- calls_minutes
- sms_sent
- last_updated
Rows: 2,000
The project starts by:
- Creating the database
- Creating all tables
- Importing CSV files using
LOAD DATA INFILE - Converting date columns using
STR_TO_DATE()
These steps are included in the setup SQL script. :contentReference[oaicite:2]{index=2}
The following constraints establish relationships between the tables:
| Parent Table | Child Table | Relationship |
|---|---|---|
| operator | plans | operator_id |
| customers_ntr | recharges | customer_id |
| plans | recharges | plan_id |
| recharges | usage_details | recharge_id |
| customers_ntr | usage_details | customer_id |
Foreign key constraints are added using ALTER TABLE ... ADD CONSTRAINT. :contentReference[oaicite:3]{index=3}
- MySQL 8.0
- MySQL Workbench
- SQL
- CSV Files
- Git
- GitHub
- CREATE DATABASE
- CREATE TABLE
- ALTER TABLE
- PRIMARY KEY
- FOREIGN KEY
- LOAD DATA INFILE
- STR_TO_DATE()
- SELECT
- WHERE
- ORDER BY
- GROUP BY
- HAVING
- LIMIT
- DISTINCT
- COUNT()
- SUM()
- AVG()
- ROUND()
- INNER JOIN
- USING()
- DATE_FORMAT()
- STR_TO_DATE()
- DENSE_RANK()
- RANK()
- CASE WHEN
- Common Table Expressions (CTE)
- Subqueries
- Window Functions
- Stored Procedures
The project includes examples of advanced SQL such as ranking, CASE expressions, subqueries, CTEs, and a stored procedure. :contentReference[oaicite:4]{index=4}
This project contains multiple business-oriented SQL case studies.
- Total customers
- City-wise customer count
- State-wise customer count
- Age group distribution
- Plans offered by each operator
- Most popular plans
- Prepaid vs Postpaid analysis
- Most expensive plans
- Total revenue
- Operator-wise revenue
- Plan-wise revenue
- City-wise revenue
- Monthly revenue trends
- Payment method analysis
- Average data usage by age group
- Top data-consuming customers
- Operator-wise average data usage
- Usage tier classification
- Top customers by state
- Heavy vs average users
- Above-average spenders
- Stored procedure for customer recharge details
These analyses are documented with SQL queries and business explanations throughout the case study file. :contentReference[oaicite:5]{index=5}
- Database contains 500 customers, 4 operators, 16 plans, 2,000 recharge records, and 2,000 usage records.
- Airtel generates the highest revenue.
- Jaipur is the highest revenue-contributing city.
- All plans in the dataset are prepaid.
- Basic and standard plans are the most frequently recharged.
- Jio customers consume the highest average mobile data.
- Customer behavior varies significantly across cities, states, and age groups.
These insights are derived from the analytical SQL queries and explanations included in the project. :contentReference[oaicite:6]{index=6} :contentReference[oaicite:7]{index=7}
Mobile-Network-Analysis/
│
├── Dataset/
│ ├── customers.csv
│ ├── operators.csv
│ ├── plans.csv
│ ├── recharges.csv
│ └── usage_details.csv
│
├── SQL/
│ ├── Database_Setup.sql
│ ├── Mobile_Network_Case_Study.sql
│
├── README.md
By completing this project, I gained practical experience in:
- Database design
- Relational database modeling
- Writing optimized SQL queries
- Business analytics using SQL
- Revenue analysis
- Customer segmentation
- Window functions
- Stored procedures
- Real-world telecom data analysis
Pushpendra Verma
- Aspiring Data Analyst
- SQL | Excel | Power BI | MySQL | Git | GitHub