Execução em tempo real no banco Neon Serverless (Database Social)
Para atender aos 35 pontos do Deliverable 4 (The Analysis: Answering Business Questions), foram definidas 5 perguntas de negócio fundamentais que cobrem todas as competências exigidas pelo curso: JOIN, Funções Agregadas, GROUP BY, GROUP BY com HAVING, e Query de Escolha Livre (além do bônus com CTE).
"Para a usuária 'ana_dev' (user_id = 1), quais são as postagens mais recentes publicadas pelas contas que ela segue?"
SELECT
u.username AS author_username,
u.full_name AS author_name,
p.post_id,
p.content AS post_content,
p.created_at AS published_at
FROM follows f
JOIN users u ON u.user_id = f.following_id
JOIN posts p ON p.user_id = u.user_id
WHERE f.follower_id = 1
ORDER BY p.created_at DESC
LIMIT 5;
| author_username | author_name | post_id | post_content | published_at |
|---|---|---|---|---|
| bruno_art | Bruno Costa | 3 | Novo wireframe do app pronto. O que acharam do layout minimalista? | 2026-10-12 10:23:43.886303 |
| bruno_art | Bruno Costa | 4 | Cores que combinam com dark mode: sempre teste contraste WCAG antes. | 2026-10-12 10:23:43.886303 |
| carla_data | Carla Mendes | 5 | Analisei 1M de tweets hoje. Thread com insights em breve 🧵 | 2026-10-12 10:23:43.886303 |
| carla_data | Carla Mendes | 6 | Pandas vs Polars: qual vocês preferem para datasets de grande porte? | 2026-10-12 10:23:43.886303 |
| diego_ops | Diego Rocha | 7 | Kubernetes não precisa ser assustador. Comece com cluster local k3s. | 2026-10-12 10:23:43.886303 |
"Qual é o total geral de postagens, curtidas, comentários e a média de engajamento por post?"
SELECT
COUNT(DISTINCT p.post_id) AS total_posts,
COUNT(DISTINCT l.like_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
ROUND(COUNT(DISTINCT l.like_id)::NUMERIC / NULLIF(COUNT(DISTINCT p.post_id), 0), 2) AS avg_likes_per_post,
ROUND(COUNT(DISTINCT c.comment_id)::NUMERIC / NULLIF(COUNT(DISTINCT p.post_id), 0), 2) AS avg_comments_per_post
FROM posts p
LEFT JOIN likes l ON l.post_id = p.post_id
LEFT JOIN comments c ON c.post_id = p.post_id;
| total_posts | total_likes | total_comments | avg_likes_per_post | avg_comments_per_post |
|---|---|---|---|---|
| 15 | 50 | 20 | 3.33 | 1.33 |
"Quais criadores de conteúdo acumularam o maior número de curtidas em seus posts?"
SELECT
u.user_id,
u.username,
u.full_name,
COUNT(DISTINCT p.post_id) AS total_published_posts,
COUNT(l.like_id) AS total_likes_received
FROM users u
JOIN posts p ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
GROUP BY u.user_id, u.username, u.full_name
ORDER BY total_likes_received DESC, total_published_posts DESC;
| user_id | username | full_name | total_published_posts | total_likes_received |
|---|---|---|---|---|
| 1 | ana_dev | Ana Silva | 2 | 8 |
| 3 | carla_data | Carla Mendes | 2 | 7 |
| 5 | elisa_pm | Elisa Nunes | 2 | 7 |
| 7 | gabi_mobile | Gabi Lopes | 2 | 7 |
| 2 | bruno_art | Bruno Costa | 2 | 6 |
| 6 | felipe_sec | Felipe Araújo | 2 | 6 |
| 4 | diego_ops | Diego Rocha | 2 | 5 |
| 8 | hugo_ai | Hugo Pereira | 1 | 4 |
"Quais publicações fomentaram debates na comunidade (receberam 2 ou mais comentários)?"
SELECT
p.post_id,
u.username AS author,
SUBSTRING(p.content, 1, 45) || '...' AS post_snippet,
COUNT(c.comment_id) AS total_comments
FROM posts p
JOIN users u ON u.user_id = p.user_id
JOIN comments c ON c.post_id = p.post_id
GROUP BY p.post_id, u.username, p.content
HAVING COUNT(c.comment_id) >= 2
ORDER BY total_comments DESC, p.post_id ASC;
| post_id | author | post_snippet | total_comments |
|---|---|---|---|
| 1 | ana_dev | Acabei de subir meu primeiro projeto open sou... | 2 |
| 2 | ana_dev | Dica: sempre use índices em FKs que aparecem ... | 2 |
| 3 | bruno_art | Novo wireframe do app pronto. O que acharam d... | 2 |
| 5 | carla_data | Analisei 1M de tweets hoje. Thread com insigh... | 2 |
| 9 | elisa_pm | Roadmap Q1 fechado! Equipe totalmente alinhad... | 2 |
| 11 | felipe_sec | Senhas em 2026: ainda encontramos usuários ut... | 2 |
| 14 | gabi_mobile | Primeiro aplicativo publicado na Google Play ... | 2 |
| 15 | hugo_ai | Testando novo modelo de linguagem local via O... | 2 |
"Quais usuários possuem saldo social positivo (mais seguidores que seguindo) e qual a taxa de influência?"
SELECT
u.user_id,
u.username,
u.full_name,
(SELECT COUNT(*) FROM follows WHERE following_id = u.user_id) AS total_followers,
(SELECT COUNT(*) FROM follows WHERE follower_id = u.user_id) AS total_following,
ROUND(
(SELECT COUNT(*) FROM follows WHERE following_id = u.user_id)::NUMERIC /
NULLIF((SELECT COUNT(*) FROM follows WHERE follower_id = u.user_id), 0), 2
) AS influence_ratio
FROM users u
WHERE (SELECT COUNT(*) FROM follows WHERE following_id = u.user_id) >=
(SELECT COUNT(*) FROM follows WHERE follower_id = u.user_id)
ORDER BY total_followers DESC, influence_ratio DESC;
| user_id | username | full_name | total_followers | total_following | influence_ratio |
|---|---|---|---|---|---|
| 3 | carla_data | Carla Mendes | 6 | 3 | 2.00 |
| 1 | ana_dev | Ana Silva | 5 | 3 | 1.67 |
| 2 | bruno_art | Bruno Costa | 3 | 2 | 1.50 |
| 4 | diego_ops | Diego Rocha | 2 | 2 | 1.00 |
"Quais são os 3 posts com maior pontuação combinada de engajamento (Likes x1 + Comentários x2)?"
WITH PostEngagement AS (
SELECT
p.post_id,
p.user_id,
p.content,
COUNT(DISTINCT l.like_id) AS likes_count,
COUNT(DISTINCT c.comment_id) AS comments_count,
(COUNT(DISTINCT l.like_id) * 1 + COUNT(DISTINCT c.comment_id) * 2) AS engagement_score
FROM posts p
LEFT JOIN likes l ON l.post_id = p.post_id
LEFT JOIN comments c ON c.post_id = p.post_id
GROUP BY p.post_id, p.user_id, p.content
)
SELECT
pe.post_id,
u.username AS author,
pe.content AS post_text,
pe.likes_count,
pe.comments_count,
pe.engagement_score
FROM PostEngagement pe
JOIN users u ON u.user_id = pe.user_id
ORDER BY pe.engagement_score DESC
LIMIT 3;
| post_id | author | post_text | likes_count | comments_count | engagement_score |
|---|---|---|---|---|---|
| 1 | ana_dev | Acabei de subir meu primeiro projeto open source! 🎉 | 5 | 2 | 9 |
| 9 | elisa_pm | Roadmap Q1 fechado! Equipe totalmente alinhada com as metas. | 5 | 2 | 9 |
| 3 | bruno_art | Novo wireframe do app pronto. O que acharam do layout minimalista? | 4 | 2 | 8 |