SQL-Projekt für Portfolio: Northwind Store

SQL-Portfolio-Projekte sind ein wichtiger Bestandteil der Ausbildung von Datenanalysten. Wie fängt man ein Projekt an, und was macht man mit den gewonnenen Erkenntnissen? Wir verwenden die Northwind-Datenbank, um diese Frage zu beantworten.

Die Durchführung eines soliden SQL-Projekts ist ein wesentliches Element im Lernprozess eines Datenanalysten. Ein SQL-Projekt ist ein großartiges Lernwerkzeug, da es Sie dazu bringt, SQL auf einen realen Datensatz anzuwenden. Dies ist besonders wichtig, wenn Sie ein Anfänger sind und nicht die Möglichkeit haben, mit realen Datensätzen zu arbeiten. Durch die Arbeit mit mehreren realen Datensätzen lernen Sie, auf welche Probleme Sie in der realen Welt stoßen können. Als Bonus ist die Durchführung mehrerer SQL-Projekte für Ihr Portfolio immer eine schöne Sache, die in einem Lebenslauf zu sehen ist.

Die Frage ist, wie Sie ein SQL-Projektportfolio erstellen. Wenn Sie einen kostenlosen Online-Datensatz gefunden haben, den Sie analysieren möchten, was tun Sie dann? Wir werden diese Fragen anhand der Northwind-Store-Datenbank beantworten.

Sie finden diesen Datensatz im Kurs SQL-Datenbanken für die Praxis. Er ist einer von sechs Datensätzen, die Daten von einer Universität, Blogverkehr, Sportergebnissen, einem Musikgeschäft und dem Museum of Modern Art (MoMA) enthalten. Dieser Kurs ist Teil des SQL-Praxis -Tracks, in dem Sie Aggregation, JOIN, Subqueries, CTEs, CASE WHEN und andere wichtige SQL-Themen üben können. Wenn Sie Ihre Kenntnisse in einigen dieser für die Berichterstattung wichtigen Bereiche auffrischen möchten, sollten Sie unseren Erstellen einfacher SQL-Berichte Kurs.

Nehmen wir nun den Northwind-Store-Datensatz und verwenden ihn für ein SQL-Projekt für ein Datenanalysten-Portfolio. Ich werde dieses Projekt in PostgreSQL durchführen, aber alles, was ich tue, lässt sich mit geringfügigen Änderungen der Syntax auf andere Datenbanken übertragen.

Sie können dabei auch unser kostenloses Spickzettel für SQL zur Datenanalyse zu Rate ziehen.

Der Northwind Store-Datensatz

Die einzige Information, die ich Ihnen über diese Datenbank geben werde, ist, dass sie aus sechs Tabellen besteht:

1. categories - Eine Liste der Produktkategorien.
2. channels - Eine Liste der Quellen, über die das Geschäft Kunden akquiriert.
3. customers - Eine Liste der Kunden des Ladens.
4. order_items - Eine Liste der Produkte, die in jeder Bestellung enthalten sind.
5. orders - Eine Liste der von Kunden aufgegebenen Bestellungen.
6. products - Eine Liste der Produkte, die das Geschäft anbietet.

Den Rest der Informationen werden wir im Rahmen unseres Projekts sammeln. Diese Datenbankinspektion sollte eigentlich am Anfang eines jeden Projekts stehen, bevor man mit der Analyse beginnt.

1. Inspektion der Datenbank

In dieser Phase der Vorbereitung eines SQL-Projekts geht es darum, Ihre Daten kennenzulernen, z. B. Tabellen- und Spaltennamen, Primär- und Fremdschlüssel, die Beziehungen zwischen den Tabellen und die Datentypen in jeder Tabelle.

Inspektion der Tabellennamen

Eine primitive Methode, um etwas über die Tabellen im Dataset zu erfahren, besteht darin, sie im Browser des RDBMS zu suchen, in das Sie das Dataset importiert haben, z. B. PostgreSQL, SQL Server oder MySQL.

In PostgreSQL können Sie diese Abfrage schreiben, um eine Liste aller Tabellen in einer Datenbank zu erhalten:

SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Sie sucht nach Daten in der information_schemawo Sie die Metadaten der Datenbank finden können. Da ich den Namen jeder Tabelle haben möchte, verwende ich die Ansicht tables nachdem ich das Schema referenziert habe, und füge table_name in SELECT ein.

Die erste Bedingung in WHERE filtert Systemschemata heraus und lässt nur benutzerdefinierte Tabellen übrig. Die zweite Bedingung stellt sicher, dass nur Basistabellen aufgelistet werden, ohne Views und andere Tabellen.

Hier ist die Liste der Tabellen in der Northwind-Datenbank:

table_name
categories
channels
customers
order_items
orders
products

Untersuchung der Spalteninformationen

Nun wollen wir die Details der einzelnen Tabellen besser verstehen. Die Kenntnis ihrer Spalten ist ein guter Anfang.

Wir können wieder abfragen information_schema um wichtige Informationen über die Spalten zu erhalten:

SELECT table_name,
       column_name,
       data_type,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_name, ordinal_position;

Wir können Tabellennamen, Spaltennamen und den Datentyp jeder Spalte finden, indem wir table_name, column_name und data_type in SELECT auflisten.

Die Spalte is_nullable liefert uns Informationen darüber, ob die Spalte NULL Werte akzeptiert.

table_name	column_name	data_type	is_nullable
categories	category_id	integer	NO
categories	category_name	character varying	NO
categories	description	text	YES
channels	id	integer	NO
channels	channel_name	character varying	NO
customers	customer_id	integer	NO
customers	email	character varying	NO
customers	full_name	character varying	NO
customers	address	character varying	YES
customers	city	character varying	YES
customers	region	character varying	YES
customers	postal_code	character varying	YES
customers	country	character varying	YES
customers	phone	character varying	YES
customers	registration_date	timestamp without time zone	NO
customers	channel_id	integer	NO
customers	first_order_id	integer	YES
customers	first_order_date	timestamp without time zone	YES
customers	last_order_id	integer	YES
customers	last_order_date	timestamp without time zone	YES
order_items	order_id	integer	NO
order_items	product_id	integer	NO
order_items	unit_price	numeric	NO
order_items	quantity	smallint	NO
order_items	discount	numeric	NO
orders	order_id	integer	NO
orders	customer_id	integer	NO
orders	order_date	timestamp without time zone	YES
orders	total_amount	numeric	NO
orders	ship_name	character varying	YES
orders	ship_address	character varying	YES
orders	ship_city	character varying	YES
orders	ship_region	character varying	YES
orders	ship_postalcode	character varying	YES
orders	ship_country	character varying	YES
orders	shipped_date	timestamp without time zone	YES
products	product_id	integer	NO
products	product_name	character varying	NO
products	category_id	integer	NO
products	unit_price	numeric	YES
products	discontinued	boolean	NO

Auf diese Weise haben wir alle Informationen an einem Ort, was die Untersuchung erleichtert.

Zunächst können wir anhand der Spaltennamen erkennen, welche Daten jede Tabelle enthält. Die Datentypen erscheinen alle logisch. Es gibt zum Beispiel keine IDs, die als character varying definiert sind. Es besteht also keine Notwendigkeit, die Daten in diesem Stadium in ein geeignetes Format zu konvertieren.

Unter NULLerhalten wir einige wertvolle Informationen über die Tabellen:

• Die Tabelle categories erlaubt es, dass die Kategoriebeschreibung NULL lautet.
• Die Tabelle channels lässt überhaupt keine NULLs zu.
• Die Tabelle customers erlaubt NULLs in vielen Spalten, einschließlich Land. Wenn wir eine geografische Segmentierung der Kunden erstellen wollen, könnten diese NULLs uns Probleme bereiten.
• Die obige Ausgabe zeigt, dass keine Spalten in der Tabelle order_items löschbar sind.
• Für die products ist es interessant zu sehen, dass der Stückpreis NULL sein kann.
• Es scheint ein wenig seltsam, dass die Tabelle orders so viele löschbare Spalten hat. Im Grunde genommen ist es damit möglich, eine Auftrags-ID und keine anderen Informationen über den Auftrag zu haben. Das müssen wir bei der Analyse dieser Tabelle im Auge behalten.

Auffinden von Primär- und Fremdschlüsseln

Der nächste Schritt der Datenprüfung besteht darin zu verstehen, wie die Tabellen zusammenarbeiten. Dazu müssen wir zunächst ihre Primärschlüssel (PKs) und Fremdschlüssel (FKs) finden.

Die PKs zeigen uns, welche Spalte(n) eine Tabelle zur eindeutigen Identifizierung von Daten verwendet. Auf diese Weise können Sie die Struktur der Tabelle besser verstehen.

FKs zeigen uns, welche Spalte mit einem Primärschlüssel einer anderen Tabelle verknüpft ist. Dies ist die Grundlage für das Verständnis der Beziehungen zwischen den Tabellen.

Sie können sowohl PKs als auch FKs mit der folgenden Abfrage auflisten:

SELECT kcu.table_name,
       kcu.column_name,
       tc.constraint_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
ON kcu.constraint_name = tc.constraint_name
AND kcu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY kcu.table_name ASC, tc.constraint_type DESC;

Dieses Mal müssen wir zwei Ansichten aus der Tabelle information_schemaverwenden, nämlich table_constraints und key_column_usage.

Die Ansicht table_constraints Ansicht enthält Informationen über die Beschränkungen für jede Tabelle. Die Ansicht key_column_usage dient dazu, Informationen über die Spalten mit den Schlüsseleinschränkungen zu erhalten.

Diese beiden Ansichten sind durch zwei gemeinsame Spalten verbunden: constraint_name (der Name der Einschränkung) und constraint_schema (der Name des Schemas, das die Einschränkung enthält).

Ich verwende die WHERE Klausel, um nur die Primärschlüssel- und Fremdschlüssel-Beschränkungen auszugeben.

In einem letzten Schritt ordne ich die Ausgabe alphabetisch nach dem Tabellennamen und dann umgekehrt alphabetisch nach dem Einschränkungstyp.

Der Code gibt diese Tabelle aus:

table_name	column_name	constraint_type
categories	category_id	PRIMARY KEY
channels	id	PRIMARY KEY
customers	customer_id	PRIMARY KEY
customers	last_order_id	FOREIGN KEY
customers	first_order_id	FOREIGN KEY
customers	channel_id	FOREIGN KEY
order_items	order_id	PRIMARY KEY
order_items	product_id	PRIMARY KEY
order_items	order_id	FOREIGN KEY
order_items	product_id	FOREIGN KEY
orders	order_id	PRIMARY KEY
orders	customer_id	FOREIGN KEY
products	product_id	PRIMARY KEY
products	category_id	FOREIGN KEY

Die Tabellen categories und channels sind die einzigen Tabellen, die PK, aber keine FK haben.

Alle anderen Tabellen haben eine PK und mindestens eine FK. Die einzige Ausnahme ist die Tabelle order_itemsdie die PK-Beschränkung auf zwei Spalten hat: order_id und product_id. Mit anderen Worten, die Tabelle hat einen zusammengesetzten Primärschlüssel, was bedeutet, dass der eindeutige Bezeichner der Daten die eindeutige Kombination von zwei Spalten ist. In diesem Fall geht es darum, nur eine Produkt-ID für eine einzelne Bestellung anzuzeigen.

Bei näherer Betrachtung zeigt sich, dass dieselben Spalten auch ein Fremdschlüssel für dieselbe Tabelle sind. Dies bedeutet, dass die Primärschlüssel ebenfalls Fremdschlüssel sind, da sie aus anderen Tabellen stammen.

Verstehen der Beziehungen zwischen den Tabellen

Die Auflistung der PKs und FKs gibt uns bereits einen Eindruck von den Beziehungen zwischen den Tabellen. Wir können sie jedoch noch besser verstehen, wenn wir die über- und untergeordneten Tabellen und Spalten der Fremdschlüssel anzeigen.

Warum sollten wir das tun? Ein Fremdschlüssel ist immer der Primärschlüssel einer anderen Tabelle. Die Auflistung aller über- und untergeordneten Tabellen und Spalten ist eine Verbesserung gegenüber der vorherigen Abfrage, da die gemeinsamen Spalten zwischen den Tabellen leicht zu erkennen sind.

Diese Informationen sind nützlich, um die Beziehungen zwischen den Tabellen zu verstehen, um zu wissen, welche Spalten Sie verwenden können, um Tabellen zu verbinden, und ob sie direkt oder über eine andere Tabelle verbunden werden können.

Hier ist der Code:

SELECT ccu.table_name AS parent_table,
       ccu.column_name AS parent_column,
       kcu.table_name AS child_table,
       kcu.column_name AS child_column      
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_name;

In der Abfrage verwenden wir Informationen aus der Tabelle table_constraints, key_column_usage, und constraint_column_usage Ansichten.

Die Ansicht constraint_column_usage Ansicht zeigt die Spalten, die von einer Einschränkung verwendet werden. Wir verwenden sie, um die übergeordnete Tabelle und Spalte der FK anzuzeigen, d.h. die Tabelle und Spalte, in der diese FK tatsächlich eine PK ist.

Die Daten von key_column_usage zeigen die Tabelle und die Spalte eines FKs.

parent_table	parent_column	child_table	child_column
orders	order_id	customers	last_order_id
orders	order_id	customers	first_order_id
channels	id	customers	channel_id
orders	order_id	order_items	order_id
products	product_id	order_items	product_id
customers	customer_id	orders	customer_id
categories	category_id	products	category_id

Aus der obigen Ausgabe geht hervor, dass die Tabelle orders direkt mit der Tabelle verbunden ist customers über die Spalten order_id (PK) und customer_id (FK) verbunden ist. Sie ist mit der Tabelle verbunden order_items über order_id (PK).

Die Tabelle channels ist direkt nur mit der Tabelle customers.

Wir haben bereits festgestellt, dass die Primärschlüssel der Tabelle order_items auch Fremdschlüssel sind. Aus der obigen Tabelle können wir sehen, dass sie aus den Tabellen orders und . products.

Die Tabelle categories ist nur mit der Tabelle products.

2. Erkundung der Daten

Wenn Sie mit einer neuen Datenbank zu arbeiten beginnen, sollte der erste Schritt darin bestehen, Ihre Daten zu untersuchen, um einige grundlegende Informationen über jede Tabelle in einer Datenbank zu erhalten. Es ist eine gute Praxis, sich die folgenden Punkte anzusehen:

• Zählen von Zeilen.
• Minimal- und Maximalwerte ermitteln (besonders bei Datumsangaben).
• Auflistung verschiedener Kategorien.
• Zählen der unterschiedlichen Werte in jeder Kategorie.
• Zählen von Zeilen pro Kategoriewert.
• Summen und Durchschnittswerte für wichtige numerische Werte.

Diese Liste ist nicht erschöpfend und kann sich mit der zu untersuchenden Tabelle ändern. Sie ist jedoch ein Minimum und sollte für die meisten Tabellen funktionieren.

Im Allgemeinen läuft dieser Schritt auf eine grundlegende Datenaggregation und Gruppierung hinaus. Ich zeige Ihnen, wie dies bei einer Tabelle aus unserer Datenbank funktioniert, und Sie können dann andere Tabellen auf die gleiche Weise untersuchen.

Untersuchen der Tabelle Bestellungen

Anzahl der Zeilen

Wie erwartet, verwenden wir hier die Aggregatfunktion COUNT():

SELECT COUNT(*) AS number_of_rows
FROM orders;

Ich verwende die Funktion COUNT() mit einem Sternchen; damit werden alle Zeilen gezählt, einschließlich der NULL-Zeilen.

number_of_rows
11,618

Die Tabelle orders hat 11.618 Zeilen. Mit anderen Worten, es gibt 11.618 Bestellungen.

Mindest- und Höchstwerte

Schauen wir uns an, welche Spalten sich für die Verwendung der Funktionen MIN() und MAX() eignen. Perfekt - wir können das älteste und das jüngste Datum finden, an dem eine Bestellung aufgegeben und versandt wurde:

SELECT MIN(order_date) AS oldest_order_date, 
	 MAX(order_date) AS latest_order_date,
	 MIN(shipped_date) AS oldest_shipped_date,
	 MAX(shipped_date) AS latest_shipped_date
FROM orders;

Aus der Ausgabe geht hervor, dass die erste Bestellung am 2017-01-01 und die letzte am 2024-06-24 aufgegeben wurde. Was die Versanddaten betrifft, so ist das erste Datum auf 2017-01-01 und das jüngste auf 2024-06-23.

oldest_order_date	latest_order_date	oldest_shipped_date	latest_shipped_date
2017-01-01 0:00:00	2024-06-24 0:00:00	2017-01-06 0:00:00	2024-06-23 0:00:00

Auflistung von Kategorien

Hierfür brauchen wir keine Aggregatfunktionen:

SELECT DISTINCT ship_country
FROM orders
ORDER BY ship_country;

In der Abfrage verwenden wir die DISTINCT Klausel, um jedes Land nur einmal anzuzeigen.

Hier ist eine Liste der Länder in dieser Tabelle:

ship_country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
Italy
Mexico
Norway
Poland
Portugal
Spain
Sweden
Switzerland
UK
USA
Venezuela

Es ist auch nützlich, eine Liste der Städte zu sehen:

SELECT DISTINCT ship_city
FROM orders
ORDER BY ship_city;

Da es viele Städte gibt, ist hier eine Teilliste:

ship_city
Aachen
Abilene
Achille
Adelphi
Adrian
Akron
Albany
Alberton
Albuquerque
…
Yucca Valley

Zählen der unterschiedlichen Werte in jeder Kategorie

Sehen wir uns nun die Anzahl der Länder an, in die Northwind Sendungen geliefert hat:

SELECT COUNT(DISTINCT ship_country) AS number_of_countries
FROM orders;

Wir verwenden wieder COUNT(), aber diesmal fügen wir DISTINCT hinzu und verweisen auf die Spalte, die wir zählen wollen.

Die Ausgabe zeigt, dass es 21 verschiedene Länder gibt, in die das Unternehmen liefert:

number_of_countries
21

Wir könnten dasselbe für Städte tun:

SELECT COUNT(DISTINCT ship_city) AS number_of_cities
FROM orders;

Es gibt 494 eindeutige Städte:

number_of_cities
494

Auch wenn wir sie nicht als eigene Kategorie aufgeführt haben, ist es nützlich zu wissen, wie viele Kunden Bestellungen aufgegeben haben:

SELECT COUNT(DISTINCT customer_id) AS number_of_customers_with_orders
FROM orders;

Wir verwenden COUNT(), um die Kunden-IDs zu zählen. Natürlich benötigen wir DISTINCT, da zu erwarten ist, dass einige Kunden mehrmals auftauchen, d. h. jedes Mal, wenn sie eine Bestellung aufgeben.

Es gibt 761 eindeutige Kunden, die mindestens einmal bei diesem Unternehmen bestellt haben.

number_of_customers_with_orders
761

Zählen der Zeilen pro Kategoriewert

Hierfür wird jeder Kategoriewert aufgelistet (das haben wir bereits getan) und die Anzahl der Zeilen für jeden Wert gezählt.

So erhalten wir zum Beispiel die Anzahl der Bestellungen pro Land:

SELECT ship_country,
	 COUNT(*) AS number_of_orders
FROM orders
GROUP BY ship_country
ORDER BY number_of_orders DESC;

Ich wähle die Spalte ship_country aus und verwende dann COUNT(*), um alle Zeilen zu zählen. Damit die Zählung für jedes Land erfolgt, muss ich die Klausel GROUP BY ship_country einfügen.

Auf diese Weise erhalten wir die Anzahl der Bestellungen nach Land. Die Ausgabe zeigt, dass die USA mit 9.789 Bestellungen der größte Markt sind:

ship_country	number_of_orders
USA	9,789
France	236
Brazil	233
Germany	230
UK	144
Spain	123
Mexico	118
Venezuela	103
Argentina	87
Canada	82
Italy	64
Austria	58
Portugal	58
Belgium	56
Denmark	56
Finland	47
Norway	30
Sweden	28
Poland	27
Ireland	26
Switzerland	23

Summe und Durchschnittswerte

Wenn Ihre Daten geeignet sind, sollten Sie Durchschnittswerte und Summen von numerischen Werten ermitteln. Das können wir für die Spalte total_amount tun, die den Wert einer Bestellung darstellt:

SELECT SUM(total_amount) AS orders_total_value,
       AVG(total_amount) AS average_order_value
FROM orders;

Um diese beiden Berechnungen darzustellen, verwenden Sie die Aggregatfunktionen SUM() und AVG().

Wir sehen, dass der Gesamtumsatz des Ladens etwas über 19 Millionen beträgt. Der durchschnittliche Bestellwert beträgt 1.636,15:

orders_total_value	average_order_value
19,008,819.69	1,636.15

Zusätzliche Datenexploration

Wie ich bereits erwähnt habe, sollten die vorherigen Aggregationen das Minimum sein, das Sie für jede Tabelle in der Datenbank durchführen.

Diese Datenexploration geht ein wenig weiter (aber nicht zu weit) als einfache Aggregationen und GROUP BY. Dies ist zwar immer noch die Grundlage, aber Sie können auch andere Konzepte anwenden, wie z. B. das Filtern von Daten (unter Verwendung von WHERE und/oder HAVING), das Extrahieren von Datumsteilen oder der Uhrzeit, die Verwendung von CASE WHEN zur Kennzeichnung von Daten und so weiter.

Schauen wir uns nun einige Beispiele an.

Anzahl der Produkte

Wir können die Funktion COUNT() verwenden, um die Gesamtzahl der Produkte zu ermitteln, die das Geschäft verkauft.

SELECT COUNT(*) AS number_of_active_products
FROM products
WHERE discontinued IS FALSE; 

Die WHERE -Klausel enthält eine Bedingung, damit nur Produkte angezeigt werden, die nicht auslaufen, d. h., es handelt sich um die Produkte, die das Geschäft derzeit verkauft.

Aus dem vorherigen Abschnitt wissen wir, dass die Spalte discontinued ein boolescher Typ ist. Daher müssen wir den Operator IS FALSE verwenden, um nur nicht eingestellte Produkte in die Zählung aufzunehmen.

Die Anzahl ist 69:

number_of_active_products
69

Umsatz nach Jahr

Ein einfacher Bericht, der den Umsatz nach Jahr zeigt, kann mit SUM() erstellt werden:

SELECT EXTRACT(YEAR FROM order_date) AS revenue_year,
       SUM(total_amount) AS revenue  
FROM orders
GROUP BY revenue_year;

Wir verwenden die Funktion EXTRACT(), um nur die Jahre ab den Bestelldaten zu erhalten. Dann summieren wir die Gesamtbeträge aller Bestellungen und gruppieren sie nach Jahr, um die Werte für jedes Jahr separat anzuzeigen.

Aus den Ergebnissen ist ersichtlich, dass das Jahr 2018 das umsatzstärkste Jahr des Unternehmens war. Das Jahr mit den niedrigsten Einnahmen ist 2024, aber das könnte daran liegen, dass das Jahr noch nicht zu Ende ist (zum Zeitpunkt der Analyse).

Interessant ist auch, dass es keine Umsatzdaten für die Jahre 2019-2022 gibt. Dies sollte überprüft werden, um festzustellen, ob die Daten aus einem bestimmten Grund fehlen oder ob es sich um einen Fehler handelt.

revenue_year	revenue
2017	3,088,759.84
2018	9,368,330.91
2023	4,646,048.11
2024	1,905,680.83

Kundensegmentierung nach Land

Wir haben Daten über die Länder der Kunden, daher wäre eine Übersicht über die Anzahl der Kunden in jedem Land informativ.

Außerdem sind wir nicht an Kunden interessiert, die keine Bestellung aufgegeben haben, da dies die Zahl der Kunden künstlich aufblähen kann. Nein, wir wollen nur Kunden, die bei uns bestellen. Hier ist die Abfrage:

SELECT country,
	 COUNT(*) AS number_of_customers
FROM customers
WHERE first_order_id IS NOT NULL
GROUP BY country
ORDER BY number_of_customers DESC;

Wir wählen und gruppieren nach Land und verwenden die Funktion COUNT() aggregate, um die Anzahl der Kunden zu ermitteln. Wenn die Daten in der Spalte first_order nicht null sind, dann hat dieser Kunde mindestens eine Bestellung aufgegeben; dies ist die Bedingung, die wir in WHERE verwenden müssen.

Die Daten sind von der höchsten bis zur niedrigsten Anzahl von Kunden geordnet.

Die Ausgabe zeigt, dass der größte Markt für Northwind, gemessen an der Kundenzahl, die USA sind. Daraus kann man auch schließen, dass dies der größte Markt nach Umsatz ist.

country	number_of_customers
USA	697
Germany	8
France	8
Brazil	8
UK	5
Venezuela	4
Spain	4
Mexico	4
Argentina	3
Canada	3
Belgium	2
Denmark	2
Portugal	2
Finland	2
Italy	2
Austria	2
Sweden	1
Poland	1
Ireland	1
Switzerland	1
Norway	1

3. Fortgeschrittene Datenanalyse und umsetzbare Einblicke

Was wir bis jetzt getan haben, ist ein guter Anfang. Die Datenanalyse in einem SQL-Projekt für ein Portfolio sollte sich jedoch nicht auf eine einfache Aggregation und Untersuchung für jede Tabelle beschränken.

Wir werden jetzt darüber hinausgehen und komplexere Abfragen schreiben, die es uns ermöglichen, Maßnahmen zu ergreifen und das Geschäft von Northwind zu verbessern. Wir möchten zum Beispiel sehen, wie sich die Umsätze je nach Vertriebskanal verändern. Sehen wir uns an, was wir erhalten, und entscheiden wir dann, was unsere nächsten Schritte sein werden.

Umsatz nach Kanal

Für jeden Kanal möchten wir den Gesamtumsatz, den durchschnittlichen Auftragswert, die Gesamtzahl der Aufträge und die Anzahl der einzelnen Kunden anzeigen. Außerdem möchten wir die Kanäle nach dem Gesamtumsatz ordnen. Hier ist die Abfrage:

SELECT ch.channel_name,
       SUM(o.total_amount) AS total_sales,
       AVG(o.total_amount) AS average_order_value,
       COUNT(o.order_id) AS total_orders,
       COUNT(DISTINCT o.customer_id) AS unique_customers,
       RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS sales_rank
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name
ORDER BY sales_rank;

Diese Abfrage verwendet mehrere Aggregationen: SUM() zur Berechnung des Gesamtumsatzes, AVG() für den durchschnittlichen Bestellwert und COUNT() für die Gesamtzahl der Bestellungen und (mit DISTINCT) für die eindeutigen Kunden.

Anschließend wird die Fensterfunktion RANK() verwendet, um die Kanäle nach dem Gesamtumsatz zu ordnen. Da Fensterfunktionen vor der Aggregation ausgeführt werden, können wir nicht einfach die Spalte total_sales in der Fensterfunktion verwenden. Stattdessen muss ich die gesamte Berechnung replizieren - eine einfache Aufgabe, die durch Kopieren und Einfügen erledigt wird.

Wir verwenden Daten aus den Tabellen channels und orders. Ich kann diese beiden Tabellen jedoch nicht direkt verbinden, da sie keine gemeinsame Spalte haben. Stattdessen müssen wir sie über die Tabelle customers.

Hier ist die Ausgabe:

channel_name	total_sales	average_order_value	total_orders	unique_customers	sales_rank
Organic Search	14,003,046.95	1,603.10	8,735	565	1
Direct	2,436,649.06	1,799.59	1,354	91	2
Referral	1,034,734.45	1,645.05	629	46	3
Social	837,378.77	1,824.35	459	32	4
Paid Search	483,824.24	1,645.66	294	19	5
Email	213,186.22	1,450.25	147	8	6

Wir sehen, dass der Großteil der Verkäufe von Northwind über die organische Suche erfolgt. Es gibt keine Widersprüche in den Daten - der Rang nach Gesamtumsatz spiegelt auch die Anzahl der Bestellungen und der einzelnen Kunden wider.

Interessant ist, dass der Kanal "Organische Suche" nicht den höchsten durchschnittlichen Bestellwert aufweist. Hier besteht Verbesserungsbedarf!

Stellen wir uns eine Strategie zur Umsatzsteigerung vor, die sich auf die Erhöhung des durchschnittlichen Bestellwerts aller Kanäle und nicht auf Neuakquisitionen konzentriert.

Wahrscheinlich wird es nicht möglich sein, einen einheitlichen Ansatz zu wählen, der nur auf dem Marketingkanal basiert. Innerhalb jedes Segments gibt es möglicherweise Kunden mit gegensätzlichen Kaufgewohnheiten. Wir können davon ausgehen, dass dies insbesondere für die organische Suche gilt, die ein so großer Kanal ist.

Daher müssen wir mehr über die Verteilung des Bestellwerts für jeden Kanal erfahren.

Verteilung des Bestellwerts

Berechnen wir nun den durchschnittlichen Bestellwert, den Median, das obere Quartil, das untere Quartil und die Standardabweichung für jeden Kanal:

SELECT ch.channel_name,
	 AVG(total_amount) AS average_order_value,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order_value,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS upper_quartile_order_value,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS lower_quartile_order_value,
       STDDEV(total_amount) AS order_value_stddev
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name;

Der durchschnittliche Bestellwert wird von AVG() berechnet.

Der Median (oder das 50. Perzentil), das obere Quartil und das untere Quartil werden mit der Funktion PERCENTILE_CONT() ordered-set aggregate berechnet, wobei das Perzentil in Klammern angegeben wird.

Für die Berechnung des Perzentils müssen die Daten in aufsteigender Reihenfolge sortiert werden; dies geschieht mit der Klausel WITHIN GROUP.

Nach der Perzentilberechnung wird STDDEV() zur Berechnung der Standardabweichung verwendet.

Hier ist die Ausgabe:

channel_name	average_order_value	median_order_value	upper_quartile_order_value	lower_quartile_order_value	order_value_stddev
Direct	1,799.59	1,005.90	2,166.80	517.40	2,323.63
Email	1,450.25	960.00	1,970.41	492.50	1,655.97
Organic Search	1,603.10	1,007.70	2,018.20	480.00	1,916.39
Paid Search	1,645.66	1,083.00	2,104.40	486.55	1,813.22
Referral	1,645.05	1,064.00	2,034.50	482.90	1,969.01
Social	1,824.35	1,122.80	2,123.20	559.90	2,319.10

Analysieren wir die Metriken für den ersten Kanal.

Die Standardabweichung des Kanals Direct beträgt 2.323,63 und ist damit höher als der Mittelwert oder Durchschnitt. Dies deutet auf eine hohe Variabilität hin, d. h. es gibt wahrscheinlich Ausreißer oder eine große Bandbreite an Auftragswerten.

Die unteren und oberen Quartile zeigen, dass 50 % der Aufträge zwischen 517,40 und 2.166,80 liegen. Eine hohe Standardabweichung bedeutet jedoch, dass viele Aufträge außerhalb dieses Bereichs liegen.

Der Median ist deutlich niedriger als der Mittelwert, was darauf hindeutet, dass die Verteilung rechtsschief ist, d. h. eine geringe Anzahl von hohen Auftragswerten, die den Mittelwert erhöhen.

Wir können jeden Kanal auf die gleiche Weise analysieren.

Das bringt uns auf eine weitere Idee. Um maßgeschneiderte Strategien zur Umsatzsteigerung zu entwickeln, können wir die Kunden weiter nach dem oberen und unteren Quartil segmentieren. Alle Kunden, die über dem oberen Quartil liegen, werden als "High Spenders" betrachtet, während die Kunden unterhalb des unteren Quartils als "Low Spenders" gelten.

Auf diese Weise können wir unsere Strategie an jeden Kanal und an die Ausgabengewohnheiten jeder Gruppe anpassen.

Kunden mit hohem und niedrigem Wert nach Kanal

Ich werde diese Berechnung nur für einen Marketingkanal durchführen. Sie können sie auch für alle anderen durchführen, da der Code derselbe bleibt; Sie ändern nur den Kanal in WHERE.

Die Kunden im oberen Quartil sind hochwertige Kunden, die in einer separaten Liste aufgeführt werden sollen:

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
           ch.channel_name
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN channels ch ON c.channel_id = ch.id
    WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

upper_quartile_value AS (
    SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_order_value) AS upper_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot
JOIN upper_quartile_value uqv 
ON cot.total_order_value >= uqv.upper_quartile
ORDER BY cot.total_order_value DESC;

Die erste CTE berechnet den Gesamtauftragswert jedes Kunden, indem sie SUM() verwendet und die Tabellen customers und Bestellungen. In WHERE filtern wir alle Kanäle außer Direkt heraus.

Die zweite CTE berechnet das obere Quartil auf die bekannte Weise.

CROSS JOINs In der dritten SELECT werden dann die beiden CTEs miteinander verknüpft, um alle Kunden zu finden, deren Bestellwert über dem oberen Quartil liegt.

Hier sind die ersten zehn Zeilen der Ausgabe:

customer_id	full_name	total_order_value	channel_name
134	Barry Michael	79,371.50	Direct
152	Carolann Williams	64,365.21	Direct
7	Frédérique Citeaux	61,865.74	Direct
17	Sven Ottlieb	57,251.14	Direct
64	Sergio Gutiérrez	55,140.75	Direct
490	Alice Blevins	54,736.24	Direct
8	Martín Sommer	54,499.55	Direct
303	Gregory Mack	52,554.20	Direct
316	Jeff Heard	51,976.31	Direct
129	Stephan Bufford	50,868.70	Direct

Insgesamt gibt es 23 Kunden in der Ausgabe. Wir können diese Informationen nutzen, um diese hochwertigen Kunden gezielt anzusprechen. So können wir beispielsweise Treueprogramme erstellen, bei denen diese Kunden besondere Rabatte, persönlichen Service, einlösbare Punkte für Einkäufe, VIP-Mitgliedschaftsprogramme usw. erhalten.

Auf ähnliche Weise können wir Kunden auflisten, deren Bestellungen unter dem unteren Quartil liegen:

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
	       ch.channel_name
    FROM customers c
    JOIN orders o 
    ON c.customer_id = o.customer_id
    JOIN channels ch
    ON c.channel_id = ch.id
	WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

lower_quartile_value AS (
    SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_order_value) AS lower_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot, lower_quartile_value lqv
WHERE cot.total_order_value <= lqv.lower_quartile
ORDER BY cot.total_order_value ASC;

Diese Abfrage ist fast die gleiche wie die vorherige, nur dass wir diesmal das untere Quartil berechnen. Außerdem suchen wir nach Kunden, deren Bestellungen unter diesem Wert liegen.

Auch hier liefert die Abfrage 191 Kunden. Hier sind die ersten zehn Zeilen:

customer_id	full_name	total_order_value	channel_name
939	Shannon Aguilar	98.40	Direct
997	Barbra Armstrong	251.50	Direct
687	Andrew Scott	452.90	Direct
787	Dennis Myer	912.00	Direct
917	Les Allen	991.40	Direct
921	Shelby Turgeon	1,162.25	Direct
560	Nancy Wiggins	1,425.80	Direct
678	Tracey Thomas	2,555.20	Direct
756	Dora Rowlands	2,713.50	Direct
715	George Scott	2,906.50	Direct

Diese Kunden müssen anders angesprochen werden, da sie zu höheren Ausgaben motiviert werden müssen. Zu den Strategien zur Steigerung des Umsatzes mit diesen Kunden können personalisierte Produktpakete, kostenloser Versand ab einem bestimmten Betrag, höhere Rabatte für höhere Bestellschwellen oder das Angebot eines kostenlosen Geschenks ab einem bestimmten Bestellwert gehören.

Führen Sie nun die gleiche Analyse für alle anderen Kanäle durch und überlegen Sie, welche Taktiken Sie anwenden könnten, um die Bestellwerte aus diesem Kanal zu erhöhen.

Sind Sie bereit für Ihr eigenes SQL-Portfolio-Projekt?

In diesem Artikel haben wir gezeigt, wie Sie ein SQL-Portfolio-Projekt mit der Northwind-Store-Datenbank vorbereiten können. Wenn Sie kreativ genug sind, können Sie natürlich auch Ihren eigenen Datensatz erstellen.

Die Erstellung eines SQL-Projekts für Ihr Portfolio ist ein wichtiger Teil der Vorbereitung auf den Einstellungsprozess. Portfolios sind eine hervorragende Möglichkeit, Ihre praktischen Fähigkeiten in der Datenanalyse mit SQL zu präsentieren. Das obige Projekt ist nur ein Beispiel. In unserem Kurs SQL-Datenbanken für die Praxis, den wir Ihnen wärmstens empfehlen, können Sie viele weitere Analyseideen ausprobieren!