Data Analysis using SQL, Pandas, Pure Python
Budget: $30 – $250 CAD
Use three approaches(1-SQL, 2-Pandas, 3-Pure Python) to answer the questions:
The attached dataset shows the advertising customers in different segments together with the latest current deals, how long they have been a customer, and which sales representative is servicing the customer.
Using the sample data in the CSV file provided, please answer the questions below: 1. Possible Data anomalies?
2. Which Sales REP has a better performance? David/John/ Data insufficient? Please explain
3. Write a program using SQL/Python/Pandas(using all) to identify (based on current deals): - The most profitable customer for each REP
- The least profitable customer for each REP
- The second most profitable customer for each REP
- Repeat the above 3 for each REP in each Customer Segment
- Argue what other information is necessary apart from balance to get a complete picture of profitability
4. Write a program using SQL/Python/Pandas (using all) to identify the most profitable sector between TVs, radio, and Cinemas. What are some different ways of assessing this performance metric (KPI)?
5. If a new Banking customer is interested in opening an account with us, what is a reasonable CURRENT_DEALS we can expect that customer to maintain? Please explain.
6. Write a pandas/python/sql program to sort customers from highest balance to lowest: - For each Sales Rep
- For each Sales Rep in the below CUSTOMER_SINCE buckets:
1 – 5yrs; 6 – 10 yrs; 11- 20 yrs; Greater than 20 yrs
The attached dataset shows the advertising customers in different segments together with the latest current deals, how long they have been a customer, and which sales representative is servicing the customer.
Using the sample data in the CSV file provided, please answer the questions below: 1. Possible Data anomalies?
2. Which Sales REP has a better performance? David/John/ Data insufficient? Please explain
3. Write a program using SQL/Python/Pandas(using all) to identify (based on current deals): - The most profitable customer for each REP
- The least profitable customer for each REP
- The second most profitable customer for each REP
- Repeat the above 3 for each REP in each Customer Segment
- Argue what other information is necessary apart from balance to get a complete picture of profitability
4. Write a program using SQL/Python/Pandas (using all) to identify the most profitable sector between TVs, radio, and Cinemas. What are some different ways of assessing this performance metric (KPI)?
5. If a new Banking customer is interested in opening an account with us, what is a reasonable CURRENT_DEALS we can expect that customer to maintain? Please explain.
6. Write a pandas/python/sql program to sort customers from highest balance to lowest: - For each Sales Rep
- For each Sales Rep in the below CUSTOMER_SINCE buckets:
1 – 5yrs; 6 – 10 yrs; 11- 20 yrs; Greater than 20 yrs