Projet SQL : Récapitulation personnelle de Reddit

Votre récapitulatif Reddit vous semble-t-il trop court et dénué de sens ? Avec la puissance de SQL, vous pouvez extraire des informations beaucoup plus profondes de vos données d'utilisation ! Découvrez comment réaliser ce projet SQL intéressant ci-dessous.

La réalisation d'un projet SQL personnel est un excellent moyen de mettre en pratique vos compétences en matière de construction de base de données et d'interrogation, et peut également constituer un ajout intéressant à votre portfolio.

Dans cet article, nous allons aborder les moyens d'extraire le maximum de vos données Reddit et de découvrir des informations sur votre activité Reddit. Dans ce projet SQL, nous nous appuierons fortement sur des sujets d'analyse de données SQL avancés. Si vous avez besoin d'un rappel sur la façon d'utiliser SQL pour un projet comme celui-ci, consultez notre piste d'apprentissage SQL pour l'analyse de données.

Vous souhaitez créer un récapitulatif similaire pour d'autres plateformes ? Consultez nos articles sur la création de vos propres récapitulatifs Netflix Wrapped et Spotify Wrapped avec SQL.

Vous êtes prêt ? Voyons comment acquérir vos données Reddit et démarrer le projet.

Mise en place de votre projet SQL Reddit personnel

Étape 1 : Obtenir les données

Reddit vous permet de demander une copie de vos données, y compris des statistiques d'utilisation intéressantes. Vous pouvez le faire sur leur page de demande de données. Vous devez être connecté pour la consulter.

Le traitement de votre demande de données prendra un certain temps ; vous recevrez un message contenant le lien de téléchargement dans votre boîte de réception Reddit une fois le traitement terminé.

Téléchargez et décompressez l'archive. Vous verrez 35 fichiers CSV contenant vos informations personnelles, y compris vos informations d'activité telles que les posts, les commentaires, les subreddits auxquels vous êtes abonné, les votes et d'autres données diverses.

La plupart de ces fichiers ne contiennent pas d'informations utiles pour un projet SQL :

• posts.csv contient des informations sur les articles que vous avez créés.
• comments.csv contient des informations sur vos commentaires. Remarque : ce fichier est plus complet que comment_headers.csv, qui ne contient que les informations de base sur le commentaire.
• subscribed_subreddits.csv contient la liste des subreddits que vous avez rejoints. Il n'y a qu'une seule colonne de données, mais elle est utile lorsque vous devez filtrer par le nom du subreddit.
• votes.csv contient l'historique de vos votes sous forme de liens vers le message et la direction du vote (soit "en hausse", soit "en baisse").

Étape 2 : Chargement des données dans une base de données

Les fichiers CSV contiennent beaucoup d'informations, mais il serait difficile d'en extraire des informations à l'aide de simples feuilles de calcul. Voici une base de données SQL qui peut simplifier le processus :

Ce schéma de base de données copie la structure des fichiers CSV de l'archive. Avec une base de données, nous serons en mesure d'extraire et d'exploiter les données beaucoup plus rapidement ! Construisons-en une avec PostgreSQL.

Créer une base de données

Voici le script permettant de créer toutes les tables présentées ci-dessus :

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)
);

Vous pouvez exécuter le script ci-dessus dans votre console psql; il créera toutes les tables nécessaires à ce projet.

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

Les tables ne sont pas reliées par des clés étrangères. Il peut y avoir des colonnes communes entre deux tables (comme posts et comments qui font référence à la même URL), mais elles ne sont en aucun cas connectées.

Faites également attention au type de données des colonnes de date. Ce schéma utilise timestamptz, qui est un alias PostgreSQL pour timestamp with time zone. Reddit stocke les fuseaux horaires dans des timestamps, donc pour importer correctement les données, vous devrez utiliser ce type de données spécifique.

Charger les données

L'étape suivante consiste à importer les données dans la base de données à partir des fichiers .csv que vous avez téléchargés depuis Reddit. Utilisez la commande suivante dans votre console psql :

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

Voici tous les noms de tables et leurs fichiers CSV respectifs :

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

N'oubliez pas d'inclure le chemin absolu du fichier avant le nom du fichier et de le mettre entre guillemets simples.

Analyse de votre activité Reddit avec SQL

Il est temps de lancer des requêtes ! Nous allons passer en revue quelques exemples de requêtes pour montrer le potentiel des données. Commençons par quelque chose de simple.

Où est-ce que je poste le plus sur Reddit ?

Savez-vous combien de fois vous avez posté dans chaque subreddit ? Voici une requête simple pour vous aider à le savoir :

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

Cette requête révèle vos habitudes de publication en listant les subreddits dans lesquels vous avez partagé au moins cinq messages. En ce qui me concerne, les résultats sont assez éloquents :

• Lejeu est mon obsession, avec 17 posts.
• Les mèmes arrivent en deuxième position avec 11 messages (parce qu'il ne suffisait pas de faire défiler les messages, il fallait que j'y contribue).
• L'humour arrive en troisième position avec 9 messages - apparemment, je me trouve hilarant.

Parler à soi-même : Commentaires sur vos propres articles

Avez-vous déjà participé à un AMA (ask me anything) ? Il pourrait être intéressant d'obtenir une liste complète de toutes les réponses que vous avez données. Cherchons donc tous les commentaires que vous avez laissés sous vos propres articles !

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

Le tableau comments comprend deux colonnes avec des URL reddit, link et permalink. La colonne permalink est l'adresse link du commentaire lui-même, tandis que le lien est le lien vers l'article sous lequel le commentaire a été publié (c'est-à-dire l'article parent).

Nous pouvons utiliser JOIN pour ajouter des informations sur l'article parent à chaque commentaire, puisque chaque commentaire possède un lien vers l'article qui peut être utilisé pour identifier l'article parent.

N'oubliez pas que le tableau posts ne contient que vos propres messages, de sorte que les commentaires dont le lien parent ne se trouve pas dans le tableau posts seront rejetés par JOIN. Cela permet de filtrer les commentaires laissés sur les articles d'autres personnes. Pour en savoir plus sur le fonctionnement des JOIN, consultez notre article sur la jointure de deux tables en SQL.

Jetez un coup d'œil au résultat de la requête :

Voici permalink le lien vers le fil de commentaires sous l'article. Vous pouvez ouvrir le lien à partir du tableau des résultats et voir ce que cela donne par vous-même. La colonne "corps" correspond au texte principal du commentaire et la colonne "média" à l'URL de toute pièce jointe pouvant être incluse dans le commentaire. Un commentaire peut être composé uniquement de texte, uniquement de médias, ou à la fois de texte et de médias.

Le résultat est une liste complète des commentaires que vous avez laissés sur vos propres messages.

Activité de vote par Subreddit

Savez-vous comment vous vous engagez dans les subreddits que vous avez rejoints ? Cette requête permet de connaître le nombre d'upvotes et de downvotes que vous avez attribués dans chaque communauté :

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;

Comme le nom du subreddit n'est pas stocké directement dans la table votes nous pouvons utiliser une condition JOIN modifiée pour vérifier si le lien permanent du vote contient un nom de subreddit spécifique.

La clause ORDER BY permet de s'assurer que les subreddits ayant le plus d'engagements positifs sont affichés en premier.

Le résultat final révèle où vous avez déposé vos votes sur Reddit. Il répertorie les subreddits avec le nombre total d'upvotes et de downvotes que vous avez attribués, montrant ainsi les communautés que vous soutenez le plus et celles où vous avez peut-être suscité une petite controverse.

Le résultat final montre où j'ai distribué des votes sur Reddit. L'humour arrive en tête avec 1 382 votes positifs et très peu de votes négatifs, tandis que les mèmes suivent de près avec 1 225 votes positifs. Gaming, en revanche, a reçu 847 votes positifs et 101 votes négatifs, ce qui signifie que j'ai peut-être été un peu plus critique dans ce domaine.

Inscrits ou non inscrits : Où postez-vous réellement ?

Essayons maintenant une question plus complexe. Combien de fois postes-tu dans les subreddits dont tu fais partie par rapport aux subreddits que tu n'as pas rejoints ? Essayons de le découvrir.

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;

Cette requête doit être construite en deux étapes. Tout d'abord, nous classons tous les posts comme "Joined" ou "Not Joined", puis nous regroupons les résultats pour obtenir le nombre de posts dans chaque catégorie.

La première étape crée de nouvelles informations que nous utiliserons plus tard dans le regroupement, c'est donc une bonne pratique d'envelopper cette partie dans un CTE. Nous utilisons CASE WHEN pour déterminer la valeur de la nouvelle colonne jointe ; si le subreddit du message (p.subreddit) est l'un de ceux auxquels vous êtes abonné (s.subreddit), le message est classé comme "Joined". Dans le cas contraire, le message est classé dans la catégorie "Non joint".

La deuxième étape se déroule dans la requête externe. Nous pouvons regrouper par la nouvelle colonne jointe pour trouver le nombre de chaque type de message. Voici mon résultat :

Votre résultat ressemblera probablement à celui-ci dans la mesure où la plupart des posts que vous avez créés se trouvent dans des subreddits que vous avez rejoints. Si vous ne voyez pas de tableau de résultats avec les lignes comme celui ci-dessus (c'est-à-dire que vous n'obtenez qu'un seul résultat), cela signifie que vous n'avez créé que des messages d'un seul type.

L'analyse de Reddit sera-t-elle votre prochain projet SQL ?

C'est bien joué ! Nous avons acquis une bien meilleure compréhension des données que celle fournie par le récapitulatif Reddit de base - et nous avons pratiqué le langage SQL en même temps ! Vous pouvez développer ce projet en ajoutant des requêtes sur le nombre de messages que vous avez publiés au cours d'une période donnée, les messages publiés à partir de différents appareils et les cadeaux que vous avez reçus. Vous pouvez également vous plonger dans d'autres fichiers que vous avez téléchargés depuis Reddit, tels que friends, chat_history, comment_votes, et bien plus encore !

Si vous souhaitez vous entraîner davantage à l'utilisation de SQL, consultez nos La pratique du SQL Piste. Avec plus de 1 000 exercices interactifs, c'est un excellent moyen de renforcer vos compétences en SQL. Bonne pratique !