Create the project, dataset, and tables within BigQuery to execute the queries needed to complete the below tasks.

A Word Document with SQL queries & answer to each task + Screenshots of each query on Google BigQuery interface and screenshot of sample results from Google BigQuery interface. 

 

TASK 1: Reports on Customers

Create a list of customers using the reports listed below

1. List of Customers: Create a query that provides a list of customers with 5 demographic qualities about them (5 columns of your choice from table Consumer_Demo). Sort your data based on two columns (one ascending and one descending). Add a filter to your query and write a sentence about what the query is doing.
2. List of Customers + Revenue: Update the query from task 1.1 to return customers and their purchases. For each customer, report the total revenue the customer generated.
3. List of Customers + Revenue + Interests: Update query from task 1.2 to report the products the customer has purchased + the total revenue of each product + 2-3 columns from Consumer_Interests

 

- Primary Key after this symbol occurs only once in the table - Key after this symbol occurs multiple times in the table Consumer_Interests Consumer_ID (PK) Advertising_Preference_Magazine Advertising_Preference_TV Advertising_Preference_Online Charity_Causes_Art Interest Photography Interest Pet Interests_Politics Interests_Reading_General Travel Activity_Touring_Sightseeing Travel Activity_Sports Consumer_Demo Consumer_ID (PK) Age Gender Income Ethnicity Education Presence_of_Child Martial_Status US_Region Media_Date Media_ID (PK) Date Channel Campaign Partner Impressions Clicks Video Plays Video Completes Landing_Page_Visits Media Cost + Site_Data Media_ID Site ID (PK) Purchase ID Date Site Visits Total_Site_Actions Time_On_Site Purchase_Data Purchase_ID (PK) Consumer_ID Units_Purchased Revenue Product