Data Analyst SQL Exercise
a
Data cleaning and segmentation (estimated completion time: 1.5 hours)
The goal of this exercise is to test your ability to clean and segment large data set and segment the data in a reasonable fashion so the business teams can use.
You will be receiving 2 filtered customer files (customers names and contact info are removed). Please aggregate the files in one file and suggest 3 ways to segment the list.
Please use SQL database for this exercise as much as possible. And please include all the query/code in your response.
Data Manipulation (estimated completion time: 2 hours)
- A lot of the customers in the data you cleaned early have more than one purchases. Please write a query that calculates and shows the total warranty count, total sales, price weighted averaged warranty life from today (in days. Say you have 2 warranties, each was purchased at $200, the first warranty has 200 days left, the second one has 250 days left, then the weighted average life should be 225 days) for each customer.
- Write a query that gives you the following output:
- Each unique customer will only have one row
- In each row, you should have all the basic information (name, phone, address, etc) and the total warranty count, total sales, price weighted averaged warranty life you calculated in (1), and the basic information of all products (format: “Brand-model-PA start date-PA end date-sale amt”).
- For example, if you have the following customer (only key columns are shown here, but you should include all columns):
Then your output table should look like this: