The SQL learning materials utilized in this repository have been sourced from Data With Danny, an online website for mastering SQL basics and applying them in various case studies.
While there is a multitude of online resources available to learn SQL fundamentals, many of them fall short of offering case studies that delve into real business requirements and demonstrate how SQL can fulfill these data needs for corporations.
Below are summaries of what I learned and the type of analytical questions I answered via case studies.
- Order By
- Counts and Distinct Valves
- Identify Duplicate Records
- Summary Statistics
- Distribution Functions
- Health Analytics Mini Case Study
- Joins (LEFT, INNER, LEFT-SEMI JOIN, ANTI-JOIN)
- Window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(), CUME_DIST()) LAG & LEAD & COALESCE
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW- RANGE, ROWS, GROUPS
- Recursive CTEs
Here are the types of questions and complexity that we will address in our case study:
- What is the full name of the employee with the highest salary?
- How many current employees have the equal longest time in their current positions?
- Which department has the highest number of current employees?
- What is the largest difference between minimimum and maximum salary values for all current employees?
- How many male employees are above the average salary value for the Production department?
- Which title has the highest average salary for male employees?
- Which department has the highest average salary for female employees?
- Which department has the most female employees?
- What is the gender ratio in the department which has the highest average male salary and what is the average male salary value for that department?
- HR Analytica want to change the average salary increase percentage value to 2 decimal places - what will the new value be for males for the company level dashboard?
HR Analytica want to perform an employee churn analysis and wants you to help them answer the following questions using your generated views:
- How many employees have left the company?
- What percentage of churn employees were male?
- Which title had the most churn?
- Which department had the most churn?
- Which year had the most churn?
- What was the average salary for each employee who has left the company?
- What was the median total company tenure for each churn employee just bfore they left?
- On average, how many different titles did each churn employee hold?
- What was the average last pay increase for churn employees?
- What proportion of churn employees had a pay decrease event in their last 5 events?
- How many current employees have the equal longest overall time in their current positions (not in years)?
The HR Analytica team also want to perform a management analysis and need answers for these questions:
- How many managers are there currently in the company?
- How many employees have ever been a manager?
- On average - how long did it take for an employee to first become a manager from their the date they were originally hired?
- What was the most common titles that managers had just before before they became a manager?
- On average - how much more do current managers make on average compared to all other employees?
TBD
TBD
TBD
TBD
TBD
TBD
TBD
TBD