5 kyu

For this challenge you need to create a RECURSIVE Hierarchical query. You have a table employees of employees, you must order each employee by level. You must use a WITH statement and name it employee_levels after that has been defined you must select from it.

A Level is in correlation what manager managers the employee. e.g. an employee with a manager_id of NULL is at level 1 and then direct employees with the employee at level 1 will be level 2.

employees table schema

  • id
  • first_name
  • last_name
  • manager_id (can be NULL)

resultant schema

  • level
  • id
  • first_name
  • last_name
  • manager_id (can be NULL)

Solution:

with recursive employee_levels(level, id, first_name, last_name, manager_id) as
(select
1 as level,
id, first_name, last_name, manager_id
from employees
where manager_id is null
union allselect level + 1,
e.id, e.first_name, e.last_name, e.manager_id
from employees e, employee_levels e1
where e.manager_id = e1.id
)
select level, id, first_name, last_name, manager_id
from employee_levels;

Other Solution:

WITH RECURSIVE employee_levels(id, first_name, last_name, manager_id, level) AS (
SELECT e.*, 1
FROM employees e
WHERE e.manager_id IS NULL
UNION ALL
SELECT e.*, el.level + 1
FROM employees e, employee_levels el
WHERE e.manager_id = el.id
)
SELECT *
FROM employee_levels el
ORDER BY el.level

SQL Basics: Simple Hierarchical structure | Codewars

Reference

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Create one exe file from Java Swing Application with several jar files.

How to write my first Java Program

P vs NP and the $1M Millennium Prize Problems

GitLab, Elm and Go — all my mistakes

Man skiing down hill

Learn Kotlin: Functions

Create a Python Abstract Class: A Step-By-Step Guide

Increase Test Automation Coverage With SmartBear Training

The Dark Side of Flutter: What Frustrations Should You Expect?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data

More from Medium

SQL Introduction 1

An internal hard drive

Embedded SQL and Dynamic SQL a

Few tips to write an effective SQL query.

Basic commands for Oracle SQL