SQL-Projekt: Persönliches Reddit-Recap

Kommt Ihnen Ihr Reddit Recap zu kurz und nichtssagend vor? Mit der Kraft von SQL können Sie viel tiefere Einblicke aus Ihren Nutzungsdaten gewinnen! Im Folgenden erfahren Sie, wie Sie dieses interessante SQL-Projekt erstellen können.

Die Erstellung eines persönlichen SQL-Projekts ist eine großartige Möglichkeit, Ihre Fähigkeiten in der Datenbankerstellung und -abfrage zu üben, und kann auch eine schöne Ergänzung für Ihr Portfolio sein.

In diesem Artikel werden wir uns mit den Möglichkeiten befassen, das meiste aus Ihren Reddit-Daten herauszuholen und Erkenntnisse über Ihre Reddit-Aktivitäten zu gewinnen. In diesem SQL-Projekt werden wir uns stark auf fortgeschrittene SQL-Datenanalyse-Themen stützen. Wenn Sie eine Auffrischung der Kenntnisse über die Verwendung von SQL für ein Projekt wie dieses benötigen, sehen Sie sich unseren Lernkurs SQL für Datenanalyse an.

Möchten Sie eine ähnliche Wiederholung für andere Plattformen erstellen? Sehen Sie sich unsere Artikel über die Erstellung Ihrer eigenen Netflix Wrapped und Spotify Wrapped mit SQL an.

Sind Sie bereit? Werfen wir einen Blick darauf, wie Sie Ihre Reddit-Daten erfassen und mit dem Projekt beginnen können.

Einrichten Ihres persönlichen Reddit-SQL-Projekts

Schritt 1: Beschaffung der Daten

Bei Reddit können Sie eine Kopie Ihrer Daten anfordern, einschließlich einiger interessanter Nutzungsstatistiken. Sie können dies auf der Seite Datenanforderung tun. Sie müssen eingeloggt sein, um die Seite zu sehen.

Die Bearbeitung Ihrer Datenanforderung wird einige Zeit in Anspruch nehmen; sobald sie abgeschlossen ist, erhalten Sie eine Nachricht mit dem Download-Link in Ihrem Reddit-Posteingang.

Laden Sie das Archiv herunter und entpacken Sie es. Sie werden 35 CSV-Dateien sehen, die Ihre persönlichen Daten enthalten, einschließlich Ihrer Aktivitätsdaten wie Beiträge, Kommentare, abonnierte Subreddits, Abstimmungen und andere verschiedene Daten.

Die meisten dieser Dateien enthalten keine Informationen, die in einem SQL-Projekt von Nutzen wären, also konzentrieren wir uns auf die, die es tun:

• posts.csv enthält Informationen über Beiträge, die Sie erstellt haben.
• comments.csv enthält Informationen über Ihre Kommentare. Hinweis: Diese Datei ist umfangreicher als comment_headers.csv, die nur die grundlegenden Informationen über den Kommentar enthält.
• subscribed_subreddits.csv enthält die Liste der Subreddits, denen Sie beigetreten sind. Es gibt nur eine Spalte mit Daten, aber sie ist nützlich, wenn Sie nach dem Namen des Subreddits filtern müssen.
• votes.csv enthält Ihre Abstimmungshistorie in Form von Links zu dem Beitrag und die Richtung der Abstimmung (entweder "nach oben" oder "nach unten").

Schritt 2: Daten in eine Datenbank laden

CSV-Dateien enthalten viele Informationen, aber es wäre schwierig, nur mit Tabellenkalkulationen einen Einblick in sie zu gewinnen. Hier ist eine SQL-Datenbank, die diesen Prozess vereinfachen kann:

Dieses Datenbankschema kopiert die Struktur der CSV-Dateien aus dem Archiv. Mit einer Datenbank können wir die Daten viel schneller abrufen und bearbeiten! Lassen Sie uns also eine mit PostgreSQL erstellen.

Erstellen Sie eine Datenbank

Hier ist das Skript zum Erstellen aller oben gezeigten Tabellen:

CREATE TABLE comments (
	id text  NOT NULL,
	permalink text  NOT NULL,
	date timestamptz  NOT NULL,
	ip text  NOT NULL,
	subreddit text  NOT NULL,
	gildings int  NOT NULL,
	link text  NOT NULL,
	parent text  NOT NULL,
	body text  NOT NULL,
	media text  NULL,
	CONSTRAINT comments_pk PRIMARY KEY (id)
);
CREATE TABLE posts (
	id text  NOT NULL,
	permalink text  NOT NULL,
	date timestamptz  NOT NULL,
	ip text  NOT NULL,
	subreddit text  NOT NULL,
	gildings int  NOT NULL,
	title text  NOT NULL,
	url text  NULL,
	body text  NULL,
	CONSTRAINT posts_pk PRIMARY KEY (id)
);
CREATE TABLE subreddits (
	subreddit text  NOT NULL,
	CONSTRAINT subreddits_pk PRIMARY KEY (subreddit)
);
CREATE TABLE votes (
	id text  NOT NULL,
	permalink text  NOT NULL,
	direction text  NOT NULL,
	CONSTRAINT votes_pk PRIMARY KEY (id)
);

Sie können das obige Skript in Ihrer psql-Konsole ausführen; es wird alle notwendigen Tabellen für dieses Projekt erstellen.

psql -U your_username -d your_database -f create-tables.sql

Die Tabellen sind nicht über Fremdschlüssel miteinander verbunden. Es kann einige gemeinsame Spalten zwischen zwei Tabellen geben (z. B. posts und comments, die auf dieselbe Post-URL verweisen), aber sie sind in keiner Weise miteinander verbunden.

Achten Sie auch auf den Datentyp der Datumsspalten. Dieses Schema verwendet timestamptz, was ein PostgreSQL-Alias für timestamp with time zone ist. Reddit speichert Zeitzonen in Zeitstempeln. Um die Daten korrekt zu importieren, müssen Sie also diesen speziellen Datentyp verwenden.

Laden der Daten

Der nächste Schritt ist das Importieren der Daten in die Datenbank aus den .csv Dateien, die Sie von Reddit heruntergeladen haben. Verwenden Sie den folgenden Befehl in Ihrer psql-Konsole:

\copy table_name FROM '\path\to\file.csv' CSV HEADER ENCODING ‘UTF8’

Hier sind alle Tabellennamen und ihre jeweiligen CSV-Dateien:

• posts - posts.csv
• votes - post_votes.csv
• subreddits - subscribed_subreddits.csv
• comments - comments.csv

Vergessen Sie nicht, den absoluten Pfad zur Datei vor dem Dateinamen anzugeben und ihn in einfache Anführungszeichen zu setzen.

Analysieren Sie Ihre Reddit-Aktivitäten mit SQL

Zeit für die Abfrage! Wir werden einige Beispielabfragen durchgehen, um zu zeigen, welches Potenzial die Daten bieten. Beginnen wir mit etwas Einfachem.

Wo poste ich am meisten auf Reddit?

Wissen Sie, wie oft Sie in jedem Subreddit gepostet haben? Hier ist eine einfache Abfrage, mit der Sie das herausfinden können:

SELECT
	subreddit,
	COUNT(*) AS num_posts
FROM posts
GROUP BY subreddit
HAVING COUNT(*) >= 5
ORDER BY num_posts DESC;

Diese Abfrage deckt Ihre Beitragsgewohnheiten auf, indem sie die Subreddits auflistet, in denen Sie mindestens fünf Beiträge geteilt haben. Für mich sind die Ergebnisse ziemlich aufschlussreich:

• Spiele sind mit 17 Beiträgen meine eindeutige Obsession.
• Memes stehen mit 11 Beiträgen an zweiter Stelle (weil Scrollen nicht genug war, musste ich auch noch etwas beitragen).
• An dritter Stelle steht dasLustige mit 9 Beiträgen - anscheinend halte ich mich für sehr witzig.

Gespräche mit mir selbst: Kommentare zu Ihren eigenen Beiträgen

Haben Sie schon einmal ein AMA (ask me anything) gemacht? Es könnte interessant sein, eine vollständige Liste aller Antworten zu erhalten, die Sie gegeben haben. Finden wir alle Kommentare, die Sie unter Ihren eigenen Beiträgen hinterlassen haben!

SELECT
	comments.permalink,
	comments.body,
	media
FROM comments
JOIN posts
	ON comments.link = posts.permalink;

Die Tabelle comments Tabelle enthält zwei Spalten mit Reddit-URLs, link und permalink. Die Spalte permalink ist die link des Kommentars selbst, während link der Link zu dem Beitrag ist, unter dem der Kommentar gepostet wurde (d. h. der übergeordnete Beitrag).

Wir können JOIN verwenden, um jedem Kommentar Informationen über den übergeordneten Beitrag hinzuzufügen, da jeder Kommentar einen Link zum Beitrag hat, der zur Identifizierung des übergeordneten Beitrags verwendet werden kann.

Denken Sie daran, dass die posts Tabelle nur Ihre eigenen Beiträge enthält, so dass Kommentare mit einem übergeordneten Link, der nicht in der posts Tabelle nicht gefunden werden, werden von JOIN verworfen. Dadurch werden die Kommentare, die zu den Beiträgen anderer Personen hinterlassen wurden, herausgefiltert. Mehr über die Funktionsweise von JOINs erfahren Sie in unserem Artikel über das Verbinden zweier Tabellen in SQL.

Werfen Sie einen Blick auf die Ausgabe der Abfrage:

Hier permalink ist der Link zu dem Kommentar-Thread unter dem Beitrag. Sie können den Link von der Ergebnistabelle aus öffnen und selbst sehen, wie er aussieht. Die Spalte "body" ist der Haupttext des Kommentars und "media" ist eine URL zu einem eventuellen Anhang, der dem Kommentar beigefügt ist. Ein Kommentar kann nur aus Text, nur aus Medien oder sowohl aus Text als auch aus Medien bestehen.

Das Ergebnis ist eine vollständige Liste der Kommentare, die Sie unter Ihren eigenen Beiträgen hinterlassen haben.

Abstimmungsaktivität nach Subreddit

Wissen Sie, wie Sie sich in den Subreddits, denen Sie beigetreten sind, engagieren? Diese Abfrage findet die Anzahl der Upvotes und Downvotes, die Sie in jeder Community abgegeben haben:

SELECT
	subreddit,
	COUNT(CASE WHEN direction = 'up' THEN 1 ELSE 0 end) as upvoted,
	COUNT(CASE WHEN direction = 'down' THEN 1 ELSE 0 end) as downvoted
FROM votes
JOIN subreddits
	ON votes.permalink LIKE '%' || subreddits.subreddit || '%'
GROUP BY subreddit
ORDER BY upvoted DESC, downvoted ASC;

Da der Subreddit-Name nicht direkt in der Tabelle votes Tabelle gespeichert ist, können wir eine modifizierte JOIN Bedingung verwenden, um zu prüfen, ob der Permalink der Abstimmung einen bestimmten Subreddit-Namen enthält.

Die ORDER BY Klausel stellt sicher, dass Subreddits mit mehr positivem Engagement zuerst angezeigt werden.

Das Endergebnis zeigt, wo Sie Ihre Stimmen auf Reddit abgegeben haben. Es listet die Subreddits mit der Gesamtzahl der von Ihnen abgegebenen Upvotes und Downvotes auf und zeigt, welche Gemeinschaften Sie am meisten unterstützen - und wo Sie vielleicht eine kleine Kontroverse ausgelöst haben.

Das Endergebnis zeigt, wo ich auf Reddit Stimmen verteilt habe. Funny liegt mit 1.382 Upvotes und kaum Downvotes an der Spitze, während Memes mit 1.225 Upvotes dicht dahinter liegt. Gaming hingegen hat 847 Upvotes und 101 Downvotes erhalten, was bedeutet, dass ich hier vielleicht etwas kritischer gewesen wäre.

Mitglied vs. Nicht-Mitglied: Wo posten Sie tatsächlich?

Versuchen wir nun eine kompliziertere Abfrage. Wie oft posten Sie in Subreddits, denen Sie beigetreten sind, im Vergleich zu Subreddits, denen Sie nicht beigetreten sind? Finden wir es heraus.

WITH joined_status AS (
	SELECT
    	  subredit,
    	  CASE 
          WHEN EXISTS (
             SELECT 1 FROM subreddits s 
 WHERE s.subreddit = p.subreddit)
    	  THEN 'Joined' ELSE 'Not Joined' END AS joined
	FROM posts
)
SELECT
	joined,
	COUNT(*) AS num_posts
FROM joined_status
GROUP BY joined
ORDER BY num_posts DESC;

Diese Abfrage muss in zwei Schritten erstellt werden. Zuerst klassifizieren wir alle Beiträge entweder als "Mitglied" oder "Nicht Mitglied", dann gruppieren wir das Ergebnis, um die Anzahl der Beiträge in jeder Kategorie zu erhalten.

Der erste Schritt erzeugt neue Informationen, die wir später bei der Gruppierung verwenden werden, daher ist es sinnvoll, diesen Teil in eine CTE zu verpacken. Wir verwenden CASE WHEN, um den Wert der neuen Spalte "Joined" zu bestimmen; wenn der Subreddit des Beitrags (p.subreddit) einer ist, den Sie abonniert haben (s.subreddit), wird der Beitrag als "Joined" klassifiziert. Andernfalls wird der Beitrag als "Nicht verbunden" eingestuft.

Der zweite Schritt erfolgt in der äußeren Abfrage. Wir können nach der neuen verbundenen Spalte gruppieren, um die Anzahl der einzelnen Beitragstypen zu ermitteln. Hier ist mein Ergebnis:

Ihr Ergebnis wird wahrscheinlich ähnlich aussehen, da die meisten der von Ihnen erstellten Beiträge in Subreddits sind, denen Sie beigetreten sind. Wenn Sie keine Ergebnistabelle mit Zeilen wie oben sehen (d. h. Sie erhalten nur ein einziges Ergebnis), bedeutet dies, dass Sie nur Beiträge eines Typs erstellt haben.

Wird die Reddit-Analyse Ihr nächstes SQL-Projekt sein?

Sehr gut gemacht! Wir haben ein viel besseres Verständnis der Daten erlangt, als es die grundlegende Reddit-Rekapitulation bietet - und haben dabei SQL geübt! Sie können dieses Projekt erweitern, indem Sie Abfragen über die Anzahl der Beiträge, die Sie in einem bestimmten Zeitraum verfasst haben, über Beiträge von verschiedenen Geräten und über erhaltene Vergoldungen hinzufügen. Sie können auch in andere Dateien eintauchen, die Sie von Reddit heruntergeladen haben, wie friends, chat_history, comment_votes und mehr!

Wenn Sie mehr praktische SQL-Praxis suchen, sehen Sie sich unsere SQL-Praxis Track. Mit über 1.000 interaktiven Übungen können Sie hier Ihre SQL-Kenntnisse vertiefen. Viel Spaß beim Üben!