Les 5 meilleures questions d'entretien pour le CTE SQL

Voici cinq questions (et leurs solutions !) sur les CTE qui vous seront (probablement) posées lors d'un entretien.

Je ne dis pas que vous aurez ces questions d'entretien sur les CTE SQL à chaque entretien d'embauche. Mais lorsque ce sera le cas, elles seront probablement du type des cinq que je vais vous présenter ici.

En dehors des questions théoriques sur les CTE, il n'y a pas beaucoup de variations dans les scénarios de CTE évalués par les recruteurs. Passez en revue ces cinq exemples, et vous aurez une bonne base pour réussir votre entretien !

Pour cela, vous devez savoir ce que sont les expressions de table communes SQL et comment fonctionne leur syntaxe.

Prêt à jeter un coup d'œil aux questions ? C'est parti !

Tableau pour les questions 1, 2 et 3

Pour la première série de questions, nous allons utiliser le tableau employees. Voici à quoi il ressemble :

id	first_name	last_name	department	salary	manager_id
1	Angelika	Voules	Marketing	5,293.74	2
2	Rozelle	Swynley	Marketing	8,295.08	18
3	Warren	Willey	Engineering	9,126.72	19
4	Lynelle	Whiten	Management Board	10,716.15	NULL
5	Consolata	Roman	Legal	8,456.06	4
6	Hoebart	Baldock	Research and Development	4,817.34	20
7	Starlene	Watkiss	Accounting	6,541.48	4
8	Barde	Ribbens	Marketing	4,852.87	2
9	Lorne	Philipsen	Engineering	7,235.59	3
10	Pedro	Naldrett	Research and Development	5,471.62	20
11	Brina	Dillinger	Marketing	6,512.17	2
12	Verile	Sonley	Research and Development	4,574.41	20
13	Noble	Geerling	Research and Development	8,391.18	20
14	Garey	MacAdam	Accounting	3,829.88	7
15	Theo	Sorrell	Engineering	6,441.67	3
16	Erminie	Gelling	Research and Development	8,590.70	20
17	Loralie	Koop	Accounting	5,248.46	7
18	Cal	Andrey	Management Board	11,258.82	NULL
19	Quincey	Gamell	Management Board	11,366.52	NULL
20	Janith	McGiffie	Research and Development	7,428.83	19

Que vous disent les données ? La première ligne, par exemple, indique qu'Angelika Voules travaille au service Marketing et que son salaire est de 5 293,74 euros. Son patron a un ID de manager de 2 ; recherchez-le dans la colonne id et vous verrez que le patron d'Angelika Voules est Rozelle Swynley.

Il y a trois lignes avec des valeurs NULL dans la colonne manager_id:

id	first_name	last_name	department	salary	manager_id
4	Lynelle	Whiten	Management Board	10,716.15	NULL
18	Cal	Andrey	Management Board	11,258.82	NULL
19	Quincey	Gamell	Management Board	11,366.52	NULL

Cela signifie que ces trois-là employees n'ont pas de managers. C'est logique puisqu'ils font tous les trois partie du conseil d'administration.

Maintenant, voyons les questions de l'entretien.

Question d'entretien 1 : Trouver le salaire moyen par département

À l'aide du tableau employees, affichez tous les employés, leurs départements, leurs salaires et le salaire moyen dans leur département respectif. Classez le résultat par département.

Solution Requête

WITH avg_salary AS (
		SELECT	AVG(salary) AS average_salary,
				department
		FROM employees
		GROUP BY department)
		
SELECT	e.first_name,
		e.last_name,
		e.department,
		e.salary,
		avgs.average_salary	
FROM employees e
JOIN avg_salary avgs
ON e.department = avgs.department
ORDER BY department;

Explication de la requête de solution

Cette requête utilise un CTE nommé avg_salary pour calculer le salaire moyen par département. L'instruction SELECT du CTE regroupe les lignes par département et utilise la fonction d'agrégation AVG() pour calculer la moyenne de chaque département.

Une fois ce résultat obtenu, nous le combinons avec d'autres colonnes de la table employees pour compléter la réponse à la question de l'entretien. Pour ce faire, nous joignons la table employees avec le CTE comme nous le ferions avec deux tables. Nous sélectionnons les colonnes first_name, last_name, department, et salary dans la table employeeset la colonne average_salary dans le CTE. Pour des raisons de commodité, nous utilisons des alias pour la table et le CTE. Enfin, nous classons le résultat par département.

Le tableau de résultats

Voici à quoi ressemble le résultat :

first_name	last_name	department	salary	average_salary
Garey	MacAdam	Accounting	3,829.88	5,206.61
Loralie	Koop	Accounting	5,248.46	5,206.61
Starlene	Watkiss	Accounting	6,541.48	5,206.61
Warren	Willey	Engineering	9,126.72	7,601.33
Lorne	Philipsen	Engineering	7,235.59	7,601.33
Theo	Sorrell	Engineering	6,441.67	7,601.33
Consolata	Roman	Legal	8,456.06	8,456.06
Cal	Andrey	Management Board	11,258.82	11,113.83
Quincey	Gamell	Management Board	11,366.52	11,113.83
Lynelle	Whiten	Management Board	10,716.15	11,113.83
Angelika	Voules	Marketing	5,293.74	6,238.47
Rozelle	Swynley	Marketing	8,295.08	6,238.47
Barde	Ribbens	Marketing	4,852.87	6,238.47
Brina	Dillinger	Marketing	6,512.17	6,238.47
Noble	Geerling	Research and Development	8,391.18	6,545.68
Erminie	Gelling	Research and Development	8,590.70	6,545.68
Verile	Sonley	Research and Development	4,574.41	6,545.68
Pedro	Naldrett	Research and Development	5,471.62	6,545.68
Hoebart	Baldock	Research and Development	4,817.34	6,545.68
Janith	McGiffie	Research and Development	7,428.83	6,545.68

L'explication du résultat

Ce tableau nous indique, par exemple, que le salaire de Starlene Watkiss est de 6 541,48 et que le salaire moyen de son service (comptabilité) est de 5 206,61.

first_name	last_name	department	salary	average_salary
Starlene	Watkiss	Accounting	6,541.48	5,206.61

Vous pouvez interpréter les autres lignes du résultat de la même manière.

Question d'entretien 2 : trouver le salaire le plus élevé par service

Trouvez l'employé dont le salaire est le plus élevé dans chaque service. Indiquez son nom, son prénom, son salaire et son service.

Solution à la question

WITH highest_salary AS (
		SELECT	first_name,
				last_name,
				department,
				salary,
				RANK () OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
		FROM employees)

SELECT	first_name,
		last_name,
		salary,
		department
FROM highest_salary
WHERE salary_rank = 1;

Explication de la requête de solution

Cette fois-ci, le CTE est nommé highest_salary. Nous l'utilisons pour classer les employés de chaque département par salaire. Comment ? En utilisant la fonction fenêtre RANK().

Nous séparons les données par la colonne département. Cela signifie que nous classons les salaires uniquement au sein de chaque service, et non dans l'ensemble de l'entreprise. Les données sont triées en fonction de la colonne salaire dans l'ordre décroissant, car nous voulons que le plus haut salary dans le département ait le rang 1.

Nous sélectionnons également plusieurs colonnes de la table employees dans le même CTE. Nous avons besoin des données de ce CTE dans l'instruction suivante SELECT.

Et dans cette instruction SELECT, nous sélectionnons les colonnes nécessaires pour répondre à la question de l'entretien. Toutes les colonnes proviennent du CTE. Nous filtrons le résultat à l'aide d'une clause WHERE pour obtenir uniquement les lignes dont le rang de salaire est 1, c'est-à-dire les lignes dont le salaire est le plus élevé.

Le tableau de résultats

first_name	last_name	salary	department
Starlene	Watkiss	6,541.48	Accounting
Warren	Willey	9,126.72	Engineering
Consolata	Roman	8,456.06	Legal
Quincey	Gamell	11,366.52	Management Board
Rozelle	Swynley	8,295.08	Marketing
Erminie	Gelling	8,590.70	Research and Development

L'explication du résultat

Il n'est pas trop difficile d'interpréter le résultat. Prenons l'exemple de cet employé :

first_name	last_name	salary	department
Consolata	Roman	8,456.06	Legal

Les données affichées nous indiquent que Consolata Roman a le salaire le plus élevé du département juridique, soit 8 456,06.

Passons à la troisième question d'entretien SQL CTE.

Troisième question d'entretien : trouver tous les employés sous la responsabilité d'un responsable spécifique

Trouvez tous les employés travaillant directement ou indirectement sous la responsabilité de l'employé dont l'ID est 18.

Solution Requête

WITH RECURSIVE subordinates AS (
		SELECT	id,
				first_name,
				last_name,
				manager_id
		FROM employees
		WHERE id = 18

	UNION

		SELECT	e.id,
				e.first_name,
				e.last_name,
				e.manager_id
		FROM employees e
JOIN subordinates s
ON e.manager_id = s.id
)
		
SELECT *
FROM subordinates
WHERE id != 18;

Explication de la requête de solution

Dans cet exemple, il ne s'agit pas d'un simple ETC ordinaire, mais d'un ETC récursif pour obtenir le résultat. La différence de syntaxe est WITH RECURSIVE au lieu de simplement WITH. Le CTE est nommé subordinatespuis vient l'instruction SELECT, comme dans les CTE non récursifs.

Cette instruction sélectionne certaines colonnes de la table employees mais uniquement pour l'employé dont l'ID est 18. Ensuite, nous utilisons l'opérateur UNION pour lier le résultat de cette instruction SELECT au résultat d'une autre instruction SELECT. Pour cela, les deux instructions SELECT doivent avoir les mêmes colonnes.

La deuxième instruction SELECT du CTE sélectionne les colonnes où manager_id (de la table employees) est égale à id (de la CTE).

Nous obtenons toutes les données de l'employé dont l'ID est 18. Ensuite, nous trouvons les subordonnés directs, et par récurrence, nous trouvons les subordonnés des subordonnés, jusqu'à ce que nous descendions dans la hiérarchie de l'organisation.

Ensuite, nous récupérons les colonnes du CTE subordonnés et supprimons l'employé dont l'ID est 18. Le tour est joué !

Le tableau des résultats

id	first_name	last_name	manager_id
2	Rozelle	Swynley	18
1	Angelika	Voules	2
8	Barde	Ribbens	2
11	Brina	Dillinger	2

L'explication des résultats

Le patron direct de Rozelle Swynley est l'employé dont l'ID du manager est 18. Mais Rozelle a aussi des subordonnés. Il s'agit d'Angelika Voules, de Barde Ribbens et de Brina Dillinger. Nous le savons parce que le tableau montre que l'ID de leur manager est le 2, qui est l'ID de Rozelle Swynley. Ce sont les subordonnés directs de Rozelle Swynley ; ils sont également les subordonnés indirects de l'employé dont l'ID est 18. L'employé dont l'ID est 18 est Cal Andrey, qui n'apparaît pas dans le résultat car nous le filtrons dans la clause WHERE.

Tableaux utilisés pour les questions 4 et 5

Pour les deux dernières questions d'entretien, nous utiliserons les tables customers et orders.

La table customers ressemble à ceci :

id	first_name	last_name
1	Simon	Paulson
2	Dylan	Bobson
3	Reb	Mackennack

Le tableau orders tableau est un peu plus grand :

id	order_date	order_amount	customer_id
1	2021-10-01	42.12	3
2	2021-10-01	415.63	1
3	2021-10-02	84.99	2
4	2021-10-02	28.96	3
5	2021-10-02	54.31	1
6	2021-10-03	74.26	1
7	2021-10-03	77.77	2
8	2021-10-03	55.70	3
9	2021-10-04	16.94	3
10	2021-10-04	51.44	1
11	2021-10-05	41.58	3
12	2021-10-06	95.00	1

Cette deuxième table contient des données sur les commandes passées. La colonne customer_id est une clé étrangère à la clé primaire de la première table, ce qui nous permet d'identifier quel client a passé quelle commande. Prenons la première ligne comme exemple :

id	order_date	order_amount	customer_id
1	2021-10-01	42.12	3

Il s'agit d'une commande passée le 1er octobre 2021. La valeur des marchandises commandées est de 42,12, et la commande a été passée par le client dont l'ID est 3. D'après le tableau customersvous voyez qu'il s'agit de Reb Mackennack.

Voyons les tâches à résoudre à l'aide de ces tables.

Question d'entretien 4 : trouver le nombre moyen de commandes

Cette question d'entretien vous demande d'utiliser une expression de table commune SQL pour trouver le nombre moyen de commandes par client.

Requête de solution

WITH orders_count AS (
		SELECT	customer_id,
				COUNT(*) AS no_of_orders
		FROM orders
		GROUP BY customer_id)

SELECT	AVG(no_of_orders) AS avg_no_of_orders
FROM orders_count;

Explication de la requête de solution

Pour obtenir le résultat, il faut d'abord compter le nombre de commandes par client. Pour ce faire, nous utilisons le CTE orders_count et la fonction COUNT() qu'il contient. Cette fonction compte le nombre de lignes dans la table orders. Comme nous ne sommes pas intéressés par le nombre total de commandes mais plutôt par le nombre de commandes par client, je regroupe le résultat par la colonne customer_id.

Maintenant que nous avons le nombre de commandes, il est facile de calculer le nombre moyen. Il suffit d'utiliser la fonction AVG() dans l'instruction SELECT, et vous obtenez la réponse à la question de l'entretien.

Le tableau des résultats

avg_no_of_orders
4

L'explication du tableau des résultats

Un tableau très petit nécessite une explication très courte. Le tableau ci-dessus montre simplement que le nombre moyen de commandes par client est de quatre.

Question d'entretien 5 : Trouvez le nombre de jours consécutifs avec commande

Dans cet exemple de CTE, vous devez calculer combien de jours consécutifs chaque client passe une commande. Il vous demande essentiellement de calculer la longueur d'une série contiguë sans interruption. Notez que toutes les commandes sont passées au cours du même mois.

Requête de solution

WITH groupings_by_date AS (
	SELECT	c.id,
			c.first_name,
			c.last_name,
			RANK() OVER (PARTITION BY c.id ORDER BY o.order_date) AS row_number,
			o.order_date,
			EXTRACT(DAY FROM o.order_date) - RANK() OVER (PARTITION BY c.id ORDER BY o.order_date) AS date_group
FROM customers c
JOIN orders o
ON c.id = o.customer_id
)

SELECT	id,
		first_name,
		last_name,
		COUNT(*) AS orders_in_row
FROM groupings_by_date
GROUP BY id, first_name, last_name, date_group;

Explication de la requête de solution

Cette requête assez longue commence par un CTE. Elle sélectionne certaines colonnes dans les tables customers et orders. Il y a une fonction de fenêtre RANK() comme dans la question 2. Cette fois, nous utilisons cette fonction pour attribuer un numéro de ligne à chaque commande d'un même client. Pour cette raison, nous partitionnons les données par l'id de la colonne. Le classement est effectué en fonction de la date de la commande (nous voulons que le classement soit séquentiel).

L'autre fonction utilisée ici est EXTRACT(). Son but est d'extraire la partie jour de la date de la commande afin d'en déduire le numéro de ligne.

Pourquoi faisons-nous cela ? Nous donnons simplement à un groupe de commandes consécutives un date_group commun. Si vous exécutez uniquement ce CTE, les deux premières lignes du résultat ressemblent à ceci :

id	first_name	last_name	row_number	order_date	date_group
1	Simon	Paulson	1	2021-10-01	0
1	Simon	Paulson	2	2021-10-02	0

Comme vous pouvez le voir, Simon Paulson a passé des ordres le 1er octobre et le 2 octobre 2021. Comme ils ont été passés deux jours de suite, ils appartiennent au même date_group.

Comment l'obtient-on ? C'est le jour extrait du site order_date moins le site row_number.

Dans la première ligne, le jour de la date '2021-10-01' est 1. Le numéro de ligne est également 1. Donc, c'est 1-1 = 0, ce qui est également la valeur dans le date_group. La deuxième ligne est 2-2 = 0, ce qui est le même date_group que ci-dessus.

La valeur spécifique de date_group n'a pas vraiment d'importance ! Il importe seulement que les jours consécutifs aient la même valeur date_group. Il s'agit d'une petite astuce pour calculer la longueur d'une série. Elle fonctionne car, si les commandes sont passées tous les jours, la différence entre le nombre de jours dans order_date et la valeur dans row_number est toujours la même pour le même client.

Notez que ces données n'apparaissent nulle part. Je vous les montre simplement pour faciliter l'explication de ce que fait le CTE ici.

Il est important de préciser que cette astuce ne fonctionne que si vos données se situent toutes dans le même mois. Si la série, par exemple, commence le 2021-10-31 et va jusqu'au 2021-11-01, l'astuce ne fonctionne pas ; ces deux jours, bien qu'ils soient l'un après l'autre, n'appartiendront pas au même date_group. Vous devez donc comprendre vos données avant de décider d'utiliser cette astuce pour obtenir la longueur d'une série.

Une fois que vous avez le CTE, utilisez-le comme une table dans l'instruction SELECT. Je vais utiliser la fonction COUNT() pour obtenir le nombre de commandes dans une ligne. Je veux voir le résultat pour chaque client, c'est pourquoi je regroupe les données par id, first_name et last_name. Je les regroupe également par la colonne date_group, afin que les commandes passées par le même client soient séparées s'il y a un écart entre elles.

Le tableau des résultats

id	first_name	last_name	orders_in_row
1	Simon	Paulson	4
3	Reb	Mackennack	5
2	Dylan	Bobson	2
1	Simon	Paulson	1

L'explication des résultats

Ce tableau montre que Simon Paulson a passé quatre commandes d'affilée. Reb Mackennack l'a fait cinq jours d'affilée, tandis que Dylan Bobson n'a passé que deux commandes d'affilée. Enfin, il y a une commande supplémentaire de Simon Paulson.

Quand les CTE sont-ils utiles ?

Comme vous le voyez dans ces exemples, les CTE sont très utiles lorsque vous devez calculer quelque chose en deux étapes au moins. Vous utilisez un CTE pour préparer le calcul préliminaire, puis vous utilisez simplement l'instruction SELECT, vous faites référence au CTE et vous effectuez un autre niveau de calcul.

Pour les calculs plus complexes, vous pouvez également utiliser plusieurs CTE, voire des CTE imbriqués. C'est similaire aux sous-requêtes, mais les CTE rendent le code plus lisible et facilitent la décomposition du calcul en étapes. Et avec les CTE imbriqués, vous pouvez écrire une requête et la référencer immédiatement comme n'importe quelle autre table.

Certains de ces exemples montrent que vous pouvez également utiliser des fonctions d'agrégation et de fenêtre. Cela rend les CTE plus puissants.

En outre, les CTE sont parfaits pour les données hiérarchiques comme les structures organisationnelles et pour parcourir un graphique. Cependant, vous devez écrire un CTE récursif dans ces cas, comme je l'ai fait dans la Question 3.

Enfin, si vous voulez écrire des requêtes récursives et les utiliser, la première étape est d'apprendre les CTE. Si vous avez besoin d'aide, voici un guide sur la façon d'aborder l'apprentissage des CTE.

Comme vous pouvez le constater, les CTE présentent de nombreux avantages et utilisations pratiques. Ces exemples ne vous donnent qu'un aperçu des solutions à des problèmes pratiques qui prendraient beaucoup plus de temps sans les CTE. Vous en voulez d'autres ? Pas de problème, jetez un œil à d'autres utilisations pratiques des CTE.

Une fois que vous aurez appris les CTE et commencé à les utiliser, essayez d'abord de réfléchir au problème. Décomposez les étapes du calcul, puis traduisez cette logique en un code d'expression de table commune SQL. Les CTE conviennent parfaitement à cette tâche.

Puisqu'il s'agit d'une version des tables temporaires, soyez clair lorsque vous les nommez : utilisez un nom qui indique immédiatement ce que fait le CTE et soyez cohérent avec les conventions de dénomination. En général, vous souhaitez suivre les meilleures pratiques en matière de CTE, qui facilitent la lecture de votre code (et de celui des autres !).

Vous voulez en savoir plus sur les expressions de table commune SQL ?

Que vous prépariez un entretien d'embauche en SQL, que vous souhaitiez vous améliorer dans votre travail actuel ou que vous souhaitiez simplement apprendre quelque chose de nouveau pendant votre temps libre, jetez un coup d'œil à notre Requêtes récursives cours.

Vous y trouverez une approche systématique des CTE, expliquant la théorie qui les sous-tend, vous montrant la syntaxe et vous donnant d'autres exemples à mettre en pratique. L'ensemble du cours fait partie de la piste SQL avancé qui couvre deux sujets supplémentaires : les fonctions de fenêtre (que nous avons utilisées dans cet article) et les extensions GROUP BY. Bonne chance !