Die 5 wichtigsten SQL CTE-Interview-Fragen

Hier finden Sie fünf Fragen (und Lösungen!) zu CTEs, die Ihnen (wahrscheinlich) in einem Vorstellungsgespräch gestellt werden.

Ich behaupte nicht, dass Sie diese SQL-CTE-Fragen in jedem Vorstellungsgespräch gestellt bekommen werden. Aber wenn Sie sie bekommen, dann sind sie wahrscheinlich ähnlich wie die fünf, die ich hier vorstelle.

Abgesehen von theoretischen Fragen zu CTEs gibt es nicht viele Variationen bei den CTE-Szenarien, die von den Interviewern bewertet werden. Wenn Sie diese fünf Beispiele durchgehen, erhalten Sie eine gute Grundlage für ein erfolgreiches Vorstellungsgespräch!

Hierfür müssen Sie wissen, was SQL Common Table Expressions sind und wie ihre Syntax funktioniert.

Sind Sie bereit, einen Blick auf die Fragen zu werfen? Los geht's!

Tabelle für die Fragen 1, 2 und 3

Für die erste Gruppe von Fragen verwenden wir die Tabelle employees. So sieht sie aus:

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

Was sagen Ihnen die Daten? In der ersten Zeile steht zum Beispiel, dass Angelika Voules im Marketing arbeitet und ihr Gehalt 5.293,74 beträgt. Ihr Vorgesetzter hat eine Manager-ID von 2. Schauen Sie in der Spalte id nach, und Sie sehen, dass Angelika Voules Vorgesetzter Rozelle Swynley ist.

Es gibt drei Zeilen mit NULL-Werten in der Spalte 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

Es bedeutet, dass diese drei employees haben keine Manager. Das macht Sinn, da alle drei im Vorstand sind.

Schauen wir uns nun die Interviewfragen an.

Interviewfrage 1: Ermitteln Sie das Durchschnittsgehalt nach Abteilung

Zeigen Sie anhand der Tabelle Mitarbeiter alle Mitarbeiter, ihre Abteilungen, Gehälter und das Durchschnittsgehalt in ihrer jeweiligen Abteilung. Ordnen Sie das Ergebnis nach Abteilung.

Lösung Abfrage

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;

Lösung Abfrage Erläuterung

Diese Abfrage verwendet ein CTE namens avg_salary um das Durchschnittsgehalt nach Abteilung zu berechnen. Die Anweisung SELECT in der CTE gruppiert die Zeilen nach Abteilung und verwendet die Aggregatfunktion AVG(), um den Durchschnitt für jede Abteilung zu berechnen.

Sobald wir das Ergebnis haben, kombinieren wir es mit anderen Spalten aus der Tabelle employees um die Antwort auf die Interviewfrage zu vervollständigen. Zu diesem Zweck verknüpfen wir die Tabelle employees mit dem CTE, wie wir es mit zwei beliebigen Tabellen tun würden. Wir wählen die Spalten first_name, last_name, department, und salary aus der Tabelle employeesund die Spalte average_salary aus der CTE. Der Einfachheit halber verwenden wir Aliasnamen für die Tabelle und die CTE. Schließlich ordnen wir das Ergebnis nach Abteilung.

Die Ergebnistabelle

So sieht das Ergebnis aus:

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

Das Ergebnis Erläuterung

Aus dieser Tabelle geht hervor, dass das Gehalt von Starlene Watkiss beispielsweise 6.541,48 beträgt und das Durchschnittsgehalt in ihrer Abteilung (Buchhaltung) bei 5.206,61 liegt.

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

Sie können die übrigen Zeilen des Ergebnisses auf die gleiche Weise interpretieren.

Interviewfrage 2: Ermitteln des höchsten Gehalts nach Abteilung

Finden Sie den Mitarbeiter mit dem höchsten Gehalt in jeder Abteilung. Geben Sie den Vor- und Nachnamen, das Gehalt und die Abteilung an.

Lösung Abfrage

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;

Lösung Abfrage Erläuterung

Dieses Mal heißt die CTE highest_salary. Wir verwenden sie, um die Mitarbeiter innerhalb jeder Abteilung nach ihrem Gehalt zu ordnen. Und wie? Indem wir die Fensterfunktion RANK() verwenden.

Wir partitionieren die Daten nach der Spalte Abteilung. Das bedeutet, dass wir die Gehälter nur innerhalb der einzelnen Abteilungen und nicht für das gesamte Unternehmen auflisten. Die Daten werden nach der Spalte Gehalt in absteigender Reihenfolge sortiert, da wir wollen, dass die höchste salary in der Abteilung den Rang 1 hat.

Wir wählen auch mehrere Spalten aus der Tabelle employees in der gleichen CTE. Wir benötigen die Daten aus dieser CTE in der nächsten Anweisung SELECT.

Und in der Anweisung SELECT wählen wir die Spalten aus, die für die Beantwortung der Interviewfrage benötigt werden. Alle Spalten stammen aus der CTE. Wir filtern das Ergebnis mit einer WHERE Klausel, um nur die Zeilen zu erhalten, deren Gehaltsrang 1 ist, d.h. die Zeilen mit dem höchsten Gehalt.

Die Ergebnistabelle

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

Das Ergebnis Erläuterung

Es ist nicht allzu schwierig, das Ergebnis zu interpretieren. Nehmen wir diesen Mitarbeiter als Beispiel:

first_name	last_name	salary	department
Consolata	Roman	8,456.06	Legal

Die angezeigten Daten zeigen, dass Consolata Roman mit 8.456,06 das höchste Gehalt in der Rechtsabteilung bezieht.

Kommen wir nun zur dritten SQL CTE-Interviewfrage.

Interviewfrage 3: Alle Mitarbeiter unter einem bestimmten Manager finden

Finden Sie alle Mitarbeiter, die direkt oder indirekt unter dem Mitarbeiter mit der ID 18 arbeiten.

Lösung Abfrage

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;

Erläuterung der Lösungsabfrage

In diesem Beispiel handelt es sich nicht um eine normale CTE, sondern wir verwenden eine rekursive CTE, um das Ergebnis zu erhalten. Der Unterschied in der Syntax ist WITH RECURSIVE statt nur WITH. Die CTE heißt subordinatesund dann kommt die Anweisung SELECT, genau wie bei nicht rekursiven CTEs.

Diese Anweisung wählt bestimmte Spalten aus der Tabelle aus employees aber nur für den Angestellten, dessen ID 18 ist. Dann verwenden wir den Operator UNION, um das Ergebnis dieser Anweisung SELECT mit dem Ergebnis einer anderen Anweisung SELECT zu verknüpfen. Dazu müssen beide SELECT-Anweisungen die gleichen Spalten haben.

Die zweite SELECT -Anweisung im CTE selektiert Spalten, bei denen manager_id (aus der Tabelle employees) gleich der id (aus der CTE) ist.

Wir erhalten alle Daten für den Mitarbeiter mit der ID 18. Dann suchen wir die direkten Untergebenen und durch Rekursion die Untergebenen der Untergebenen, bis wir die gesamte Hierarchie der Organisation durchlaufen haben.

Dann holen wir die Spalten aus dem CTE Untergebene und entfernen den Mitarbeiter mit der ID 18. Das war's!

Die Ergebnistabelle

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

Die Erläuterung der Ergebnisse

Rozelle Swynleys direkter Vorgesetzter ist der Mitarbeiter, dessen Vorgesetzten-ID die 18 ist. Aber Rozelle hat auch Untergebene. Diese sind Angelika Voules, Barde Ribbens und Brina Dillinger. Das wissen wir, weil die Tabelle zeigt, dass die ID ihres Vorgesetzten 2 ist, was Rozelle Swynleys ID ist. Sie sind Rozelle Swynleys direkte Untergebene; sie sind auch indirekte Untergebene des Mitarbeiters mit der ID 18. Der Mitarbeiter mit der ID 18 ist Cal Andrey, der im Ergebnis nicht angezeigt wird, weil wir ihn in der Klausel WHERE herausfiltern.

Für die Fragen 4 und 5 verwendete Tabellen

Für die letzten beiden Interviewfragen verwenden wir die Tabellen customers und orders.

Die Tabelle customers sieht wie folgt aus:

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

Die Tabelle orders Tabelle ist ein bisschen größer:

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

Diese zweite Tabelle enthält Daten über die erteilten Aufträge. Die Spalte customer_id ist ein Fremdschlüssel zum Primärschlüssel der ersten Tabelle, so dass wir feststellen können, welcher Kunde welche Bestellung aufgegeben hat. Nehmen wir die erste Zeile als Beispiel:

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

Es handelt sich um eine Bestellung, die am 1. Oktober 2021 aufgegeben wurde. Der Wert der bestellten Waren beträgt 42,12, und die Bestellung wurde von einem Kunden mit der ID 3 aufgegeben. Aus der Tabelle customerssehen Sie, dass es sich um Reb Mackennack handelt.

Sehen wir uns die Aufgaben an, die mit diesen Tabellen zu lösen sind.

Interviewfrage 4: Finden Sie die durchschnittliche Anzahl der Bestellungen

In dieser Interviewfrage werden Sie gebeten, einen allgemeinen SQL-Tabellenausdruck zu verwenden, um die durchschnittliche Anzahl der Bestellungen pro Kunde zu ermitteln.

Lösung Abfrage

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;

Lösung Abfrage Erläuterung

Um das Ergebnis zu erhalten, zählen Sie zunächst die Anzahl der Bestellungen pro Kunde. Dies geschieht mit Hilfe der CTE orders_count und der darin enthaltenen Funktion COUNT(). Diese Funktion zählt die Anzahl der Zeilen in der Tabelle orders. Da wir nicht an der Gesamtzahl der Bestellungen, sondern an der Anzahl der Bestellungen pro Kunde interessiert sind, gruppiere ich das Ergebnis nach der Spalte customer_id.

Da wir nun die Anzahl der Bestellungen haben, ist es einfach, die durchschnittliche Anzahl zu berechnen. Verwenden Sie einfach die Funktion AVG() in der Anweisung SELECT, und Sie erhalten die Antwort auf die Interviewfrage.

Die Ergebnistabelle

avg_no_of_orders
4

Die Erläuterung der Ergebnisse

Eine sehr kleine Tabelle erfordert eine sehr kurze Erklärung. Die obige Tabelle zeigt einfach, dass die durchschnittliche Anzahl der Bestellungen pro Kunde vier beträgt.

Interviewfrage 5: Finden Sie die Anzahl der aufeinanderfolgenden Tage mit Bestellung

In diesem CTE-Beispiel müssen Sie berechnen, an wie vielen Tagen in Folge jeder Kunde eine Bestellung aufgibt. Im Grunde sollen Sie die Länge einer zusammenhängenden Reihe ohne Lücken berechnen. Beachten Sie, dass alle Bestellungen innerhalb desselben Monats liegen.

Lösung Abfrage

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;

Erläuterung zur Lösungsabfrage

Diese recht langwierige Abfrage beginnt mit einer CTE. Sie wählt bestimmte Spalten aus den Tabellen customers und orders. Es gibt eine RANK() Fensterfunktion wie in Frage 2. Diesmal verwenden wir diese Funktion, um jeder Bestellung innerhalb desselben Kunden eine Zeilennummer zuzuordnen. Aus diesem Grund partitionieren wir die Daten nach der Spalte id. Die Reihung erfolgt nach dem Bestelldatum (wir wollen, dass die Reihung sequentiell ist).

Die andere Funktion, die hier verwendet wird, ist EXTRACT(). Ihr Zweck ist es, den Tagesanteil aus dem Bestelldatum zu extrahieren, damit wir die Zeilennummer davon abziehen können.

Warum tun wir das? Wir geben einer Gruppe von aufeinanderfolgenden Bestellungen einfach eine gemeinsame date_group. Wenn Sie nur diese CTE ausführen, sehen die ersten beiden Zeilen des Ergebnisses wie folgt aus:

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

Wie Sie sehen können, hat Simon Paulson sowohl am 1. Oktober als auch am 2. Oktober 2021 Aufträge erteilt. Da sie an zwei aufeinanderfolgenden Tagen platziert wurden, gehören sie zu derselben date_group.

Wie kommen wir zu diesem Wert? Es ist der Tag, der aus der order_date minus der row_number extrahiert wurde.

In der ersten Zeile ist der Tag des Datums "2021-10-01" gleich 1. Die Zeilennummer ist ebenfalls 1. Es ist also 1-1 = 0, was auch der Wert in date_group ist. Die zweite Zeile ist 2-2 = 0, was dem Wert von date_group entspricht.

Der spezifische Wert von date_group ist nicht wirklich wichtig! Wichtig ist nur, dass die aufeinanderfolgenden Tage den gleichen date_group Wert haben. Dies ist ein kleiner Trick, um die Länge einer Serie zu berechnen. Er funktioniert, weil die Differenz zwischen der Anzahl der Tage in order_date und dem Wert in row_number für ein und denselben Kunden immer gleich ist, wenn die Bestellungen jeden Tag erfolgen.

Beachten Sie, dass diese Daten nirgendwo erscheinen. Ich zeige sie Ihnen nur, um Ihnen zu verdeutlichen, was die CTE hier tut.

Es ist wichtig zu erwähnen, dass dieser Trick nur funktioniert, wenn Ihre Daten alle im selben Monat liegen. Wenn die Reihe z. B. am 2021-10-31 beginnt und bis zum 2021-11-01 geht, funktioniert der Trick nicht; diese beiden Tage, obwohl sie hintereinander liegen, gehören nicht zum selben date_group. Sie müssen also Ihre Daten verstehen, bevor Sie diesen Trick anwenden, um die Länge einer Reihe zu bestimmen.

Sobald Sie das CTE haben, verwenden Sie es wie eine Tabelle in der Anweisung SELECT. Ich werde die Funktion COUNT() verwenden, um die Anzahl der Bestellungen in einer Zeile zu ermitteln. Da ich das Ergebnis für jeden Kunden sehen möchte, gruppiere ich die Daten nach id, first_name und last_name. Außerdem gruppiere ich sie nach der Spalte date_group, so dass die Bestellungen desselben Kunden getrennt werden, wenn eine Lücke zwischen ihnen besteht.

Die Ergebnistabelle

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

Das Ergebnis Erläuterung

Diese Tabelle zeigt, dass Simon Paulson vier Bestellungen in Folge aufgegeben hat. Reb Mackennack hat dies fünf Tage in Folge getan, während Dylan Bobson nur zwei Bestellungen in Folge aufgegeben hat. Schließlich gibt es noch eine zusätzliche Bestellung von Simon Paulson.

Wann sind CTEs nützlich?

Wie Sie in diesen Beispielen sehen, sind CTEs sehr nützlich, wenn Sie etwas in mindestens zwei Schritten berechnen müssen. Sie verwenden ein CTE, um die erste Berechnung vorzubereiten, und dann verwenden Sie einfach die Anweisung SELECT, verweisen auf das CTE und führen eine weitere Berechnungsstufe durch.

Bei komplexeren Berechnungen können Sie auch mehrere CTEs oder sogar verschachtelte CTEs verwenden. Das ist ähnlich wie bei Unterabfragen, aber CTEs machen den Code lesbarer und erleichtern die Aufgliederung der Berechnung in Schritte. Und mit verschachtelten CTEs können Sie eine Abfrage schreiben und sofort auf sie verweisen wie auf jede andere Tabelle.

Einige dieser Beispiele zeigen, dass Sie auch Aggregat- und Fensterfunktionen verwenden können. Dies macht CTEs noch leistungsfähiger.

Darüber hinaus eignen sich CTEs hervorragend für hierarchische Daten wie Organisationsstrukturen und zum Durchlaufen eines Graphen. Allerdings müssen Sie in diesen Fällen ein rekursives CTE schreiben, wie ich es in Frage 3 getan habe.

Wenn Sie rekursive Abfragen schreiben und verwenden möchten, müssen Sie zunächst CTEs lernen. Wenn Sie Hilfe brauchen, finden Sie hier einen Leitfaden zum Erlernen von CTEs.

Wie Sie sehen können, haben CTEs viele Vorteile und praktische Anwendungen. Diese Beispiele zeigen Ihnen nur einen kleinen Einblick in Lösungen für praktische Probleme, die ohne CTEs viel länger dauern würden. Sie wollen noch mehr? Kein Problem, schauen Sie sich einige andere praktische Anwendungen von CTEs an.

Wenn Sie CTEs gelernt haben und anfangen, sie zu verwenden, versuchen Sie, zuerst über das Problem nachzudenken. Brechen Sie die Berechnungsschritte auf und übersetzen Sie diese Logik dann in einen SQL-Code für einen gemeinsamen Tabellenausdruck. CTEs sind dafür geeignet.

Da es sich bei CTEs um eine Version von temporären Tabellen handelt, sollten Sie sie klar benennen: Verwenden Sie einen Namen, aus dem sofort hervorgeht, was die CTE tut, und halten Sie sich an die Namenskonventionen. Generell sollten Sie sich an die bewährten CTE-Verfahren halten, die Ihnen (und anderen!) das Lesen des Codes erheblich erleichtern.

Möchten Sie mehr über SQL Common Table Expressions wissen?

Egal, ob Sie sich auf ein SQL-Vorstellungsgespräch vorbereiten, in Ihrem derzeitigen Job besser werden wollen oder einfach in Ihrer Freizeit etwas Neues lernen möchten, werfen Sie einen Blick auf unseren Rekursive Abfragen Kurs an.

Dort finden Sie einen systematischen Ansatz für CTEs, der die Theorie dahinter erklärt, Ihnen die Syntax zeigt und Ihnen weitere Beispiele zum Üben gibt. Der gesamte Kurs ist Teil des Fortgeschrittenes SQL Track, der zwei weitere Themen abdeckt: Fensterfunktionen (die wir in diesem Artikel verwendet haben) und die GROUP BY Erweiterungen. Viel Erfolg!