HardSQL challenge #267DATEORDER BYCALCULATION

Calculate Employee Tenure in Days and Sort by Longest

Return every employee with the number of days since their hire date, longest-serving first.

The challenge runs on the SQLabHub practice database (SQLite) and uses the employees table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find employees with longest tenure.

A correct answer returns 120 rows with 13 columns: id, first_name, last_name, email, department, department_id, title, salary, hire_date, manager_id, city, status, tenure.

Tables used in this challenge

employees 120 rows

id INTEGER (PK) · first_name TEXT · last_name TEXT · email TEXT · department TEXT · department_id INTEGER · title TEXT · salary REAL · hire_date TEXT · manager_id INTEGER · city TEXT · status TEXT

First 3 rows of the employees table
idfirst_namelast_nameemaildepartmentdepartment_idtitlesalaryhire_datemanager_idcitystatus
1WilliamLewiswlewis18@corp.ioCustomer Success7Senior CS Manager617742022-07-24NULLSan Diegoactive
2JohnMillerjmiller51@corp.ioLegal8Paralegal1916942021-08-09NULLSan Diegoactive
3DanielRiveradrivera50@corp.ioEngineering1VP Engineering1350672016-06-06NULLHoustonactive

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

The result has the columns listed above and 120 rows. This query depends on today's date, so its values change from day to day and no fixed rows are shown here.

What you practise

  • Working with dates in SQLite: strftime, julianday and DATE
  • Sorting the result with ORDER BY
  • Deriving a new column from an expression and naming it with an alias

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

julianday difference

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Read from the employees table.
  2. Add the calculated column tenure: julianday('now') - julianday(hire_date).
  3. Sort the result by tenure descending.

Model solution

Checking your session…

What to watch for

julianday('now') is the current date, so the tenure column changes every day and no fixed expected values can be shown. The order stays the same. Divide by 365.25 for years.

  • Date functions. These date functions are SQLite syntax. MySQL uses DATE_FORMAT and DATEDIFF, PostgreSQL uses TO_CHAR, EXTRACT and date subtraction.
  • Calculated columns. Dividing one integer by another truncates the result in SQLite. Multiply one side by 1.0 to get decimals.
  • ORDER BY. Without ORDER BY the row order is not guaranteed, even if it looks stable. Rows that tie on the sort columns can come back in any order.
Solve This SQL Challenge

Keep learning