2020: Week 7
As a little aside, Preppin' Data turns 1 year old on Thursday 13th February 2020. To celebrate the day, I just wanted to say thank you to a number of people who make this weekly challenge run:
- Jonathan Allenby - my fellow Dr Prepper, who, without asking a question by a coffee machine in London, we'd never have a Prep based challenge. He's taken on a lot to keep this all running and providing great solution posts from day one
- Tom Prowse - who is well on his way to becoming a Dr Prepper in his own right. Lots of great solution posts and filling the gaps for Jonathan and I when we need support - thank you.
- Jenny Martin - throwing up challenges, spotting issues and now providing video walkthroughs of the challenges at dataprepsolutions.com
- Bona Wang, Donna Coles, Jack Parry, Kate Brown and Rosario Gauna for finding errors and posing new challenges and generally encouraging Jonathan and I in the early days to keep us going.
Enough of the thank you's, back to why you are here - Week 7's challenge. This week one of our supporters, Bona, and I have collaborated on an idea for a challenge around a common issue when processing data - counting something that no longer exists in the dataset. With GDPR and other consumer focused legislation becoming more prominent, customer records are disappearing rapidly once a customer leaves a business (for good reason). However, as an analyst, this makes certain tasks more challenging, like counting how many 'x' there were in a business at anyone time.
For this challenge we are looking at employees who have left the business over time as well as understanding who still remain.
- Input Data
- Create a full list of employees
- Create an end date for current employees that are yet to leave (use 1st March 2020) to help with calculations
- Create a scaffold month that can have the Employee data joined on to it
- Form your measures for analysis:
- Count number of employees in employment by the company that month.
- If the employee leaves in that month, remove them from the reporting within the month of leaving.
- Avg Salary per employee per month
- Total Salary paid by the company per month
- Output the data