Projeto SQL: Recapitulação pessoal do Reddit

Seu Reddit Recap parece muito curto e sem sentido? Com o poder do SQL, você pode extrair insights muito mais profundos dos seus dados de uso! Descubra como criar esse interessante projeto SQL abaixo.

Criar um projeto pessoal de SQL é uma ótima maneira de praticar suas habilidades de criação e consulta de banco de dados, além de ser uma boa adição ao seu portfólio.

Neste artigo, abordaremos as maneiras de extrair o máximo de seus dados do Reddit e descobrir insights sobre sua atividade no Reddit. Neste projeto de SQL, vamos nos basear muito em tópicos avançados de análise de dados SQL. Se você precisar de uma atualização sobre como utilizar o SQL em um projeto como esse, confira nossa trilha de aprendizado de SQL para análise de dados.

Deseja criar uma recapitulação semelhante para outras plataformas? Confira nossos artigos sobre como criar seu próprio Netflix Wrapped e Spotify Wrapped com SQL.

Pronto? Vamos dar uma olhada em como você pode adquirir seus dados do Reddit e começar o projeto.

Configuração de seu projeto SQL pessoal do Reddit

Etapa 1: obter os dados

O Reddit permite que você solicite uma cópia de seus dados, incluindo algumas estatísticas de uso interessantes. Você pode fazer isso em sua página de solicitação de dados. É necessário estar conectado para visualizá-la.

Sua solicitação de dados levará algum tempo para ser processada; você receberá uma mensagem com o link para download na sua caixa de entrada do Reddit assim que ela for concluída.

Faça o download e descompacte o arquivo. Você verá 35 arquivos CSV contendo suas informações pessoais, incluindo informações sobre sua atividade, como publicações, comentários, subreddits inscritos, votos e outros dados diversos.

A maioria desses arquivos não contém informações que seriam úteis em um projeto SQL, portanto, vamos nos concentrar naqueles que contêm:

• posts.csv contém informações sobre as postagens que você criou.
• comments.csv contém informações sobre seus comentários. Observação: Esse arquivo é mais extenso do que comment_headers.csv, que contém apenas as informações básicas sobre o comentário.
• subscribed_subreddits.csv contém a lista de subreddits dos quais você participou. Há apenas uma coluna de dados, mas ela é útil quando você precisa filtrar pelo nome do subreddit.
• votes.csv contém seu histórico de votos como links para a publicação e a direção do voto (para cima ou para baixo).

Etapa 2: Carregar dados em um banco de dados

Os arquivos CSV contêm muitas informações, mas seria difícil extrair qualquer insight deles usando apenas planilhas. Aqui está um banco de dados SQL que pode simplificar o processo:

Esse esquema de banco de dados copia a estrutura dos arquivos CSV do arquivo. Com um banco de dados, poderemos recuperar e operar os dados com muito mais rapidez! Então, vamos criar um usando o PostgreSQL.

Criar um banco de dados

Aqui está o script para criar todas as tabelas mostradas acima:

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

Você pode executar o script acima no console psql; ele criará todas as tabelas necessárias para este projeto.

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

As tabelas não estão conectadas usando chaves estrangeiras. Pode haver algumas colunas comuns entre duas tabelas (como posts e comments referenciando o mesmo URL de postagem), mas elas não estão conectadas de forma alguma.

Além disso, preste atenção ao tipo de dados das colunas de data. Esse esquema usa timestamptz, que é um alias do PostgreSQL para timestamp with time zone. O Reddit armazena fusos horários em carimbos de data/hora, portanto, para importar corretamente os dados, você terá de usar esse tipo de dados específico.

Carregar os dados

A próxima etapa é importar os dados para o banco de dados a partir dos arquivos .csv que você baixou do Reddit. Use o seguinte comando no console psql:

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

Aqui estão todos os nomes das tabelas e seus respectivos arquivos CSV:

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

Não se esqueça de incluir o caminho absoluto para o arquivo antes do nome do arquivo e colocá-lo entre aspas simples.

Análise de sua atividade no Reddit com SQL

É hora de começar a fazer consultas! Vamos analisar alguns exemplos de consultas para mostrar o potencial que os dados têm. Vamos começar com algo simples.

Onde eu mais posto no Reddit?

Você sabe quantas vezes postou em cada subreddit? Aqui está uma consulta simples para ajudá-lo a descobrir:

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

Essa consulta revela seus hábitos de publicação listando os subreddits em que você compartilhou pelo menos cinco publicações. Para mim, os resultados são bastante reveladores:

• Os jogos são minha obsessão clara, com 17 publicações.
• Os memes ocupam um sólido segundo lugar, com 11 (porque não bastava rolar a tela, eu tinha que contribuir).
• Engraçado vem em terceiro lugar com 9 - aparentemente, eu me acho hilário.

Falando comigo mesmo: Comentários sobre suas próprias publicações

Você já participou de um AMA (ask me anything)? Pode ser interessante obter uma lista completa de todas as respostas que você deu. Vamos encontrar todos os comentários que você deixou em suas próprias publicações!

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

A tabela comments inclui duas colunas com URLs do reddit, link e permalink. A coluna permalink é o link do comentário em si, enquanto o link é o link da publicação na qual o comentário foi postado (ou seja, a publicação principal).

Podemos usar o JOIN para adicionar informações sobre a publicação principal a cada comentário, já que cada comentário tem um link de publicação que pode ser usado para identificar a publicação principal.

Lembre-se de que a tabela posts contém apenas suas próprias postagens, portanto, os comentários com um link pai não encontrado na tabela posts serão descartados pelo JOIN. Isso efetivamente filtra os comentários deixados nas postagens de outras pessoas. Você pode ler mais sobre como os JOINs funcionam em nosso artigo sobre como unir duas tabelas no SQL.

Dê uma olhada no resultado da consulta:

Aqui permalink está o link para o tópico de comentários sob a postagem. Você pode abrir o link da tabela de resultados e ver como ele se apresenta. A coluna body é o texto principal do comentário e media é um URL para qualquer anexo que possa ser incluído no comentário. Um comentário pode ser somente de texto, somente de mídia ou de texto e mídia.

O resultado é uma lista completa dos comentários que você deixou em suas próprias publicações.

Atividade de votação por subreddit

Você sabe como se envolve com os subreddits dos quais participa? Essa consulta encontra a quantidade de votos positivos e negativos que você deu em cada comunidade:

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 o nome do subreddit não está armazenado diretamente na tabela votes podemos usar uma condição JOIN modificada para verificar se o link permanente do voto contém um nome de subreddit específico.

A cláusula ORDER BY garante que os subreddits com mais engajamentos positivos sejam exibidos primeiro.

O resultado final revela onde você tem votado no Reddit. Ele lista os subreddits juntamente com o número total de votos positivos e negativos que você deu, mostrando quais comunidades você mais apoia - e onde você pode ter gerado alguma controvérsia.

O resultado final mostra onde eu estava distribuindo votos no Reddit. O gênero engraçado lidera o grupo com 1.382 votos positivos e quase nenhum negativo, enquanto os memes vêm logo em seguida com 1.225 votos positivos. Os jogos, por outro lado, receberam 847 votos positivos e 101 negativos, o que significa que eu poderia ter sido um pouco mais crítico.

Cadastrado vs. Não cadastrado: Onde você realmente posta?

Agora vamos tentar uma pergunta mais complicada. Com que frequência você publica nos subreddits dos quais faz parte em comparação com os subreddits dos quais não participa? Vamos descobrir.

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;

Essa consulta precisa ser criada em duas etapas. Primeiro, classificamos todas as postagens como "Participou" ou "Não participou" e, em seguida, agrupamos o resultado para obter o número de postagens em cada categoria.

A primeira etapa cria novas informações que usaremos no agrupamento posteriormente, portanto, é uma boa prática envolver essa parte em um CTE. Usamos o CASE WHEN para determinar o valor da nova coluna unida; se o subreddit da postagem (p.subreddit) for um dos que você assinou (s.subreddit), a postagem será classificada como "Unida". Caso contrário, a postagem será classificada como "Not Joined".

A segunda etapa acontece na consulta externa. Podemos agrupar pela nova coluna unida para encontrar o número de cada tipo de publicação. Aqui está o meu resultado:

Seu resultado provavelmente será semelhante a este, pois a maioria das postagens que você criou está em subreddits aos quais você se juntou. Se você não vir uma tabela de resultados com as linhas como a acima (ou seja, se obtiver apenas um único resultado), isso significa que você só criou posts de um tipo.

A análise do Reddit será seu próximo projeto SQL?

Muito bem! Adquirimos um entendimento muito melhor dos dados do que o que o Reddit Recap básico fornece - e praticamos SQL ao fazer isso! Você pode expandir esse projeto adicionando consultas sobre o número de postagens que você fez em um determinado período, postagens de diferentes dispositivos e elogios que você recebeu. Você também pode mergulhar em outros arquivos que baixou do Reddit, como friends, chat_history, comment_votes e muito mais!

Se estiver procurando por mais prática de SQL, confira nossa Trilha de Práticas em SQL Rastreamento. Com mais de 1.000 exercícios interativos, essa é uma ótima maneira de fortalecer suas habilidades em SQL. Boa prática!