📊 Day 22 Data Download

BSNL Telecom Call Detail Records (CDR) Analysis Dataset

Self-JOINs Time Analysis Revenue Calculation Fraud Detection

Table 1: call_records (30 rows)

call_id	caller_number	receiver_number	call_datetime	duration_sec	call_type	tower_id
CALL001	9876543210	9123456789	2024-01-15 08:30:15	125	local	T001
call002	9988776655	8765432109	15/01/2024 09:15:30	NULL	STD	t002
CALL003	9123456789	9876543210	2024/01/15 10:45:00	89	Local	T001
Call004	8765432109	9988776655	2024-01-15 14:20:45	342	ISD	T003
CALL005	9123456789	7654321098	15-01-2024 16:55:20	0	local	t001
call006	9876543210	9988776655	2024-01-16 07:10:30	256	LOCAL	T001
CALL007	7654321098	9123456789	16/01/2024 11:25:45	178	std	t005
Call008	9988776655	8765432109	2024/01/16 13:40:20	NULL	Local	T002
CALL009	8765432109	9876543210	2024-01-16 15:15:00	8450	ISD	T003
call010	9123456789	7654321098	16-01-2024 18:30:15	45	local	t001
CALL011	9876543210	9123456789	2024-01-17 09:05:30	310	LOCAL	T001
CALL012	9988776655	8765432109	17/01/2024 10:20:45	189	Std	t002
call013	7654321098	9988776655	2024/01/17 12:35:00	0	local	T005
Call014	8765432109	9123456789	2024-01-17 14:50:20	267	LOCAL	T003
CALL015	9123456789	9876543210	17-01-2024 17:15:40	NULL	local	t001
call016	9876543210	7654321098	2024-01-18 08:25:15	425	STD	T001
CALL017	9988776655	9123456789	18/01/2024 11:40:30	198	Local	t002
Call018	7654321098	8765432109	2024/01/18 13:55:45	356	isd	T005
CALL019	8765432109	9988776655	2024-01-18 16:10:00	8920	ISD	T003
call020	9123456789	9876543210	18-01-2024 19:25:20	67	LOCAL	t001
CALL021	9876543210	9988776655	2024-01-19 07:40:35	NULL	local	T001
Call022	9988776655	7654321098	19/01/2024 10:55:50	289	Std	t002
CALL023	7654321098	9123456789	2024/01/19 14:10:05	0	local	T005
call024	8765432109	9876543210	2024-01-19 17:25:20	512	ISD	T003
CALL025	9123456789	8765432109	19-01-2024 20:40:35	234	LOCAL	t001
CALL026	9876543210	9123456789	2024-01-20 09:55:50	378	local	T001
call027	9988776655	8765432109	20/01/2024 13:10:05	NULL	STD	t002
Call028	7654321098	9988776655	2024/01/20 16:25:20	445	Local	T005
CALL029	8765432109	7654321098	2024-01-20 19:40:35	8765	isd	T003
call030	9123456789	9876543210	20-01-2024 22:55:50	123	LOCAL	t001

Table 2: customers (20 rows)

phone_number	customer_name	plan_id	circle	registration_date
9876543210	rajesh kumar	P01	DELHI	2023-05-10
9988776655	PRIYA SHARMA	p02	mumbai	15/06/2023
9123456789	Amit Patel	P01	Gujarat	2023/08/20
8765432109	sunita devi	P03	BIHAR	10-09-2023
7654321098	MOHAMMED ALI	p02	Kerala	2023-11-15
9876543211	anita desai	P01	delhi	2023-06-22
9988776656	VIKRAM SINGH	p03	MUMBAI	18/07/2023
9123456788	Lakshmi Iyer	P02	Kerala	2023/09/15
8765432108	ravi shankar	P01	GUJARAT	12-10-2023
7654321099	DEEPA REDDY	p02	Bihar	2023-12-05
9876543212	Karan Mehta	P03	Delhi	2024-01-10
9988776657	sneha gupta	P01	mumbai	20/01/2024
9123456787	ARJUN NAIR	p02	KERALA	2024/02/08
8765432107	Meera Shah	P01	Gujarat	15-02-2024
7654321097	suresh kumar	P03	bihar	2024-03-12
9876543213	POOJA VERMA	p01	DELHI	25/03/2024
9988776658	Rohan Das	P02	Mumbai	2024/04/18
9123456786	kavita pillai	P03	kerala	10-05-2024
8765432106	NITIN YADAV	p01	Bihar	2024-06-15
7654321096	Divya Joshi	P02	Gujarat	22/07/2024

Table 3: towers (15 rows)

tower_id	tower_location	circle	tower_type
T001	Connaught Place	DELHI	4G
t002	BANDRA WEST	mumbai	5g
T003	Ahmedabad City	Gujarat	4G
t004	Patna Junction	BIHAR	3G
T005	kochi marine drive	Kerala	4g
T006	Karol Bagh	delhi	4G
t007	ANDHERI EAST	MUMBAI	5G
T008	Satellite Road	Gujarat	4g
T009	Boring Road	Bihar	3G
t010	ernakulam south	KERALA	4G
T011	ROHINI SECTOR	Delhi	5g
t012	Juhu Beach	mumbai	4G
T013	Vastrapur Lake	GUJARAT	4g
T014	Gandhi Maidan	bihar	3G
t015	Fort Kochi	Kerala	5G

Table 4: plans (3 rows)

plan_id	plan_name	monthly_rental	free_minutes	rate_per_min
P01	Basic Plan	199.00	100	1.00
p02	PREMIUM PLAN	499.00	500	0.50
P03	unlimited plan	799.00	NULL	0.00

📖 How to Import Data

MySQL:

CREATE DATABASE bsnl_cdr;
USE bsnl_cdr;

CREATE TABLE call_records (
    call_id VARCHAR(50),
    caller_number VARCHAR(20),
    receiver_number VARCHAR(20),
    call_datetime VARCHAR(50),
    duration_sec VARCHAR(10),
    call_type VARCHAR(20),
    tower_id VARCHAR(20)
);

LOAD DATA INFILE 'day_22_call_records.csv'
INTO TABLE call_records
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

PostgreSQL:

CREATE TABLE call_records (
    call_id VARCHAR(50),
    caller_number VARCHAR(20),
    receiver_number VARCHAR(20),
    call_datetime VARCHAR(50),
    duration_sec VARCHAR(10),
    call_type VARCHAR(20),
    tower_id VARCHAR(20)
);

COPY call_records FROM '/path/to/day_22_call_records.csv'
DELIMITER ',' CSV HEADER;

Note: All columns are VARCHAR to preserve messy data. Clean them in your queries!