Let's see how our dataset looks like

Observations

Which registrar has done most Aadhar card Appointment ?

Select Registrar,Round((Count(ID)/(Select Count(*) From Data))*100,2) as Cnt From data Group By Registrar Order by 2 desc



The reason for above can be more no of Enrolment agency in a particular Registrar. Let’s see the distribution of Enrolment Agency in each Registrar.

SELECT d1.Registrar, (Select Count(d2.[Enrolment Agency]) From Data d2 Where d2.Registrar = d1.Registrar) AS [No of Enrol Agency] FROM Data AS d1 GROUP BY d1.Registrar ORDER BY 2 DESC;



Let’s see which states have most Aadhar Registrations.

Select State ,Round((Count(ID)/(Select Count(ID) From Data))*100,2) as [% of Aadhar reg] From Data Group By State Order by 2 desc;



Let’s see the sex ratio of Aadhar enrolment applicants.

Select Gender,Round((Count(ID)/(Select Count(ID) From Data))*100,5) as [No of applicants] From Data Group By Gender

Let’s see Age distribution across the dataset. Which age group has most no of Aadhar enrolment Applicants.

Select (Select Count(*) From Data d2 Where d2.Age >= 0 AND d2.Age <=15) as [Child (0-15)], (Select Count(*) From Data d2 Where d2.Age > 15 AND d2.Age <= 22) as [Teen (15-22)], (Select Count(*) From Data d2 Where d2.Age > 22 AND d2.Age <= 35) as [Youth (22-35)], (Select Count(*) From Data d2 Where d2.Age > 35 AND d2.Age <=55) as [Adult (35-55)], (Select Count(*) From Data d2 Where d2.Age > 55 AND d2.Age <=118) as [Senior_citizen (55-118)] From data d1 Where d1.ID = 1

Comparision of Female Aadhar Enrolments in different Districts.

Select District,Count(*) as Cnt From Data Where Gender = "F" Group By District,Gender order by 3

Who are the top 30 Enrolment Agency that have generated most number of Aadhaar’s.

Select TOP 30 [Enrolment Agency], Round((Sum([Aadhaar Generated])/(Select Sum([Aadhaar Generated])From Data))*100,2) as total_adhar_generated From data - Group By [Enrolment Agency] Order by 2 DESC;

TOP 30 Enrolments Agency who has large no of Enrolment rejections.

Select Top 30 [Enrolment Agency] , Round((Sum([Enrolment Rejected])/(Select Sum([Enrolment Rejected]) From Data))*100,2) as [% of Enrolment Rejection] From Data Group By [Enrolment Agency] Order by 2 desc;

State wise Aadhaar card enrolment applicants.

Select State, Round((Count(ID)/(Select Count(ID) From Data))*100,2) as Per_of_App From data Group By State;

State wise accepted applications Percentage.

SELECT d1.State,(Select Count(ID) From Data d2 Where d2.State = d1.State) as no_of_applicants,Round (((Select Sum([Enrolment Rejected]) From Data d2 Where d2.State = d1.State)/no_of_applicants)*100,2) as per_of_rejected, (100 - per_of_rejected) as per_of_accepted From Data d1 Group By d1.State;

Aadhar card generation % state wise.

Select State,Round((Sum([Aadhaar Generated])/(Select Sum([Aadhaar Generated])From Data))*100,2) as [% of Aadhar Generated] From Data Group By State order by 2 desc;

Percentage of Email and phone number provided in total Aadhar generated till now.

Select Sum([Residents providing Email]) as total_email_proveded , Sum([Aadhaar Generated]) as total_aadhr_genreated From Data
Select Sum([Residents providing mobile number]) as total_mobile_proveded , Sum([Aadhaar Generated]) as total_aadhr_genreated From Data

Let’s see % of Aadhar generated age wise.

Select [Age_group],Sum([Aadhaar generated]) as [No_of_Aadhar_Generated] From( SELECT Age, [Aadhaar Generated], Switch( [Age] > 0 AND [Age] <= 15 , "Child", [Age] > 15 AND [Age] <= 22 , "Teen", [Age] > 22 AND [Age] <= 35 , "Youth", [Age] > 35 AND [Age] <= 55 , "Adult", [Age] > 55 AND [Age] <= 118 , "Senior_Citizen", [Age] = 0 , "Invalid_Age-0" ) AS Age_group FROM Data) Group By [Age_group]

Let’s see % of Aadhar generated sex wise.

Select gender,Sum([Aadhaar generated]) as [no of aadhar generated] From data Group By gender

Gender Ratio In each age group.

SELECT Age_group, Gender, Count(*) AS Cnt FROM (SELECT Age, [Aadhaar Generated],Gender, Switch( [Age] > 0 AND [Age] <= 15 , "Child", [Age] > 15 AND [Age] <= 22 , "Teen", [Age] > 22 AND [Age] <= 35 , "Youth", [Age] > 35 AND [Age] <= 55 , "Adult", [Age] > 55 AND [Age] <= 118 , "Senior_Citizen", [Age] = 0 , "Unknown-age" ) AS Age_group FROM Data) AS [%$##@_Alias] GROUP BY Age_group, Gender;