Attendence for each lok sabha session

select lok_sabha
  ,session
  ,dateOfAttendance
  ,count(*) as TotalMPs
  ,count(case when isPresent=True then 1 end)*100/count(*) perMPsPresent
from fact.lok_sabha_fact 
where isPresent is not null
group by 1,2,3 order by 1,2,3

Overall attendence so far

Who are the members who are exempt from siginig the register

select distinct lok_sabha
  ,memberName
from fact.lok_sabha_fact 
where isPresent is null
order by memberName

Party

1. Which Party has the highest attendence % ?

   select lok_sabha
     ,partyFname
     ,count(distinct mpsno) as TotalMPs
     ,count(case when isPresent=True then 1 end)*100/count(*) perMPsPresent
   from fact.lok_sabha_fact 
   where isPresent is not null
   group by 1,2 order by 1,3 desc
   

   1. Min of MPs to be considered

2. Top 5 and bottom 5 MPs from each party

   with base as
     (select lok_sabha
       ,partyFname
       ,memberName
       -- ,count(distinct mpsno) as TotalMPs
       ,count(case when isPresent=True then 1 end)*100/count(*) perAttendence
     from fact.lok_sabha_fact 
     where isPresent is not null
     group by 1,2,3 order by 1,4 desc)
   
   select *
     ,dense_rank() over(partition by lok_sabha order by perAttendence desc) overallRnk
     ,dense_rank() over(partition by lok_sabha,partyFname order by perAttendence desc) partyRnk
   from base where partyFname in ('Indian National Congress','Bharatiya Janata Party')
   qualify partyRnk<=5
   order by lok_sabha
   

Profession

1. Which profession has the highest attendence % ?

   with base as
     (select lok_sabha
       ,profession
       ,count(distinct mpsno) as TotalMPs
       ,count(case when isPresent=True then 1 end)*100/count(*) perAttendence
     from fact.lok_sabha_fact 
     where isPresent is not null
     group by 1,2 order by 1,3 desc,4 desc)
   
   select *
   from base
   

2. Profession with the most and least share in MPs

   1. How do they look over the years?

      with base as 
        (select coalesce(profession,profession2) profession
          ,status
          ,count(*) totalMPs 
        
          from lok_sabha.src_ls_member_metadata where status='Sitting'
        group by 1,2 order by 3 desc)
      
      select *
        ,totalMPs*100/sum(totalMPs) over(partition by status) perShare
       from base order by totalMPs desc
      

Geography

1. Which state has the highest attendence record ?
2. Which State has the lowest ?

Gender

1. Who attend parliament more Male or Female MP ?
2. Which Party has the Highest % of Female MPs ?

Age

1. Young vs Old MP attendnce records?
2. What is the Avg age of our current MPs?

Term

1. How does the attendece trend look like for MPs who have had multiple terms ? Does it go up or Down ?
2. What % of MPs in each LS have more than 1 term ? Has it increased or decreased ?

Marital Status

1. What is the % of batchlor MPs ?
2. Batchlor vs married MP attendence

Digital India

1. Look at % of digital use across each segment mentioned above.

MPs who actually qualify for next election (more than 75%)

with base as
  (select memberName
    ,count(case when isPresent=True then 1 end)*100/count(*) perAttendence
  from fact.lok_sabha_fact 
  where lok_sabha=17 and isPresent is not null 
    and mpsno not in (select distinct mpsno from fact.lok_sabha_fact where isPresent is null and lok_sabha=17)
  group by 1 order by 1,2 desc)

select case when perAttendence>=75 then 'PASS' else 'FAIL' end as hasPassed
  ,count(*)
from base
group by 1