Proyecto SQL: Resumen personal de Reddit

¿Te parece que tu resumen de Reddit es demasiado corto y sin sentido? Con el poder de SQL, puedes extraer información mucho más profunda de tus datos de uso. Descubre cómo crear este interesante proyecto SQL a continuación.

Crear un proyecto SQL personal es una buena forma de practicar tus habilidades de construcción y consulta de bases de datos, y también puede ser una buena adición a tu portafolio.

En este artículo, vamos a cubrir las formas de extraer el máximo provecho de sus datos de Reddit y descubrir ideas acerca de su actividad en Reddit. Nos basaremos en gran medida en temas avanzados de análisis de datos SQL en este proyecto SQL. Si necesita un repaso sobre cómo utilizar SQL para un proyecto como éste, consulte nuestro curso de SQL para análisis de datos.

¿Quieres crear un resumen similar para otras plataformas? Consulta nuestros artículos sobre cómo crear tu propio Netflix Wrapped y Spotify Wrapped con SQL.

¿Estás listo? Echemos un vistazo a cómo puedes adquirir tus datos de Reddit y empezar con el proyecto.

Configuración de tu proyecto personal Reddit SQL

Paso 1: Obtener los datos

Reddit le permite solicitar una copia de sus datos, incluyendo algunas estadísticas de uso interesantes. Puedes hacerlo en su página de Solicitud de Datos. Es necesario iniciar sesión para verla.

Tu solicitud de datos tardará algún tiempo en procesarse; recibirás un mensaje con el enlace de descarga en tu bandeja de entrada de Reddit una vez que se haya completado.

Descargue y descomprima el archivo. Verás 35 archivos CSV que contienen tu información personal, incluida la información sobre tu actividad, como publicaciones, comentarios, subreddits suscritos, votos y otros datos diversos.

La mayoría de estos archivos no contienen información útil para un proyecto SQL, así que vamos a centrarnos en los que sí la contienen:

• posts.csv contiene información sobre los mensajes que has creado.
• comments.csv contiene información sobre tus comentarios. Nota: Este archivo es más extenso que comment_headers.csv, que sólo contiene la información básica sobre el comentario.
• subscribed_subreddits.csv contiene la lista de subreddits a los que te has unido. Sólo hay una columna de datos, pero es útil cuando necesitas filtrar por el nombre del subreddit.
• votes.csv contiene tu historial de votos como enlaces a la publicación y la dirección del voto (ya sea "arriba" o "abajo").

Paso 2: Cargar datos en una base de datos

Los archivos CSV contienen mucha información, pero sería difícil extraer algo de ella utilizando sólo hojas de cálculo. He aquí una base de datos SQL que puede simplificar el proceso:

Este esquema de base de datos copia la estructura de los ficheros CSV del archivo. Con una base de datos, podremos recuperar y operar con los datos mucho más rápido. Así que vamos a crear una utilizando PostgreSQL.

Crear una base de datos

Aquí está el script para crear todas las tablas mostradas arriba:

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

Puede ejecutar el script anterior en su consola psql; creará todas las tablas necesarias para este proyecto.

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

Las tablas no están conectadas mediante claves externas. Puede haber algunas columnas comunes entre dos tablas (como posts y comments que hacen referencia a la misma URL de entrada), pero no están conectadas de ninguna manera.

Además, preste atención al tipo de datos de las columnas de fecha. Este esquema utiliza timestamptz, que es un alias de PostgreSQL para timestamp with time zone. Reddit almacena las zonas horarias en marcas de tiempo, por lo que para importar correctamente los datos tendrás que utilizar este tipo de datos específico.

Cargar los datos

El siguiente paso es importar los datos a la base de datos desde los archivos .csv que ha descargado de Reddit. Utilice el siguiente comando en su consola psql:

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

Aquí están todos los nombres de las tablas y sus respectivos archivos CSV:

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

No olvides incluir la ruta absoluta al archivo antes del nombre del archivo y encerrarlo entre comillas simples.

Análisis de su actividad en Reddit con SQL

¡Es hora de realizar consultas! Haremos algunas consultas de ejemplo para mostrar el potencial que tienen los datos. Empecemos con algo sencillo.

¿Dónde publico más en Reddit?

¿Sabes cuántas veces has publicado en cada subreddit? Aquí tienes una sencilla consulta que te ayudará a averiguarlo:

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

Esta consulta descubre tus hábitos de publicación enumerando los subreddits en los que has compartido al menos cinco publicaciones. En mi caso, los resultados son bastante reveladores:

• Los juegos son mi clara obsesión, con 17 publicaciones.
• Los memes ocupan un sólido segundo lugar con 11 (porque desplazarse no era suficiente, tenía que contribuir).
• Lo gracioso ocupa el tercer lugar con 9. Por lo visto, creo que soy divertidísimo.

Hablando solo: Comentarios sobre tus propias publicaciones

¿Has hecho alguna vez un AMA (pregúntame cualquier cosa)? Sería interesante obtener una lista completa de todas las respuestas que has dado. Busquemos todos los comentarios que has dejado en tus propias entradas.

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

La tabla comments incluye dos columnas con URLs de reddit, link y permalink. La columna permalink es el link al comentario en sí, mientras que link es el enlace al post bajo el que se publicó el comentario (es decir, el post padre).

Podemos utilizar JOIN para añadir información sobre la entrada principal a cada comentario, ya que cada comentario tiene un enlace a la entrada que puede utilizarse para identificar la entrada principal.

Recuerde que la tabla posts sólo contiene tus propias entradas, por lo que los comentarios con un enlace padre que no se encuentre en la tabla posts serán descartados por JOIN. De este modo, se filtran los comentarios dejados en las entradas de otras personas. Puede leer más sobre cómo funcionan los JOINs en nuestro artículo sobre Cómo unir dos tablas en SQL.

Eche un vistazo a la salida de la consulta:

Aquí permalink está el enlace al hilo de comentarios bajo la entrada. Puedes abrir el enlace desde la tabla de resultados y ver cómo se ve por ti mismo. La columna cuerpo es el texto principal del comentario y medios es una URL a cualquier archivo adjunto que pueda incluirse con el comentario. Un comentario puede ser sólo texto, sólo medios o ambos.

El resultado es una lista completa de los comentarios que has dejado en tus propias publicaciones.

Actividad de votación por subreddit

¿Sabes cómo te relacionas con los subreddits a los que te has unido? Esta consulta encuentra la cantidad de upvotes y downvotes que has dado en cada comunidad:

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;

Como el nombre del subreddit no se almacena directamente en la tabla votes podemos utilizar una condición modificada de JOIN para comprobar si el permalink del voto contiene un nombre de subreddit específico.

La cláusula ORDER BY se asegura de que los subreddits con más participaciones positivas se muestren en primer lugar.

El resultado final revela dónde has estado emitiendo tus votos en Reddit. Enumera los subreddits junto con el número total de upvotes y downvotes que has dado, mostrando qué comunidades apoyas más - y donde podrías haber provocado un poco de controversia.

El resultado final muestra dónde he estado repartiendo votos en Reddit. Divertido lidera el pelotón con 1.382 upvotes y apenas downvotes, mientras que memes le sigue de cerca con 1.225 upvotes. Los juegos, por otro lado, recibieron 847 votos positivos y 101 negativos, lo que significa que podría haber sido un poco más crítico.

Unidos vs. No unidos: ¿Dónde publicas realmente?

Hagamos ahora una pregunta más complicada. ¿Con qué frecuencia publicas en subreddits de los que formas parte en comparación con subreddits a los que no te has unido? Averigüémoslo.

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;

Esta consulta tiene que construirse en dos pasos. En primer lugar, clasificamos todas las publicaciones como "Participante" o "No participante" y, a continuación, agrupamos el resultado para obtener el número de publicaciones en cada categoría.

El primer paso crea nueva información que utilizaremos más tarde en la agrupación, por lo que es una buena práctica envolver esta parte en una CTE. Usamos CASE WHEN para determinar el valor de la nueva columna unida; si el subreddit de la entrada (p.subreddit) es uno al que estás suscrito (s.subreddit) entonces la entrada se clasifica como "Unida". En caso contrario, la entrada se clasifica como "No suscrita".

El segundo paso se produce en la consulta externa. Podemos agrupar por la nueva columna unida para encontrar el número de cada tipo de entrada. Este es mi resultado:

Tu resultado probablemente se parecerá a este en que la mayoría de los posts que has creado están en subreddits a los que te has unido. Si no ves una tabla de resultados con las filas como la de arriba (es decir, sólo obtienes un único resultado), significa que sólo has creado posts de un tipo.

¿Será el análisis de Reddit su próximo proyecto SQL?

¡Bien hecho! Hemos adquirido una comprensión de los datos mucho mejor que la que nos proporciona la Recapitulación básica de Reddit, ¡y hemos practicado SQL mientras lo hacíamos! Puedes ampliar este proyecto añadiendo consultas sobre el número de publicaciones que has hecho en un periodo determinado, las publicaciones desde distintos dispositivos y las doradas que has recibido. También puedes bucear en otros archivos que hayas descargado de Reddit, como friends, chat_history, comment_votes, ¡y mucho más!

Si buscas más práctica en SQL, echa un vistazo a nuestro Ejercicio de SQL Pista. Con más de 1.000 ejercicios interactivos, es una forma estupenda de reforzar tus conocimientos de SQL. ¡Feliz práctica!