Preparing Data Hints: Tasks Gather customerid and territoryid from the customer table Gather TotalPurchaseYTD, DateFirstPurchase, MaritalStatus, YearlyIncome, Gender, TotalChildren, NumberChildrenAtHome Education, Occupation, HomeOwnerFlag, NumberCarsOwned, and CommuteDistance from the person table Calculate the customers age by determinine the number of years between October 31st , 2007 and the customers birthdate. Determine which customers purchased a bicycle by using the salesorderheader and salesorderdetail tables and the productsubcategoryid. Merge these pieces of information together and flag each customer what purchased a bike with a 1 and a 0 otherwise. Export the final table as a .csv so you can read it into R

Hints Write a query that selects customerid, territoryid, TotalPurchaseYTD, DateFirstPurchase, MaritalStatus, YearlyIncome, Gender, TotalChildren, NumberChildrenAtHome Education, Occupation, HomeOwnerFlag, NumberCarsOwned, and CommuteDistance from the customer table and the person table joined together In the same query calculate age by subtracting the customers birthdate from October 31st, 2007. Hint: Use the substr() function to format the birthdate as yyyy-mm-dd and substract that value from 2007-10-31. Write a query that pulls salesorderid, customerid, productid, and productsubcategoryid from salesorderheader and salesorderdetail and product tables where the productsubcategoryid is equal to 1, 2, or 3 (Why?) You may want to create tables from these queries and store them in the database instead of doing all of this in one query. Write a query that gets all of the predictor variables from your first query, and creates an additional column that uses a CASE statement to flag a bike buying customer using your second query. Create a table for your final dataset and use FILE >> EXPORT >> Table(s) as CSV file. . . in DB Browser for SQLite.

please note:

Accessing Data Hints: Task Open the AdventureWorks.db file in SQLite or DB Browser for SQLite. Hints Download the AdventureWorks.db file Open DB Browser for SQLite. Open the Database in DB Browser for SQLite. II. Exploring Data Hints: Tasks Locate the required attributes in their respective tables using the Schema diagram and the Data Dictionary. Explore the following tables and make note of any joins needed using the information from the Requirements document. i. customer ii. person iii. salesorderheader iv. salesorderdetail v. product Hints Find these tables in the schema, and note the available columns. Find these tables in the data dictionary and note the columns and their attributes Explore these tables in DB Browser for SQLite. Make note of any inconsistencies (e.g., column name mismatches, columns that appear in the database but not in the schema or dictionary). Think about how these tables and attributes might help you to determine who might buy a bike, how you could define which customers purchased a bike, and how you might be able to join these tables together if necessary.