SQL Challenge: Employee Salary Analytics

Question

SQL Challenge: Employee Salary Analytics

Piyushbhatt

Old Hand

Points: 374
More actions
December 12, 2023 at 8:44 am

#4344388

onsider an employees table:
employee_id (int)
employee_name (varchar)
department (varchar)
joining_date (date)
salary (decimal)
Highest Earner by Department: Find the employee with the highest salary in each department.
Average Salary by Department and Year: Calculate the average salary for each department per year.
Longest Serving Employee: Identify the employee with the longest tenure.
Please provide concise SQL queries for these analyses, emphasizing simplicity and efficiency. Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

You appear to be asking us to do your assignment for you, am I right? That table structure does not exist in the real world

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

I asked ChatGPT:

WITH RankedEmployees AS (
  SELECT
    employee_id,
    employee_name,
    department,
    joining_date,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS RowNum
  FROM
    employees
)
SELECT
  employee_id,
  employee_name,
  department,
  joining_date,
  salary
FROM
  RankedEmployees
WHERE
  RowNum = 1;

SELECT
  department,
  YEAR(joining_date) AS joining_year,
  AVG(salary) AS average_salary
FROM
  employees
GROUP BY
  department,
  YEAR(joining_date);

SELECT TOP 1
  employee_id,
  employee_name,
  department,
  joining_date
FROM
  employees
ORDER BY
  joining_date ASC;

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Piyushbhatt wrote:

onsider an employees table:
employee_id (int) employee_name (varchar) department (varchar) joining_date (date) salary (decimal) Highest Earner by Department: Find the employee with the highest salary in each department.
Average Salary by Department and Year: Calculate the average salary for each department per year.
Longest Serving Employee: Identify the employee with the longest tenure.
Please provide concise SQL queries for these analyses, emphasizing simplicity and efficiency. Thanks!

Sorry... This looks like a test from some tutorial or other practice website. If you want to earn a badge from them, please do you own work. At least try.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

Just to add to what Phil said - that SHOULDN'T exist in the real world. I have run across some things that make me scratch my head like storing numeric serial numbers in CHAR(31) columns or having an application limit the input to 20 characters and the database column that the value is stored in is a VARCHAR(MAX). Or my all-time favorite thing - a table with 2 DATETIME columns where one is for the date and the other is for the time. That one is a 3rd party tool so I can't fix the database design on it, but there are some odd design decisions that make their way out to the real world and then the experienced DBA's are left scratching their heads when they need to support the system. Can't change it because the app will break. Can't leave it because the DBA sanity will break.

I do recommend that learning this stuff on your own though is a better use of your time. Asking for answers on a forum will not help you in the real world. If you get a job and they ask for this sort of stuff, they are not going to want to wait for days to get an answer. They will want quick results. Or worse - you get called into a meeting to discuss a similar data set and need to write the TSQL in real time during a screen sharing session to get the results. I have had to do that before and it was pretty easy to provide the results they were looking for. Then we did some tweaks on the call to get the query to work exactly like they wanted, exported to excel and fired it off to them for review.

It is a real-world skill that may be required as a DBA or DB Developer.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.