Module 7 Challenge Part 4

00:00

I will come back to module seven. This is less than 7.9 challenge. Four. Again. We've gone over a lot of these bullet points, but the big bullet point at the bottom is that we're going to create a sequel view that provides salary data related to positions. So this will actually be a pretty interesting challenge.

00:17

And with that said, Let's go take a look at the requirements document.

00:22

Here we are the requirements document.

00:24

This is the position Salary data view challenge on the requirements are as follows utilizing the automatic database created view that exposes all the professional positions and use at the company with the following aggregates.

00:38

A count of members minimum salary, maximum seller, standard deviation and percentile rank.

00:45

An additional instructions include round all decimals to four decimal places. The tables required R E P A employees, E p employees rate and E P. Employee position.

00:57

Views require create a salary view this vote. This view will replace the need to use E P employees rate in the primary query. Now we still include the E P A employees rate table in the requirements because we need that table to make this view.

01:11

The goal is to make the man query easier to design

01:17

Onley most recent or latest salary

01:19

and hence table expression should help in the creation of this view.

01:25

For this view, we have three required columns. Company I D.

01:29

Employee I. D

01:30

and annual salary.

01:32

When the annual salary is not indicated or is know, as is the case on some records, use our hourly rate times regular hours, times 52

01:44

while including the quotes as they're laid out.

01:47

And the 52 is because there are 52 weeks in a year. So what we're doing essentially is we are calculating what they're predicted Annual salary would be, even though they don't have one. They only have an hourly rate

02:02

on then, once we have this view completed will move on to the main viewed, which needs to include the following combs

02:08

position, title member account. So how many members of the employee workforce are have or have that position?

02:16

The average salary, So the average salary for that position, the minimum salary for that position, the maximum salary for that position, the range of that salary for that position, and what that is that that would be the max minus the men.

02:31

The standard deviation. Now there should be a function that you can just run against a column to get the calculation. For that you don't actually have to run the standard deviation formula

02:44

and a percentile rank compared to other positions. Again, you'll be able to use a function for that. You won't have to actually run a percentile rank equation.

02:53

Now use the following tables and views

02:55

you'll need to use the salary view that you created in the first step.

02:59

The E p A employees table, the BP Employees position table and the E P position table

03:05

aggregate your results. Use the fallen functions to achieve your goal.

03:09

Count men max percent rank average an S T D, which is the standard deviation function.

03:17

Now. Before we go over the solution,

03:21

please pause the video and see if you can come up with a solution on your own.

03:25

Now, with that said, let's go ahead and head over and take a look at and walk through the solution.

03:32

I welcome to the solution now, this solution or this challenge vice an interesting way to look at aggregated data in a database and really get some interesting ideas and how things are playing out in your application or in your company. For example, we're gonna look at positions the minimum and the maximum.

03:50

So we'll be able to see if someone with the same position

03:53

has a huge disparity with somebody else with the same position, which might bring up question to the table of why that is

04:00

now. That said, this challenge was kind of complicated, so don't feel too bad if you had some trouble getting to that answer.

04:08

So let's go ahead and walk through this and see what I did to solve that challenge document

04:14

now. The first thing we needed to do was make a salary data view, and the reason we wanted to make this view is to get rid of two complexities that have to be dealt with in one way or another, and I wanted to pull that out of the primary view so that it didn't get too complicated in that zone.

04:30

Those two complexities are that an employee can have multiple salaries

04:34

and, for example, you could have gotten multiple raises and now you have multiple salaries saved to the database

04:42

for this report, though we just always want to use the current salary.

04:46

So we want to be sure to filter each employee to their current or most recent salary.

04:51

The other complexity is that some employees don't even have an annual salary indicated. So we need to run a calculation on their hourly rate times the regular hours Times 52 which is the number of weeks in a year to generate an annual salary

05:09

so that we can just insert that into the table

05:14

and aggregate that against the other numbers as well.

05:16

So I used table expressions in this view. So with employees I selected from E P Employees Rate,

05:25

which I a liest with E. R.

05:27

And I selected the max effective dates.

05:30

So that's gonna give me the most recent salary for any Given employees.

05:36

And then in the next table expression called salary data

05:41

I selected from the U P employees rate and the employees table expression I used above,

05:47

and I

05:48

connected on the company I D. Employee I D and the effective date

05:55

using a inner join,

05:58

and I used the case statement when the annual salary is no, then round the hourly rate times a regular hours times 52 rounded to the fourth decimal place.

06:09

Otherwise, it was just annual salary.

06:12

So that's how I completed the salary data view.

06:15

So if I run this portion, we can see what that looks like.

06:19

Um, but we don't want to create the view

06:23

so

06:26

you can see that we're going to each employee i d. Once

06:29

and then we're getting one annual salary for every employee.

06:32

All right, so once we have that salary view, we had to move on to the next part. So let's take a look at what that looks like.

06:43

So we're using a couple tables in a couple of use here. We're using the E p A employees or the E P Position Pos Aliases Pos,

06:50

where we are joining that to the E P A Employees position table, which is a table that shows all the positions employees have been in.

07:00

They were also joining to the salary data view, which is the view that we made above

07:04

Rubin by the company I d. And the description.

07:09

So we put the company idea on the report, but the description on the report

07:13

and we put the position I D account

07:15

and the count is, of course, will be the number of people at the company that have that position.

07:19

We then use the average function on the annual salary column, and that gives us the average salary within used, the minimum function on the salary table and that are on the salary column. That gives us the minimum same thing with the maximum

07:35

on. Then for the range we do maximal minus minimum.

07:40

And then for the standard deviation. We just call the standard deviation function

07:46

and I title that *** standard deviation and then for the percent rank, that's actually kind of a that's actually a window function. So we have to use that over keyword in there,

07:57

and we ordered by the rounding off the average salary,

08:01

and then we needed some. Multiply that by 100 so that we could change that decimal to a percent Now. You didn't do that. That's perfectly fine. That wasn't end the document to do that, but it was just when people are looking at percents that usually are used to looking at whole numbers, for example, 15%

08:20

in that 0.15

08:22

and then once we run that, we'll get some interesting data.

08:26

So let me go ahead and we're on this.

08:30

And the data we have is, for example, we have a position of operations employee

08:37

and has to members. The average is 52,000 year,

08:41

and the percent rank is zero. That's actually correct, because that is the lowest salary in the database. So they're starting at zero and the highest salary at

08:50

in the databases at 100%. So that's correct. So when you see the zeros and the 100 those air, correct.

08:56

I mean, if you look at someone in the middle you can see, for example,

09:01

that the executives have the highest

09:03

ah, high highest pay, which is, you know, kind of expected.

09:07

And what's a good one to look at? We can see that senior consultant

09:11

has five members and average sourly of 76 9 60 minimum of 62 a maximum of 93. We could see that some consultants are getting paid Ah,

09:22

a little bit more than others.

09:26

And there at the 63% rank

09:28

and back complete this challenge.

09:31

I hope you were able to follow along. I hope you were able to get that working and I hope, see in the next one. Thank you.