Step by step guide Hindi:

https://www.youtube.com/watch?v=yj7TTLUv5GQ&list=PLGTMkAiTnEDIm3wtHmg_lYYP9lADd6GyC&index=1&ab_channel=AlokTheAnalyst

ARD - Analytics Requirement Document

ARD - Analytics Requirement Document (1)

Script link: https://colab.research.google.com/drive/19iVBkojfPyiNXyFgz3KI3_5zRm9gc2rv?authuser=1#scrollTo=xkhIWgrUp_SQ

Project Data: https://drive.google.com/drive/u/4/folders/1JJM01BRDQj9dUAhgFLf74bmYlqa_YmBv

create or replace table fact.lok_sabha_fact as 
with attendence_data as 
  (select lok_sabha
    , session
    ,dateOfAttendance
    ,mpsno
    ,division
    ,memberName
    ,attendanceStatus
    ,case when attendanceStatus='NS' then 'Member did not sign'
      when attendanceStatus='NS@' then 'Member present but forgot to sign'
      when attendanceStatus='NR' then 'Not Required'
      when attendanceStatus='S#' then 'Signed both Register & Mobile'
      when attendanceStatus='S*' then 'Signed through Mobile'
      when attendanceStatus='S' then 'Member signed'
      end as attendenceCtg
    
    ,case when attendanceStatus in ('NS@','S#','S*','S') then True
       when attendanceStatus in ('NS') then False end as isPresent

  from lok_sabha.dim_src_member_attendence)

,session_dates as 
  (select * 
  from lok_sabha.dim_src_loksabha_session_dates)

,mp_metadata as 
  (select * 
  from lok_sabha.src_ls_member_metadata)

select attendence_data.*
  -- ,mpLastFirstName
  ,mp_metadata.status
  ,mp_metadata.noOfTerms
  ,mp_metadata.stateName
  ,partySname
  ,profession
  ,profession2
  ,partyFname
  ,gender
  ,constName
  ,qualification
  ,maritalStatus
  ,age
  ,dob
  ,date_diff(dateOfAttendance,dob,day)/365 as calculatedAge
from attendence_data
left join mp_metadata on mp_metadata.mpsno=cast(attendence_data.mpsno as Int)