State of Minnesota interview question
What is the following SQL code trying to do? Please explain (select A.RecipientID B.BeginDate, B.EndDate, B.CountyOfResidence, B.MajorProgram, B.EligibilityType, A.Birthdate, (Case When ((date'2010-12-31' - dhsviews.RecipientsV.Birthdate ) / 365.25) <= 21 then 'Ages 0 through 21' When ((date'2010-12-31' - dhsviews.RecipientsV.Birthdate ) / 365.25) <= 65 then 'Ages 22 through 65' When ((date'2010-12-31' - dhsviews.RecipientsV.Birthdate ) / 365.25) <= 65 then 'Ages 66 and Over' Else 'error' end_ as Age_Category from dhsviews.Recipients V as A, DHSViews.RecipientElibgibility V as B Where A.RecipientID = B.RecipientID and B.MajorProgram IN ('MA', 'NM', 'IM', 'EH') and B.EligibilityStatus IN('A', 'C') and B.BeginDate <= date '2010-12-31' and B.EndDate >= date '2010-01-01' Order by 1,2);
Interview Answer
Anonymous
I believe the SQL query is pulling the following from 2 tables ( A & B) -recipientid through birthdate. The "A." or "B." indicates what column the variable belongs to based on the alias "A" or "B". The Case statements are grouping the ages of the recipient IDs based on their birthdate (12/31/2010-birthdate/365.25). The age categories are 'Ages 0 to 21', 'Ages 22 to 65', 'Ages 66 and over' and any result that does not fit into that category = 'error'. The query identifies the tables to pull from and formally identifying their alias names. WHERE clause matches the records based on "recipientid" from the different tables. There are also filters for major program, eligibility status, begin date, and end date. At the end, order by column 1 - A.RecipientID THEN column 2- B.Begindate