People Analytics using SQL Scalar Function
Nowadays, almost every roles has to work with data in some form or another. Usually, we do it through spreadsheets or databases. But if we can learn a little SQL you can become so much more powerful in your job.
One of the most common database structures today is relational database. Going hand-in-hand with relational databases is Structured Querying Language (SQL). SQL is a powerful and robust tool for extracting relevant and useful data from a large dataset.
Data Analytics spans every aspect of business, from R&D to marketing, accounting, and beyond. One of these aspect is People Analytics.
People Analytics
We can define people analytics as a data-driven approach to managing people at work. People analytics is the collection and application of talent data to improve critical talent and business outcomes.
As HR manager, you can make decisions about your people based on talent data analysis, rather than personal relationships or decision making based on experience. Using People Analytics, managers and senior talent leaders are very helped to unlock the power of talent data — increasing rigor, reducing bias, and improving performance.
SQL Scalar Functions
Probably you understand what is functions and how to code it in Python Programming Language. If you do, create a scalar function in SQL Server is not too hard to master. We can say, SQL server scalar function takes one or more parameters and return a single value.
Why we need a scalar function in SQL?
This function help us simplify our code. Sometimes (or daily :D), we have a complex calculation that appears in many queries. Instead of including the formula in every query, we can create a scalar function that encapsulates the formula. So, we can use it in each query if needed.
Use the CREATE FUNCTION statements:
CREATE FUNCTION function_name (parameter_list)
RETURNS data_type AS
BEGIN
statements
RETURN value
END
Syntax explanation:
- After CREATE FUNCTION, give the name of the function.
- Specify a list of parameters surrounded by parentheses after the function name.
- In RETURNS statement, specify the data type of the return value.
- Finally, include a RETURN statement to return a value inside the body of the function.
Employees Dataset
The Employees sample database was developed by Patrick Crews and Giuseppe Maxia and provides a combination of a large base of data (approximately 160MB) spread over six separate tables and consisting of 4 million records in total. The structure is compatible with a wide range of storage engine types. Through an included data file, support for partitioned tables is also provided.
In addition to the base data, the Employees database also includes a suite of tests that can be executed across the test data to ensure the integrity of the data that you have loaded. This should help ensure the quality of the data during initial load, and can be used after usage to ensure that no changes have been made to the database during testing.
For further information and to install it in MySQL Server, read on this site.
Creating SQL Scalar Function to People Analytics
Before we create SQL Scalar Function, let’s see all tables in employees database.
From this table, we can see employees database has 6 tables: (1) departments, (2) dept_emp, (3) dept_manager, (4) employees, (5) salaries, and (6) titles.
This database also has 2 views: (1) current_dept_emp and (2) dept_emp_latest_date.
In this article, I wanna show you how to create scalar (user-defined) function in SQL. All function below was created using employees databases. So, hopefully the function can help HR Manager to analyze their talent data.
1. Salary Calculator
One of monthly workload in HR department is the things related with salary including tax. You can create simple function in SQL to count take home salary. Let’s make it simple, Take Home Salary = Salary-Tax. If you consider bonus or other reward, you can edit/alter this function.
One of you, maybe want to ask, why I use this query on my function:
READS SQL DATA
DETERMINISTIC
I have 2 reasons. First, when you create a stored function, you must declare either that it is deterministic or that it does not modify data. Second, by default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. For more detail about this issue please read here
Probably, you doubt, is our function created successfully? You have to check on SCHEMAS. If your function appear on Functions, congratulation!! You made it.
As you can see, SalaryAfterTax function is appear on Functions. Because we use employees database, this function is created under employees schema.
This is not the last function I made. I’ll show you another function. So, another function will appear below Functions tab.
Let’s try our new function. From Salaries table, show employer number, salary, and take home pay using SalaryAfterTax Function. See full query below:
Here is the result. Take a look on TakeHomePay Column. This is result of Salary-(Salary*Tax).
2. Bonus Calculator
In the end of year, some company give bonus to all employees or certain staffs. If your company kindly give bonus to all employees, so we need rules of bonus. As HR Manager, you must to act fairly to your employees.
As previous function, I’ll keep it simple enough for giving example. In Employees Database, we have 7 job titles, such as: Senior Engineer, Staff, Engineer, Senior Staff, Assistant Engineer, Technique Leader, and Manager.
Bonus differ on job title level. If you have “Senior”, “Leader”, and “Manager” title, your bonus is 5 percent. But, if you don’t have it, HR Manager still give you bonus, 2 percent. How to convert this rules as SQL Function? Here is the query:
In Employees database, there is Title table and Salaries table. We need to join these column due to we can’t access job title and salary data from one table. Join these table using LEFT JOIN. See full query below:
Here is the result. After read this how-to article, I hope you as HR Manager will never miscalculated bonus again :D
Sometimes, uncertainty feeling comes to us. If you doubt the bonus is correct, use this complex one. Here is query I provide if you wanna check whether percentage of your team bonus is precisely correct. In this query, I use Common Table Expression (CTE).
After you review this output table, may your uncertainty feeling will be omitted.
3. Paid Leave Calculator
Beside salary dan company brand, paid leave is one of competitive values considered when talent apply to a company. At common company, your annual paid leave are 12 days. Assuming, you get 1 day paid leave monthly.
Make it more complex (and fair), your company will give extra 2 paid leave days for leader, manager, and senior position. And your company also cares with female issues. So, every female employees get extra 2 paid leave days whatever her position. Let’s translate this rules to be SQL query:
In Employees database, there is Title table and Employees table. We need to join these column to show employee number, job title, first name, last name, and gender columns.
After we get title and gender columns, use PaidLeave function.
Take a look the result below. See Amabile Gomatam row. As a Senior Engineer, he gets 14 paid leave days (12 days + 2 extra days). But, Valdiodio Niizuma also gets 14 paid leave days. Even she is not a Senior Engineer, she gets extra paid leave days because gender incentive.
Arumugam Ossenbru… gets 16 paid leave days because she is Senior Staff and also get gender incentive (12 days + 2 extra days as Senior level + 2 extra days as gender incentive).
(Bonus) Employee Email Generator
Electronic mail (email) is still used as main medium communication in many company. Email is free, accessible for all devices, serve as a record, and easy to use for all employees generations.
But, one of the challenges is to create unique email for employees. If we use first name and last name, we can meet 2 employees with exactly have same name. As alternative, we can combine name with birth date to create unique email name. Here is the function:
We not only provide email name, but also temporary password. In this function, we create temporary password from the day of hire date and the year of birth date.
Use employees table to try Email and Password generator function.
Here is the result. All of your employees already get proper company email name and also temporary password.
Takeaways
Data Analytics skills is no longer as Data Analyst privilege. Whatever your profession, you have same opportunities to mastering Data Analytics skill. SQL is the one of prominent skills in Data Analytics. In this how-to article, you have read how to create and use SQL Scalar Function.
Thank you and see you on my another articles.
