SQL-Projekt für Einsteiger: AdventureWorks Vertriebs-Dashboard

Die Erstellung eines eigenen SQL-Projekts ist eine gute Möglichkeit, Ihre Fähigkeiten zu verbessern und praktische Erfahrungen zu sammeln. Dieses SQL-Projektbeispiel für Anfänger zeigt Ihnen, wie Sie mit SQL und der AdventureWorks-Beispieldatenbank ein Vertriebs-Dashboard erstellen.

Wenn Sie ein SQL-Anfänger sind, der seine Fähigkeiten über einfache Abfrageübungen hinaus erweitern möchte, ist es eine gute Idee, ein SQL-Datenanalyseprojekt in Angriff zu nehmen. Es wird Sie nicht nur herausfordern, das Gelernte anzuwenden, sondern Ihnen auch ein tieferes Verständnis dafür vermitteln, wie SQL in realen Szenarien funktioniert. Dies ist Ihre Chance, von den grundlegenden Übungen zu etwas Greifbarem zu gelangen, das Ihre wachsenden Kenntnisse unter Beweis stellt!

In diesem Artikel führen wir Sie durch die Schritte zur Erstellung eines SQL-Projekts mit der AdventureWorks-Datenbank. Wir behandeln alles von der Projektidee bis zum Schreiben der endgültigen Abfragen. Sie werden lernen, wie Sie jeden Schritt des Prozesses angehen, und die Konzepte, die wir verwenden werden, können auf jedes SQL-Projekt übertragen werden.

Wenn Sie fertig sind, werfen Sie einen Blick auf unseren Kurs SQL-Datenbanken für die Praxis, der 6 verschiedene Datenbanken enthält, mit denen Sie die Erstellung von Berichten üben können. Alternativ können Sie auch nachlesen, wie Sie kostenlose Datensätze für Ihr eigenes SQL-Projekt finden und mit Ihrer eigenen Datenbank weiterarbeiten!

Werfen wir nun einen Blick auf die Datenbank, die wir in diesem SQL-Beispielprojekt verwenden werden.

Erkundung der AdventureWorks-Datenbank

Die AdventureWorks-Datenbank ist eine Beispieldatenbank, die von Microsoft für SQL Server entwickelt wurde. Sie wurde auf viele Datenbanken portiert - auch auf PostgreSQL, das wir in diesem Artikel verwenden werden. Die Datenbank umfasst 68 Tabellen, die einen fiktiven Fahrradhersteller beschreiben und Daten über verschiedene Arten von Transaktionen enthalten, die während des Geschäftsbetriebs auftreten. Da AdventureWorks so groß ist, ist es ein perfekter Datensatz für Anfänger, die SQL in einer realen Umgebung üben möchten.

Die Datenbank ist in 5 Schemata unterteilt, die jeweils einen anderen Bereich von Vorgängen repräsentieren: Production, Purchasing, Sales, HR, und Person. In diesem Projekt werden die meisten unserer Daten aus dem Sales Schema stammen. Wir werden auch andere Schemata verwenden, um zusätzliche Informationen zu erhalten.

Schauen wir uns die Tabellen an, die wir am häufigsten verwenden werden:

• SalesOrderHeader: Dies ist die größte Tabelle in der Datenbank. Sie speichert alle Informationen, die sich auf einen Auftrag als Ganzes beziehen. Sie wird der häufigste Ausgangspunkt für verkaufsbezogene Abfragen sein.
• Product: Hier werden umfangreiche Informationen über die vom Unternehmen angebotenen Produkte gespeichert.
• SalesOrderDetail: Diese Tabelle verbindet die SalesOrderHeader und Product und speichert Informationen über die einzelnen Produkte, aus denen sich die einzelnen Bestellungen zusammensetzen.
• ProductReview: Hier werden die Bewertungen der Kunden für bestimmte Produkte gespeichert.
• Store: In dieser Tabelle werden grundlegende Informationen über jedes Geschäft gespeichert. Die meisten Daten sind in der Spalte Demographics im XML-Format gespeichert; wir werden sie in diesem Projekt nicht verwenden.
• SalesTerritory und CountryRegion: Wir werden diese beiden Tabellen zusammen verwenden, um den Namen des Landes zu erhalten, mit dem die Bestellung verbunden ist.

Es gibt noch viele andere Tabellen in der Datenbank, aber keine Sorge, wir werden sie und ihre wichtigen Spalten bei Bedarf vorstellen.

Scoping Ihres SQL-Projekts

In diesem Projekt wollen wir ein Vertriebs-Dashboard für AdventureWorks erstellen. Doch wie beginnt man ein solches Projekt? Ein guter Ausgangspunkt ist, den Umfang des Projekts zu bestimmen. Oft bedeutet dies, dass Sie eine Liste von Fragen erstellen, die Sie mit den Daten beantworten möchten. Unsere erste Liste von Fragen lautet:

• Wie hoch ist der monatliche Gesamtumsatz?
• Wie hoch sind die monatlichen Umsätze nach Ländern?
• Welche Produkte sind unsere Verkaufsschlager?
• Welche Geschäfte erzielen die besten Ergebnisse?
• Wie hoch ist der durchschnittliche Umfang jeder Bestellung?
• Wie hoch ist der durchschnittliche Kundenlebenswert in jedem Land?

Während wir in die Daten eintauchen und unsere Abfragen schreiben, können wir diese Fragen verfeinern und unsere Abfragen entsprechend anpassen.

So sollten Sie alle Ihre SQL-Projekte beginnen: Schreiben Sie eine Liste der Fragen, die Sie an die Daten haben, und schreiben Sie dann die Abfragen, die Ihnen die Antworten liefern werden. Während Sie sich durch die Abfragen und Daten arbeiten, werden neue Fragen auftauchen. Versuchen Sie, auch diese mit Ihren Abfragen zu beantworten.

Jetzt können wir mit dem Schreiben der Abfragen für unser Vertriebs-Dashboard beginnen! Halten Sie unser SQL für Anfänger Cheat Sheet bereit, falls Sie eine kurze Auffrischung der Syntax benötigen.

Was werden wir in unser SQL-Projekt-Dashboard einbauen?

Bericht 1: Monatliche Verkäufe

Frage: Wie hoch ist der Umsatz des Unternehmens für jeden Monat?

Der erste Bericht in unserem Dashboard zeigt die Einnahmen für alle vergangenen Monate (für jedes Jahr). Diese Abfrage kann verwendet werden, um Trendlinien der Einnahmen darzustellen, oder sie kann als solche analysiert werden. Wir möchten die Daten in chronologischer Reihenfolge anzeigen, beginnend mit den jüngsten Daten.

Herangehen

Zunächst wählen wir die Tabelle, auf der unsere Abfrage basieren soll. Wir möchten drei Spalten anzeigen: das Jahr und den Monat des Verkaufs und den Gesamtumsatz für diesen Monat und dieses Jahr. Wir benötigen den Gesamtbetrag der Bestellung und das Verkaufsdatum, also die SalesOrderHeader Tabelle aus dem Sales Schema eine naheliegende Wahl.

Um das Jahr und den Monat anzuzeigen, verwenden wir die Funktion EXTRACT:

EXTRACT(<MONTH / YEAR> FROM OrderDate)

Wir werden diese Spalten verwenden, wenn wir das Ergebnis gruppieren und ordnen.

Um die Gesamteinnahmen für diesen bestimmten Monat zu erhalten, können wir SUM(TotalDue) zusammen mit der Gruppierung nach den beiden vorherigen Spalten verwenden. Auf diese Weise erhalten wir separate SUM() Ergebnisse für jedes einzelne Jahr-Monats-Paar.

Hier ist die endgültige Abfrage:

SELECT
  EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  ROUND(SUM(TotalDue), 2) AS TotalRevenue
FROM Sales.SalesOrderHeader
GROUP BY 
  EXTRACT(YEAR FROM OrderDate),
  EXTRACT(MONTH FROM OrderDate)
ORDER BY 
  OrderYear DESC,
  OrderMonth DESC;

Bericht 2: Monatliche Umsätze nach Land

Frage: Wie hoch sind die monatlichen Einnahmen für jedes Land?

Wir möchten die Gesamteinnahmen für jeden Monat (in jedem Jahr) für jedes Land in der Datenbank sehen. Das Ergebnis sollte in chronologischer Reihenfolge sortiert werden, wobei die jüngeren Daten an erster Stelle stehen. Wir können diese Daten verwenden, um Trendlinien für jedes Land zu zeichnen oder die Daten so zu analysieren, wie sie sind.

Ansatz

Diese Abfrage ähnelt der vorherigen Abfrage - wir müssen nur für jeden Kauf Informationen über das Land hinzufügen. Wie können wir das tun?

Die Tabelle SalesOrderHeader Tabelle hat eine Spalte mit dem Namen TerritoryId, die sich auf die SalesTerritory Tabelle verweist. Über diese Tabelle haben wir Zugriff auf die Spalte CountryRegionCode.

Die Ländernamen werden in der Tabelle CountryRegion des Person Schemas gespeichert. Wir können diese Tabelle mit der Tabelle SalesTerritory Tabelle verbinden, indem wir den Regionalcode verwenden. Damit haben wir eine klare Möglichkeit, unsere Abfrage zu erweitern.

Wenn wir die Tabellen wie folgt verknüpfen: SalesOrderHeader -> SalesTerritory -> CountryRegion, erhalten wir Zugriff auf die Spalte CountryRegion.Name. Wir können sie als erste Spalte in der Anweisung SELECT und am Ende von GROUP BY und ORDER BY einfügen.

Werfen Sie einen Blick auf die endgültige Abfrage:

SELECT
  cr.Name AS Country,
  EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  ROUND(SUM(TotalDue), 2) AS TotalRevenue
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesTerritory st
  ON soh.TerritoryId = st.TerritoryId
JOIN Person.CountryRegion cr
  ON cr.CountryRegionCode = st.CountryRegionCode
GROUP BY 
  cr.Name,
  EXTRACT(YEAR FROM OrderDate),
  EXTRACT(MONTH FROM OrderDate)
ORDER BY
  OrderYear DESC,
  OrderMonth DESC,
  Country;

Hier ist ein Teil der Ausgabe:

Bericht 3: Meistverkaufte Produkte

Frage: Was sind unsere meistverkauften Produkte?

Wir möchten eine Liste der meistverkauften Produkte zusammen mit einigen relevanten Informationen wie Lebenszeitverkäufe und Kundenrezensionen hinzufügen. Der Bericht sollte einigermaßen aktuelle Daten enthalten.

Vorgehensweise

Die Tabelle SalesOrderDetail Tabelle, die die Menge jedes verkauften Artikels enthält, wird unser Ausgangspunkt für diese Abfrage sein. Wir werden sie schrittweise aufbauen.

Die wichtigste Kennzahl in dieser Abfrage ist die verkaufte Gesamtmenge für jedes Produkt, die wir durch die Kombination von SUM(OrderQty) und GROUP BY ProductId erhalten können.

Das Ergebnis der Abfrage wäre jedoch ziemlich schwer zu lesen, da die Produkte nur durch ihre Id identifizierbar sind. Die Namen der Produkte sind in der Product Tabelle des Production Schemas gespeichert. Wenn man die beiden Tabellen mit ProductId verbindet und ProductName hinzufügt, erhält man diese Abfrage:

SELECT
  p.ProductId,
  p.Name AS ProductName,
  SUM(od.OrderQty) AS TotalUnitsSold
FROM Sales.SalesOrderDetail od
JOIN Production.Product p
  ON od.ProductID = p.ProductID
GROUP BY
  p.Name,
  p.ProductId
ORDER BY TotalUnitsSold DESC
LIMIT 10;

Diese Abfrage ist ein brauchbares Dashboard-Element, aber sie kann noch verbessert werden.

Wir haben bereits die Grundlage für die Arbeit mit Produkten gelegt. Jetzt wollen wir die Bewertungen zu unseren Produkten hinzufügen. Dazu erstellen wir eine separate Abfrage, die wir später mit der Hauptabfrage verknüpfen werden.

Produktbewertungen werden in der ProductReview Tabelle des Production Schemas gespeichert. Wir interessieren uns vor allem für die ProductId und die Rating, die der Kunde hinterlassen hat, also bilden wir den Durchschnitt der Bewertungspunkte und gruppieren die Daten nach ProductId.

Jetzt haben wir eine saubere Eins-zu-Eins-Zuordnung zwischen dem Produkt und seinen Bewertungen. Wir runden das Ergebnis auf eine Dezimalstelle, wie es bei Bewertungen üblich ist:

SELECT
  ProductId,
  ROUND(AVG(Rating), 1) AS ProductRating
FROM Production.ProductReview
GROUP BY ProductId;

Wir können diese Abfrage nun in einen gemeinsamen Tabellenausdruck (Common Table Expression, CTE) verpacken und zusammen mit unserer Hauptabfrage verwenden. CTEs sind eine Möglichkeit, eine temporäre Ergebnismenge zu erstellen - wie eine virtuelle Tabelle, die nur im Kontext der Abfrage existiert. Wenn Sie eine vollständige Erklärung der Funktionsweise von CTEs wünschen, lesen Sie unseren umfassenden Leitfaden zu CTEs in SQL.

Nun gibt es ein mögliches Problem: Möglicherweise gibt es nicht für alle Produkte eine Bewertung. Wenn wir also eine Verknüpfung Product aus der Hauptabfrage mit der CTE verknüpfen, sollten Sie eine LEFT JOIN verwenden, um keine Daten zu verlieren.

Es könnte von Vorteil sein, nur die neuesten Daten zu betrachten. Wir können alte Verkäufe in der Hauptabfrage herausfiltern, indem wir die Spalte OrderDate verwenden und mit INTERVAL ‘2 MONTHS’ zwei Monate vom aktuellen Datum subtrahieren. Dann können wir die Daten so filtern, dass nur die Bestellungen angezeigt werden, die nach diesem Datum aufgegeben wurden.

Wenn Sie sich entscheiden, den Datumsbereich zu ändern, ist es ganz einfach, den Teil INTERVAL zu modifizieren. Um mehr über die INTERVAL Syntax zu erfahren, schauen Sie sich unseren Standard-SQL-Funktionen Kurs an.

Dies ist die vollständige Abfrage:

WITH ProductRating AS (
  SELECT
    ProductId,
    ROUND(AVG(Rating), 1) AS ProductRating
  FROM Production.ProductReview
  GROUP BY ProductId
)
SELECT
  p.ProductId,
  p.Name AS ProductName,
  SUM(od.OrderQty) AS TotalUnitsSold,
  pr.ProductRating AS ProductRating
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
  ON od.SalesOrderId = oh.SalesOrderId
JOIN Production.Product p
  ON od.ProductID = p.ProductID
LEFT JOIN ProductRating pr
  ON pr.ProductId = p.ProductId
WHERE OrderDate > CURRENT_DATE - INTERVAL '2 MONTH'
GROUP BY
  p.Name,
  p.ProductId,
  pr.ProductRating
ORDER BY TotalUnitsSold DESC
LIMIT 10;

Dies ist eine Liste, die es wert ist, auf dem Dashboard eines Vertreters angezeigt zu werden! Ein Verständnis der Verkaufstrends ist etwas, zu dem jeder Mitarbeiter Zugang haben sollte - und jetzt haben wir eine Abfrage, die dies liefert.

Unser Dashboard wächst; lassen Sie uns weitere Kennzahlen hinzufügen!

Bericht 4: Die leistungsstärksten Filialen

Frage: Welches sind die 10 umsatzstärksten Geschäfte der letzten 2 Monate?

Wir möchten die zehn umsatzstärksten Geschäfte - einschließlich der Online-Geschäfte - und ihren jeweiligen Umsatzzuwachs in den letzten zwei Monaten anzeigen. Ein "Top-Shop" ist definiert als der Shop mit dem höchsten Umsatz in den letzten 2 Monaten. Behandeln Sie alle Online-Shops als eine Einheit.

Herangehensweise

Wie üblich beginnen wir mit der SalesOrderHeader Tabelle. Um herauszufinden, welcher Shop für welchen Verkauf verantwortlich ist, schauen wir uns die Store Tabelle im Sales Schema.

Jede Filiale kann über die Spalte SalesPersonId mit einem Verkauf verknüpft werden. Bei allen Verkäufen, bei denen SalesPersonId gleich NULL ist, ist auch OnlineOrderFlag auf true gesetzt. Dies erleichtert die Gruppierung und Anzeige von Online-Verkäufen, da wir jeden Verkauf, bei dem SalesPersonId gleich NULL ist, als Online-Verkauf behandeln können.

Um keine Daten zu verlieren, wenn Sie die Tabellen auf SalesPersonId (was manchmal NULL ist) verbinden, verwenden Sie LEFT JOIN zwischen SalesOrderHeader und Store. In der Anweisung SELECT interessieren uns zwei Dinge: der Name des Geschäfts und der Gesamtumsatz, der in diesem Geschäft erzielt wurde. Vergessen Sie nicht, die Spalte Store.Name mit COALESCE(..., ‘Online’) zu umschließen, um sicherzustellen, dass alle Online-Bestellungen eine gut lesbare Bezeichnung haben.

Um alte Daten herauszufiltern, verwenden Sie die gleiche WHERE-Bedingung wie im letzten Bericht:

WHERE OrderDate > CURRENT_DATE - INTERVAL ‘2 MONTHS’.

Ordnen Sie die Ergebnisse nach dem Gesamtumsatz (größere Werte werden zuerst angezeigt) und verwenden Sie LIMIT 10, um nur die 10 umsatzstärksten Geschäfte im ausgewählten Zeitraum anzuzeigen.

Hier ist die Abfrage:

SELECT
  COALESCE(s.Name, 'Online') AS StoreName,
  ROUND(SUM(so.TotalDue), 2) AS TotalSalesAmount
FROM Sales.SalesOrderHeader so
LEFT JOIN Sales.Store s
  ON so.SalesPersonId = s.SalesPersonId
WHERE OrderDate > CURRENT_DATE - INTERVAL '2 MONTHS'
GROUP BY s.Name
ORDER BY TotalSalesAmount DESC
LIMIT 10;

Bericht 5: Einnahmequellen

Frage: Wie ist der Online-Umsatz im Vergleich zum Offline-Umsatz?

Für diese Dashboard-Kennzahl möchten wir Daten darüber einbeziehen, wie viele Bestellungen online und wie viele in einer Filiale aufgegeben wurden. Die Endergebnisse sollten nach Monat und Jahr aufgeschlüsselt werden.

Ansatz

Auch hier werden wir diese Abfrage iterativ aufbauen. Um die Verkäufe in Online- und Offline-Gruppen aufzuteilen, können wir die OnlineOrderFlag aus der Sales.SalesOrderHeader Tabelle. Diese Tabelle enthält praktischerweise auch den Gesamtpreis der Bestellung, so dass diese Metrik aus einer einzigen Tabelle berechnet werden kann.

Allerdings ist OnlineOrderFlag vom Datentyp BOOLEAN; wenn wir es in der Klausel GROUP BY verwenden, bleiben die Gruppen true und false übrig. Um die Daten besser lesbar zu machen, können wir einen CASE WHEN Ausdruck verwenden, um alle wahren Werte durch "Online" und alle falschen Werte durch "Store" zu ersetzen. Und da OnlineOrderFlag bereits zu BOOLEAN ausgewertet wird, müssen wir ihn nicht ändern, um ihn als Bedingung zu verwenden. Eine ausführlichere Erklärung, wie wir CASE WHEN in diesem Beispiel verwenden, finden Sie in unserer Anleitung zur Verwendung von CASE WHEN mit GROUP BY.

Jetzt, da wir die richtigen Gruppennamen haben, können wir die anderen Metriken auswählen, die die Umsatzquellen vergleichen: die Gesamtzahl der abgeschlossenen Geschäfte und den Gesamtumsatz aus diesen Geschäften. Die erste Kennzahl lässt sich durch einfaches Zählen aller SalesOrderIds und die zweite durch Aufsummieren aller TotalDue-Werte ermitteln. Die Gruppierung kann mit dem bloßen OnlineOrderFlag und nicht mit dem CASE WHEN Ausdruck vorgenommen werden; in dieser Verwendung werden einfach Werte zugeordnet.

So sieht die aktuelle Abfrage aus:

SELECT
  CASE WHEN OnlineOrderFlag THEN 'Online' ELSE 'Store' END AS OrderOrigin,
  COUNT(SalesOrderId) AS TotalSales,
  SUM(TotalDue) AS TotalRevenue
FROM Sales.SalesOrderHeader
GROUP BY OnlineOrderFlag
ORDER BY TotalRevenue DESC;

Wir können eindeutig eine Schlussfolgerung erkennen: Physische Läden machen fast 10-mal weniger Umsatz, erwirtschaften aber 3-mal so viel wie der Online-Laden.

Wir können diese Daten aussagekräftiger machen, indem wir sie nach Jahr und Monat filtern, damit wir Trends in den Umsatzströmen erkennen können. Die Tabelle Sales.SalesOrderHeader Tabelle enthält die Spalte OrderDate, die wir verwenden können, um nur die Teile YEAR und MONTH zu erhalten, wie wir es im ersten Bericht getan haben. Wenn wir diese als zusätzliche Spalten hinzufügen und sie in GROUP BY verwenden, sehen wir, dass die beiden Gruppen “Store” und “Online” in verschiedene Jahre und Monate aufgeteilt wurden. Wir können zusätzlich die Daten filtern, wie wir es zuvor mit CURRENT_DATE und INTERVAL getan haben.

Hier ist die vollständige Abfrage und ein Beispiel für die Ergebnisse:

SELECT
  CASE WHEN OnlineOrderFlag THEN 'Online' ELSE 'Store' END AS OrderOrigin,
  EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  COUNT(SalesOrderId) AS TotalSales,
  SUM(TotalDue) AS totalRevenue
FROM Sales.SalesOrderHeader
WHERE OrderDate > CURRENT_DATE - INTERVAL '2 MONTHS'
GROUP BY
  EXTRACT(YEAR FROM OrderDate),
  EXTRACT(MONTH FROM OrderDate),
  OnlineOrderFlag
ORDER BY
  OrderOrigin,
  OrderYear DESC,
  OrderMonth DESC;

Bericht 6: Durchschnittlicher Auftragsumfang pro Land

Frage: Was ist die durchschnittliche Bestellgröße?

Wir möchten für jedes Land die durchschnittliche Bestellgröße für alle Bestellungen aus diesem Land anzeigen. Die Länder mit der größten durchschnittlichen Bestellgröße sollen zuerst angezeigt werden.

Ansatz

In einem der vorherigen Berichte haben wir bereits den Ländernamen für jeden Verkauf ermittelt, so dass wir uns hier auf die Bestellgrößen konzentrieren können. Es ist wichtig zu beachten, dass wir in dieser Abfrage zwei Mal aggregieren müssen: Zuerst, um die Bestellmenge für jede Bestellung zu ermitteln, und dann, um den Länderdurchschnitt zu ermitteln. Wir beginnen mit der Ermittlung der Auftragsgrößen.

Jede Bestellung kann durch einen Eintrag in der SalesOrderHeader Tabelle identifiziert werden. Sie hat mehrere entsprechende Einträge in der Tabelle SalesOrderDetail Tabelle, die den einzelnen Produkten in der Bestellung entsprechen. Durch Summieren der OrderQtys aus der SalesOrderDetail Tabelle für jede SalesOrderId ergibt ordentliche Paare aus Bestellnummer und Bestellgröße.

Wir verwenden ein paar JOINs, um die Ländernamen zu erhalten, und bilden eine CTE wie diese:

WITH OrderSizes AS (
  SELECT
    sod.SalesOrderId,
    SUM(OrderQty) AS ProductCount,
    cr.Name AS Country
  FROM Sales.SalesOrderHeader soh
  JOIN Sales.SalesOrderDetail sod
    ON sod.SalesOrderId = soh.SalesOrderId
  JOIN Sales.SalesTerritory st
    ON soh.TerritoryId = st.TerritoryId
  JOIN Person.CountryRegion cr
    ON cr.CountryRegionCode = st.CountryRegionCode
  GROUP BY
    sod.SalesOrderId,
    cr.Name
)
SELECT * 
FROM OrderSizes;

Jetzt müssen wir nur noch die durchschnittliche Bestellgröße aus der CTE ermitteln und das Ergebnis nach Ländern gruppieren. Vergessen Sie nicht, die Daten so zu ordnen, dass die größten durchschnittlichen Bestellmengen zuerst angezeigt werden.

Schauen Sie sich die vollständige Abfrage und ihr Teilergebnis an:

WITH OrderSizes AS (
  SELECT
    sod.SalesOrderId,
    SUM(OrderQty) AS ProductCount,
    cr.Name AS Country
  FROM Sales.SalesOrderHeader soh
  JOIN Sales.SalesOrderDetail sod
    ON sod.SalesOrderId = soh.SalesOrderId
  JOIN Sales.SalesTerritory st
    ON soh.TerritoryId = st.TerritoryId
  JOIN Person.CountryRegion cr
    ON cr.CountryRegionCode = st.CountryRegionCode
  GROUP BY
    sod.SalesOrderId,
    cr.Name
)
SELECT
  Country,
  ROUND(AVG(ProductCount), 2) AS AverageOrderSize
FROM OrderSizes
GROUP BY Country
ORDER BY AverageOrderSize DESC;

Bericht 6: Durchschnittlicher Kundenwert über die gesamte Lebensdauer pro Region

Frage: Wie hoch ist der durchschnittliche Customer Lifetime Value in jeder Region?

Der Customer Lifetime Value (CLV) ist eine wichtige Kennzahl, die angibt, wie viel das Unternehmen von einem neu gewonnenen Kunden während seiner gesamten Beziehung zur Marke erwarten kann. Der CLV wird berechnet, indem die Einnahmen aus allen Käufen des Kunden addiert werden. Diese Kennzahl ist besonders nützlich bei der Berechnung des Budgets für Marketingkampagnen; sie wird am besten gruppiert nach der Region, in der das Unternehmen tätig ist, dargestellt.

Herangehensweise

Lassen Sie uns zunächst die Abfrage erstellen, die den durchschnittlichen CLV für jeden einzelnen Kunden berechnet. Wir wissen bereits, wie der CLV berechnet wird: durch Summierung aller TotalDue Werte für jeden Kunden aus der SalesOrderHeader Tabelle. So erhalten wir ein Zwischenergebnis. Da wir es später nach Ländern gruppieren wollen, ist es von Vorteil, auch einige dieser Daten hier einzubeziehen.

Die Tabelle Customer Tabelle im gleichen Schema hat eine TerritoryId Spalte, die wir später verwenden können, um weitere Informationen zu erhalten. Für den Moment fügen wir der CLV-Abfrage einfach TerritoryId als zusätzliche Spalte hinzu. Dies erfordert eine einfache Verknüpfung und eine Erweiterung der Anweisung GROUP BY.

Hier sehen Sie, wie die Abfrage bisher aussieht, zusammen mit einigen Beispielergebnissen:

SELECT
  cs.CustomerId,
  cs.TerritoryId,
  SUM(TotalDue) AS LifetimeRevenues
FROM sales.Customer cs
JOIN sales.SalesOrderheader ord
  ON cstm.CustomerId = ord.CustomerId
GROUP BY
  cs.CustomerId,
  cs.TerritoryId;

Die Hälfte des Problems ist gelöst. Was bleibt, ist, dies auf Länderebene zu gruppieren und das Ergebnis besser lesbar zu machen. Lassen Sie uns eine CTE verwenden, um die Ergebnisse dieser Abfrage zu speichern.

Wir können die CTE nach TerritoryId gruppieren und den durchschnittlichen Lebenszeitwert der Kunden pro Gebiet anzeigen. Dies ergibt ein technisch korrektes Ergebnis, obwohl die Gebiete nicht identifizierbar sind. Die tatsächlichen Namen der Länder sind in der CountryRegion Tabelle aus dem Person Schema gespeichert. Sie ist durch den Schlüssel CountryRegionCode identifizierbar. Im Sales Schema finden wir diesen Schlüssel in der SalesTerritory Tabelle. Wir verbinden also das Ergebnis der CTE über die Tabelle SalesTerritory Tabelle, mit der CountryRegion Tabelle. Jetzt können wir den Namen des Landes anstelle des numerischen Id anzeigen. Vergessen Sie nicht, die GROUP BY entsprechend zu bearbeiten.

Schauen Sie sich die endgültige Abfrage und einige ihrer Ergebnisse an:

WITH CustomerLifetimeRevenue AS (
  SELECT
    cstm.CustomerId,
    ord.TerritoryId,
    SUM(TotalDue) AS LifetimeRevenue
  FROM Sales.Customer cstm
  JOIN Sales.SalesOrderHeader ord
    ON cstm.CustomerId = ord.CustomerId
  GROUP BY
    cstm.CustomerId,
    ord.TerritoryId
)
SELECT
  cr.Name AS Country,
  ROUND(AVG(clr.LifetimeRevenue),2) AS AvgLifetimeCustomerValue
FROM CustomerLifetimeRevenue clr
JOIN Sales.SalesTerritory tr
  ON clr.TerritoryId = tr.TerritoryId
JOIN Person.CountryRegion cr
  ON cr.CountryRegionCode = tr.CountryRegionCode
GROUP BY cr.Name
ORDER BY
  AvgLifetimeCustomerValue DESC,
  cr.Name;

Erstellen Sie Ihr eigenes SQL-Projekt für Anfänger!

Wir haben erfolgreich Abfragen erstellt, mit denen ein informatives Vertriebs-Dashboard erstellt werden kann. Dieser Artikel soll Sie jedoch dazu inspirieren, Ihr eigenes SQL-Projekt zu starten. Lassen Sie uns noch einmal zusammenfassen, wie man ein SQL-Projekt erstellt:

• Finden Sie einen interessanten Datensatz.
• Überlegen Sie sich ein Geschäftsproblem für diesen Datensatz, wie wir es bei diesem Vertriebs-Dashboard getan haben.
• Schreiben Sie Fragen zu diesem Problem, die das Projekt beantworten soll.
• Erstellen Sie SQL-Abfragen für die Fragen, die Sie geschrieben haben.

Wenn Sie etwas mehr Struktur brauchen, empfehlen wir Ihnen unseren Minikurs SQL-Datenbanken für die Praxis. Er enthält 6 verschiedene Datenbanken, die Sie in Ihrem eigenen SQL-Projekt verwenden können, sowie einige Fragen und Vorschläge, die Ihnen den Einstieg erleichtern.

Wenn Sie weitere Beispiele für SQL-Einsteigerprojekte sehen möchten, lesen Sie unseren Artikel über die Erstellung eines Northwind Store-Projekts für Ihr Portfolio.

Sind Sie hungrig nach mehr allgemeiner Praxis? Schauen Sie sich unseren umfangreichen SQL-Praxis Track an. Viel Spaß beim Lernen!