Progetto SQL: Ricorso personale a Reddit

Il vostro Reddit Recap vi sembra troppo breve e privo di significato? Con la potenza dell'SQL, potete estrarre informazioni molto più profonde dai vostri dati di utilizzo! Scoprite come costruire questo interessante progetto SQL.

Realizzare un progetto SQL personale è un ottimo modo per esercitarsi nella costruzione e nell'interrogazione di database e può anche essere una bella aggiunta al proprio portfolio.

In questo articolo, vedremo come estrarre il massimo dai dati di Reddit e scoprire le informazioni sulla vostra attività su Reddit. In questo progetto SQL faremo molto affidamento su argomenti avanzati di analisi dei dati SQL. Se avete bisogno di un ripasso su come utilizzare SQL per un progetto come questo, date un'occhiata alla nostra traccia di apprendimento SQL per l'analisi dei dati.

Volete creare un riepilogo simile per altre piattaforme? Date un'occhiata ai nostri articoli su come creare il vostro Netflix Wrapped e Spotify Wrapped con SQL.

Pronti? Vediamo come acquisire i dati di Reddit e iniziare il progetto.

Impostazione del progetto SQL Reddit personale

Passo 1: ottenere i dati

Reddit consente di richiedere una copia dei propri dati, comprese alcune interessanti statistiche di utilizzo. È possibile farlo nella pagina di richiesta dei dati. Per visualizzarla è necessario aver effettuato il login.

L'elaborazione della richiesta di dati richiederà un po' di tempo; al termine riceverete un messaggio con il link per il download nella vostra casella di posta Reddit.

Scaricare e decomprimere l'archivio. Verranno visualizzati 35 file CSV contenenti i vostri dati personali, comprese le informazioni sulle vostre attività, come post, commenti, sottoredditi sottoscritti, voti e altri dati vari.

La maggior parte di questi file non contiene informazioni utili per un progetto SQL, quindi concentriamoci su quelli che lo sono:

• posts.csv contiene informazioni sui post creati dall'utente.
• comments.csv contiene informazioni sui vostri commenti. Nota: questo file è più esteso di comment_headers.csv, che contiene solo le informazioni di base sul commento.
• subscribed_subreddits.csv contiene l'elenco dei subreddit a cui ci si è uniti. C'è solo una colonna di dati, ma è utile quando si deve filtrare in base al nome del subreddit.
• votes.csv contiene la cronologia dei voti come link al post e la direzione del voto (su o giù).

Passo 2: caricare i dati in un database

I file CSV contengono molte informazioni, ma sarebbe difficile estrarle utilizzando solo fogli di calcolo. Ecco un database SQL che può semplificare il processo:

Questo schema di database copia la struttura dei file CSV dall'archivio. Con un database, saremo in grado di recuperare e operare sui dati molto più velocemente! Costruiamone uno utilizzando PostgreSQL.

Creare un database

Ecco lo script per la creazione di tutte le tabelle mostrate sopra:

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

Potete eseguire lo script di cui sopra nella vostra console psql; esso creerà tutte le tabelle necessarie per questo progetto.

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

Le tabelle non sono collegate tramite chiavi esterne. Potrebbero esserci alcune colonne comuni tra due tabelle (come posts e comments che fanno riferimento allo stesso URL del post), ma non sono in alcun modo collegate.

Inoltre, prestare attenzione al tipo di dati delle colonne di data. Questo schema utilizza timestamptz, che è un alias PostgreSQL per timestamp with time zone. Reddit memorizza i fusi orari in timestamp, quindi per importare correttamente i dati è necessario utilizzare questo tipo di dati specifico.

Caricare i dati

Il passo successivo consiste nell'importare i dati nel database dai file .csv scaricati da Reddit. Utilizzare il seguente comando nella console psql:

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

Ecco tutti i nomi delle tabelle e i rispettivi file CSV:

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

Non dimenticate di includere il percorso assoluto del file prima del nome del file e di inserirlo tra virgolette singole.

Analizzare l'attività di Reddit con SQL

È ora di fare delle query! Eseguiremo alcune query di esempio per mostrare il potenziale dei dati. Iniziamo con qualcosa di semplice.

Dove si pubblica di più su Reddit?

Sapete quante volte avete postato in ogni subreddit? Ecco una semplice query che vi aiuterà a scoprirlo:

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

Questa query scopre le vostre abitudini di pubblicazione elencando i subreddit in cui avete condiviso almeno cinque post. Per me, i risultati sono piuttosto eloquenti:

• Il gioco è la mia ossessione, con 17 post.
• I meme occupano un solido secondo posto con 11 (perché scorrere non era abbastanza, dovevo contribuire).
• Il divertimento si piazza al terzo posto con 9 - a quanto pare, penso di essere esilarante.

Parlando con me stesso: Commenti ai propri post

Avete mai fatto un AMA (ask me anything)? Sarebbe interessante avere un elenco completo di tutte le risposte che avete dato. Scopriamo tutti i commenti che avete lasciato sotto i vostri stessi post!

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

La tabella comments include due colonne con gli URL di reddit, link e permalink. La colonna permalink è l'URL link del commento stesso, mentre link è il link al post sotto il quale è stato pubblicato il commento (cioè il post padre).

Possiamo usare JOIN per aggiungere informazioni sul post padre a ogni commento, dato che ogni commento ha un link al post che può essere usato per identificare il post padre.

Ricordare che la tabella posts contiene solo i propri post, quindi i commenti con un link al genitore non trovato nella tabella posts saranno scartati da JOIN. In questo modo vengono effettivamente filtrati i commenti lasciati sui post di altre persone. Per saperne di più sul funzionamento delle JOIN, consultate il nostro articolo sull'unione di due tabelle in SQL.

Date un'occhiata all'output della query:

Qui permalink c'è il link al thread dei commenti sotto il post. È possibile aprire il link dalla tabella dei risultati e vedere come si presenta. La colonna corpo è il testo principale del commento e media è l'URL di qualsiasi allegato che può essere incluso nel commento. Un commento può essere solo testo, solo media o sia testo che media.

Il risultato è un elenco completo dei commenti lasciati dall'utente sui propri post.

Attività di voto per Subreddit

Sapete come vi impegnate nei subreddit a cui vi siete iscritti? Questa query trova la quantità di upvotes e downvotes che avete dato in ogni comunità:

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;

Poiché il nome del subreddit non è memorizzato direttamente nella tabella votes possiamo usare una condizione modificata di JOIN per verificare se il permalink del voto contiene un nome specifico di subreddit.

La clausola ORDER BY assicura che i subreddit con un maggior numero di impegni positivi vengano visualizzati per primi.

Il risultato finale rivela dove avete espresso i vostri voti su Reddit. Elenca i sottoreddit insieme al numero totale di voti positivi e negativi che avete dato, mostrando quali sono le comunità che sostenete di più e dove potreste aver suscitato qualche polemica.

Il risultato finale mostra dove ho distribuito voti su Reddit. Il divertimento è in testa con 1.382 voti in più e quasi nessun voto in meno, mentre i meme seguono da vicino con 1.225 voti in più. Gaming, invece, ha ottenuto 847 upvotes e 101 downvotes, il che significa che potrei essere stato un po' più critico.

Iscritti e non iscritti: Dove si posta effettivamente?

Ora proviamo a fare una domanda più complicata. Quanto spesso postate nei subreddit di cui fate parte rispetto a quelli a cui non siete iscritti? Scopriamolo.

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;

Questa query deve essere costruita in due fasi. Innanzitutto classifichiamo tutti i post come "Iscritto" o "Non Iscritto", quindi raggruppiamo i risultati per ottenere il numero di post in ciascuna categoria.

La prima fase crea nuove informazioni che verranno utilizzate in seguito nel raggruppamento, quindi è buona norma racchiudere questa parte in una CTE. Utilizziamo CASE WHEN per determinare il valore della nuova colonna joined; se il subreddit del post (p.subreddit) è uno a cui si è iscritti (s.subreddit), il post viene classificato come "Joined". Altrimenti, il post è "Non unito".

Il secondo passo avviene nella query esterna. Possiamo raggruppare per la nuova colonna unita per trovare il numero di ogni tipo di post. Ecco il mio risultato:

Il vostro risultato sarà probabilmente simile a questo, in quanto la maggior parte dei post che avete creato sono in sottoreddit a cui vi siete uniti. Se non vedete una tabella di risultati con le righe come quella qui sopra (cioè ottenete un solo risultato), significa che avete creato solo post di un tipo.

L'analisi di Reddit sarà il vostro prossimo progetto SQL?

Ben fatto! Abbiamo acquisito una comprensione dei dati molto più approfondita di quella fornita dalla Reddit Recap di base e abbiamo fatto pratica di SQL nel farlo! È possibile ampliare questo progetto aggiungendo query sul numero di post effettuati in un determinato periodo, sui post da dispositivi diversi e sui riconoscimenti ricevuti. Potete anche analizzare altri file scaricati da Reddit, come amici, chat_history, commenti_voti e altro ancora!

Se volete fare più pratica con l'SQL, date un'occhiata ai nostri Pratica su SQL Traccia. Con oltre 1.000 esercizi interattivi, è un ottimo modo per rafforzare le vostre competenze in SQL. Buona pratica!