Youtube Tutorial (Hindi, Auto Dubbed in English) :

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

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

SQL Code:

create or replace table lok_sabha.dim_src_member_attendence as
with base as 
  ( SELECT lok_sabha
    ,session
    ,dateOfAttendance
    ,attendence_data
  FROM lok_sabha.src_member_attendence,
  unnest(JSON_EXTRACT_ARRAY(member_level_attendence)) as attendence_data)

select *
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.mpsno') mpsno
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.division') division
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.memberName') memberName
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.attendanceStatus') attendanceStatus
from base

create or replace table lok_sabha.dim_src_loksabha_session_dates as 
SELECT * 
  ,JSON_EXTRACT_SCALAR(api_data,'$.presentCount') as presentCount
  ,JSON_EXTRACT_SCALAR(api_data,'$.absentCount') as absentCount
FROM `lok_sabha.src_loksabha_session_dates`;

SQL Questions Playlist: https://www.youtube.com/playlist?list=PLGTMkAiTnEDJR9F59CW3M1xGMJ5fZnnpP

Protfolio Projects : https://www.youtube.com/playlist?list=PLGTMkAiTnEDIm3wtHmg_lYYP9lADd6GyC

Case study Frameworks : https://www.youtube.com/playlist?list=PLGTMkAiTnEDIagFrYySUV87WBUCM_gM3P

Setup your analytics infra : https://www.youtube.com/playlist?list=PLGTMkAiTnEDLXNASjx5sXwXREvf54W9tx

Analytics Roadmap: https://www.notion.so/Data-Analytics-Roadmap-26134eb1037480339e20e15575dc7a06

Latest Job openings: https://www.linkedin.com/in/aloktheanalyst/