📚 100 DAYS SQL

CODING CHALLENGE

DAY 22
Master SQL One Problem at a Time

✨ Daily SQL Problems
✨ Real-World Scenarios
✨ Complete Solutions
✨ Interview Ready

📊 Download Practice Dataset

Click this link to get the CSV files:

Get Dataset (4 tables: call_records, customers, towers, plans)

📞 BSNL Telecom Call Detail Records (CDR) Analysis

BSNL needs CDR analysis for revenue optimization, network planning, fraud detection, and usage pattern tracking.

Table 1: call_records

call_id caller_number receiver_number call_datetime duration_sec call_type
CALL001 987654321091234567892024-01-15 08:30:15125local
call002 9988776655 876543210915/01/2024 09:15:30NULLSTD
CALL0039123456789 9876543210 2024/01/15 10:45:0089Local
Call004876543210999887766552024-01-15 14:20:45342ISD

Table 2: customers

phone_number customer_name plan_id circle
9876543210rajesh kumarP01DELHI
9988776655 PRIYA SHARMAp02mumbai
9123456789 Amit Patel P01Gujarat
8765432109sunita devi P03 BIHAR

Table 3: towers

tower_id tower_location circle tower_type
T001Connaught PlaceDELHI4G
t002 BANDRA WESTmumbai5g
T003 Ahmedabad City Gujarat4G

Table 4: plans

plan_id plan_name monthly_rental free_minutes
P01Basic Plan199.00100
p02 PREMIUM PLAN499.00500
P03unlimited plan799.00NULL

Download full dataset above (30+ rows per table)

📋 Challenge Requirements

Build comprehensive CDR analysis system for BSNL's telecom operations.

🧹 Data Cleaning:
  • Standardize IDs: uppercase, trim
  • Clean phone numbers: remove spaces
  • Standardize names: proper case
  • Standardize datetime formats
  • Handle NULL/0 duration (failed calls)
  • Standardize call_type, circles, tower_type
🔗 Multi-Table Analysis:
  • Self-JOIN customers (caller & receiver)
  • JOIN towers and circles
  • JOIN plans for billing
  • Calculate call duration in minutes
  • Track peak calling hours
  • Identify inter-circle vs intra-circle calls
⚠️ Revenue & Fraud Detection:
  • Calculate billable_minutes (total - free)
  • Calculate revenue per customer
  • Detect unusual patterns (duration > 2 hours)
  • Flag excessive calling (10+ calls/day)
  • Identify customers exceeding limits
  • Calculate revenue_loss from failed calls
  • Segment users: heavy, normal, light

💡 Window Functions, HOUR(), Self-JOINs, Subqueries, CTEs!

🎉 Ready to Solve?

Download the PDF for complete solution with hints!

🔗 Connect & Stay Updated

Follow my SQL journey:

LinkedIn GitHub Access all 100 challenges!

💪 Keep Practicing!

Master telecom analytics
Build CDR analysis systems
Detect fraud with SQL

Day 23 coming tomorrow!

Rahul Kanche — exploring data, decoding clarity!