Exercise SQL count based on Subscriber-MemberId information

Use the table solve the query below.

SELECT [SUBSCRIBER_ID]

      ,[MEMBER_SUFFIX]

      ,[FIRST_NAME]

      ,[MIDDLE_NAME]

      ,[LAST_NAME]

      ,[SUFFIX_NAME]

      ,[FAMILY_RELATIONSHIP]

      ,[FAMILY_REL_DESC]

      ,[BIRTH_DATE]

      ,[DATE_OF_DEATH]

      ,[ADDRESS_SUFFIX]

      ,[ALTERNATE_ID1]

      ,[ALTERNATE_ID2]

      ,[GENDER]

      ,[MEMBER_ETHNICITY]

      ,[MAILING_ADDRESS_SUFFIX]

  FROM [IkaDataWarehouse].[dbo].[ECC_Member_Master]

Member Count By Gender – This is a summary report.  Please generate a summary by gender for all members.  Basically this report should have 2 columns: Gender and Count.

Member Count By Age Group – This is a summary report.  Please generate a summary by age group for all members.  Basically for this report, you will need to create your own age groups and place the member into that specific age group category.  How to define the age group, I will leave up to you but you should basically have the SQL script in your dataset such as “SELECT …, CASE WHEN birthday between date1 and date2 then [00-10] WHEN birthday between date3 and date4 then [11-20] ELSE …. END AS Age_Group”.  Another way is to calculate the age and then base the group off of that calculation.  Final output of that report should be two columns: Age_Group and Member_Count.  If you have time, you can create a pivot of this and have a two row report that looks similar to the one below.

                                    

	[00-10]	[11-20]	[21-30]	[31-40]	[41-50]	[51-60]	[61+]
Member_Count	17	45	3456	4567	4321	789	212

Member List for No Birthdays – This is a detailed list of Member Info.

Output: Subscriber_Id, Member_Suffix OR Combine the two as Member_Id, First_Name, Middle_Name, Last_Name

Subscriber-Member Count Report – This is a summary type report.  You would basically run for multiple group bys.  This is your standard template for member OR subscriber summary reports.

These are subsets of the Subscriber-Member Count reports:

Member Count by Age Group: Count the Subscriber or Member using a Group By (Age_Group); Output: Age_Group, Member_Count

Member Count by Gender: Count the Subscriber or Member using a Group By (Gender); Output: Gender, Member_Count

Member Count by Subscriber: Count the Member using a Group By (Subscriber); Output: Subscriber_Id, Member_Count

Using the Subscriber-MemberId information, create another report that looks like the one below:

Subscriber_Id	Self	Spouse/Significant Other	Child	Total
N000000###1	1	1	0	2
N000000###2	1	0	0	1
N000000###3	1	0	0	1
N000000###4	1	1	5	7

Note that this one uses the Family Relationship column and also group by.