SQL Assignments– Telecom BSS Domain
Assignment 1: Customer Billing Summary
Scenario:
You are part of the billing team. Your task is to provide monthly billing summaries to
customers.
Task:
• - Write a query that returns: customer_id, customer_name, billing_month, total_charges,
tax_amount, net_payable.
• - Include only active customers with bills generated in the last 6 months.
• - Join data from customer, bill, and bill_item tables.
Tables:
• - Customer(customer_id, name, status, activation_date)
• - Bill(bill_id, customer_id, bill_month, total_amount, tax, status)
• - BillItem(bill_item_id, bill_id, charge_type, amount)
Assignment 2: Detecting Dormant Customers
Scenario:
Marketing team wants to target customers who haven't used any services recently.
Task:
• - Identify customers who haven’t generated any usage in the last 3 months.
• - Include customer details and last usage date.
Tables:
• - Customer(customer_id, name, status)
• - UsageDetail(usage_id, customer_id, usage_date, service_type, duration, amount)
Assignment 3: Service Subscription Snapshot View
Scenario:
You need to provide a snapshot view of each customer's current subscribed services.
Task:
• - Create a view `vw_customer_services` with columns: customer_id, name,
active_services (comma-separated).
• - Only include currently active subscriptions.
Tables:
• - Customer(customer_id, name)
• - Subscription(subscription_id, customer_id, service_id, start_date, end_date)
• - Service(service_id, service_name)
Assignment 4: High Usage Customers (Window Functions)
Scenario:
You are tasked with identifying the top 3 highest data users in each region for each month.
Task:
• - Use window functions to rank customers by total data usage per region per month.
• - Return only the top 3 per region-month.
Tables:
• - Customer(customer_id, name, region)
• - UsageDetail(customer_id, usage_date, service_type, data_used_mb)
Assignment 5: Churn Risk Analysis
Scenario:
Predictive analytics team wants to understand patterns leading to customer churn.
Task:
• - Write a stored procedure to flag customers who:
• - - Have not made a payment in the last 2 months.
• - - Have usage less than 100MB in the last month.
• - - Have raised 2 or more complaints in the last 3 months.
• - Store results in a `ChurnRiskCustomers` table.
Tables:
• - Customer(customer_id, name)
• - Payment(payment_id, customer_id, payment_date, amount)
• - UsageDetail(customer_id, usage_date, data_used_mb)
• - Complaint(complaint_id, customer_id, complaint_date, issue_type)
Assignment 6: Prepaid Recharge Behavior Analysis
Scenario:
Analyze recharge frequency to design new recharge packs.
Task:
• - Group customers based on recharge frequency: Frequent (>4/month), Moderate (2-4),
Low (<2).
• - Calculate average recharge amount and most used recharge pack.
Tables:
• - Customer(customer_id, name, account_type)
• - Recharge(recharge_id, customer_id, recharge_date, pack_id, amount)
• - RechargePack(pack_id, pack_name, validity_days)
Bonus Challenge: Multi-SIM Customer Usage Correlation
Scenario:
Some customers use multiple SIMs under one account. Correlate their total usage.
Task:
• - Identify accounts with multiple SIMs.
• - Calculate total usage per account (sum of all SIMs) per month.
• - Flag accounts with >2 SIMs having combined usage over 50GB/month.
Tables:
• - Account(account_id, account_name)
• - SIM(sim_id, account_id, msisdn)
• - Usage(sim_id, usage_date, data_used_mb)
