Individual Assignment 1 - SQL
BUMK 758W
Spring 2015
Overview
In this assignment, you will work on the reviewer viewership dataset. You will perform a list of tasks using
SQL to retrieve relevant information in desired format, and to organize data. This is an individual assignment.
You are not supposed to discuss the problems with any other students.
The dataset is provided to you in two excel files available at ELMS. The schema is as explained in the lectures. You will use SAS for this assignment. If you use another DBMS, please ensure the SQL statement you submit can run in SAS.
Assignment
Each of the following questions should be answered using SQL statements. Unless otherwise noted, you are free to use one or multiple SQL statements for each question. Intermediate data may be used if you prefer
(e.g. creating an additional column)
Part I, Preliminary
You should begin by importing the two excel files into SAS. This will create two tables. To maintain consistency across the class, please name the reviewer characteristics table “Reviewer”, and the content viewership table “Content_Viewership” (they are so named for the two files). Once the tables are created, you are ready to answer the following questions.
Part II, Basic Tasks (8 points)
1. How many reviewers are there in the dataset?
2. What is the average daily Viewership, Content, and Centrality across all reviewers (words with first letter capitalized indicates it is the name of a column, same for below)?
3. How many reviewers are both Advisor and Top50?
4. What are the top five daily Viewership numbers, for which reviewer and on what Date?
5. Who are the top three Advisors with highest average Centrality?
6. Do Top50 reviewers have higher average Content than Advisors?
7. Create a table “Date” to store all the Dateids and Dates in the viewership dataset (you need to populate the table with the records). How many different days are there in the dataset?
8. Create a table “Lead” with one record for each reviewer who is a Lead, which contains information on the average Viewership, average Content, and average Centrality of that reviewer.

Part III, More Advanced Tasks (12 points)
9. Does the group of reviewers who are Top10 or Top50 have higher