DSO552-Week 5 Subqueries, Views, and Data Cleaning in SQL Solved

Week 5: Subqueries, Views, and Data Cleaning in SQL

Parch and Posey Database

 

Subqueries and Views
1.    What is the lifetime average amount spent in USD for the top 10 total spending accounts?

2.    For the customer/account that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?

3.    Which channel was the most frequently used by different accounts?

4.    YOURTURN Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.

Subqueries using ‘WITH’
5.    What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?

6.    For the customer/account that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?

7.    Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.

Data Cleaning
LEFT and RIGHT
8.       In the accounts table, there is a column holding the website for each company. The last three digits specify what type of web address they are using. Pull these extensions and provide how many of each website type exist in the accounts table.

9.       There is much debate about how much the name (or even the first letter of a company name) matters. Use the accounts table to pull the first letter of each company name to see the distribution of company names that begin with each letter (or number).

10.    Use the accounts table and a CASE statement to create two groups: one group of company names that start with a number and a second group of those company names that start with a letter. What proportion of company names start with a letter?

POSITION & STRPOS
11. Use the accounts table to create first and last name columns that hold the first and last names for the primary_poc.

1

CONCAT
12.   Each company in the accounts table wants to create an email address for each primary_poc. The email address should be the first name of the primary_poc last name primary_poc @ company name .com.

13.   You may have noticed that in the previous solution some of the company names include spaces, which will certainly not work in an email address. See if you can create an email address that will work by removing all of the spaces in the account name, but otherwise your solution should be just as in the previous question. (Hint: lookup replace)

14.   We would also like to create an initial password, which they will change after their first log in. The first password will be the first letter of the primary_poc’s first name (lowercase), then the last letter of their first name (lowercase), the first letter of their last name (lowercase), the last letter of their last name (lowercase), the number of letters in their first name, and then the name of the company they are working with, all capitalized with no spaces.

2