Technical Assessment - Business System Analyst

The questions below are to test your knowledge of SQL databases and ability to write reporting queries, while understanding the data and paying attention to data quality.

1. You are presented with a Company Sales database comprised of the following tables: the tables contain the following data. Namely:

Products table

ProductID	Name	UnitCostPrice	UnitRetailPrice
1	Chart	999	1449
2	Gauge	599	699
3	Map	599	649

Salesperson table

PersonID	Name	Country
101	Andy	Canada
102	Bob	U.S.A.
103	Matthew	France

Customers table

CustomerID	Company	City
10089	IBM	Toronto
24535	Johnsons	New York
33555	Goodies	Paris

Geographic Location table

City	Country	Continent
Toronto	Canada	North America
New York	US	North America
Paris	France	Europe

Orders table

OrderID	CustomerID	PersonID	ProductID	Quantity	Discount
1	75353	103	1	5	0%
2	24535	102	1	20	5%
3	10089	101	3	3	10%
4	10089	101	1	55	20%
5	33555	101	2	2

Question 1.1: Based on the relationships defined, write a query that shows the total sales amount after discount by each salesperson for each product.

Question 1.2: Based on the relationships defined, write a query that shows all the products ordered by North America customer, including number of units and total sales amount after discount

Question 1.3: Based on the relationships defined, write a query that shows the top 2 highest total sales amount after discount with company by each product.

Question 1.4: Based on the relationships defined, write a query that shows the total profit amount in dollars and as a percentage, made on all the sales of Charts by Continent, Country and salesperson. Also include number of units, total cost amount and total sales amount after discount in you code.

1. A store owner has collected information on sales from the 3 stores he owns for 10 years. The Data Dictionary is shown below

Transaction Code	Unique transaction ID
Store Number	Unique Store Number
Client ID	Unique Client ID
Item ID	Unique Item ID
Item Quantity	Number of Items bought together
Item Price	Price of a Single Item
Date and Time of Purchase	Timestamp of the Purchase
Payment Method	Cash, Credit Card, Debit Card, Cheque

What information or analysis can be performed with the information provided

that might generate value to the business.

Assumption: a transaction code entitles the purchase of a single type of item

1. What will be the query in SQL if I wish to return all the records when there is a match in any of the tables?
2. Write a SQL query to get the third highest salary of a female employee older than 30 from Employee_Details table as illustrated below.

Employee_Id	EmployeeName	Employee_Age	EmployeeGender	EmployeeSalary
1	Bob	23	Male	40,000
2	Julia	35	Female	25,000
3	Jonathan	31	Male	18,000
4	Alice	29	Female	48,000
5	Ricardo	32	Male	15,000
6	Stacy	33	Female	45,000
7	Jennifer	30	Female	37,000
8	Alison	43	Male	27,000
9	Julio	28	Male	30,000
10	Estela	35	Female	46,000

1. Using the table above, can you create a graphical representation of the data? This could be done in Excel or any other tool and transfer it over.
2. How would you estimate how many tourists buy shoes in New York every July?
3. What metrics do you use to determine if a project is progressing on track?
4. During analysis, how do you treat missing values?
5. What are the various steps involved in an analytics project?
6. Can you mention a few issues that data analyst usually encounters while analysing the data?