Leaderboard Tabnews: Reconhecendo a Excelência na Comunidade

Leaderboard da Comunidade Tabnews

Olá, Tabnews!

É com imensa alegria que compartilho um algo empolgante para a comunidade: um Leaderboard do Tabnews. Este projeto, apesar de ser uma iniciativa independente e não oficial, tem o potencial de enriquecer nossa experiência na plataforma, destacando as contribuições valiosas dos membros.

O Ponto de Partida

Originalmente, embarquei na jornada de criar um blog estático para minhas postagens na plataforma. No entanto, durante esta empreitada, deparei-me com uma oportunidade empolgante de criar algo muito maior para a comunidade: uma leaderboard que destaca o engajamento ativo no Tabnews.

A Mágia do SQL

Tudo que precisamos para criar nossa Leaderboard é um banco de dados contendo uma tabela com todos os posts. Eu usei bash, curl e parallel, e espero contar sobre isso em outro post, outras pessoas fizeram coisas semelhantes em JavaScript e Python, Mas iremos além com consultas de SQL para extrair insights significativos. Esta Leaderboard é um testamento ao poder do SQL, em especial as window functions se alergico à SQL, não continue.

Ranking Mensal

O processo começa seperando as postagens em duas categorias: originais e comentários, permitindo que os usuários disputem em ambos os domínios. Depois as postagens são ranqueadas com base no número de tabcoins recebidos (com comentários como critério de desempate) para cada mês.

ranked_originals AS (
    SELECT 
        strftime('%m', updated_at) AS month, 
        uri, 
        tabcoins,
	ROW_NUMBER() OVER (PARTITION BY strftime('%m', updated_at) ORDER BY tabcoins DESC, children_deep_count DESC) AS seq,
        RANK() OVER (PARTITION BY strftime('%m', updated_at) ORDER BY tabcoins DESC, children_deep_count DESC) AS rank
    FROM originals
	
),
ranked_comments AS (
        SELECT 
            strftime('%m', updated_at) AS month, 
            uri, 
            tabcoins,
	    ROW_NUMBER() OVER (PARTITION BY strftime('%m', updated_at) ORDER BY tabcoins DESC, children_deep_count DESC) AS seq,
            RANK() OVER (PARTITION BY strftime('%m', updated_at) ORDER BY tabcoins DESC, children_deep_count DESC) AS rank
        FROM comments
),
ranked_data as ( 
SELECT 
    ro.rank as rankO,
    ro.month, 
    ro.uri as original_uri ,
    ro.tabcoins as ro_tabcoins,
    rc.rank as rankC,
    rc.uri as comment_uri, 
    rc.tabcoins as rc_tabcoins
FROM ranked_comments rc
LEFT JOIN ranked_originals ro ON rc.month = ro.month AND rc.seq = ro.seq
ORDER BY rc.month, rc.rank, ro.rank)
SELECT * FROM ranked_data WHERE rankO <= 10 AND rankC <= 10;  

Com um toque de magia AWK, convertemos esses dados em tabelas Markdown bem formatadas, perfeitas para compartilhar e discutir dentro da nossa comunidade. Antes de continuar veja os 10 primeiros posts originais de cada mês e as melhores respostas para entender como a Leaderboard funciona.

Construindo a Leaderboard

Vamos atribuír pontos a cada posição no ranking mensal. Transformando os ranks mensais em uma corrida anual, onde os usuários podem ganham pontos todos os meses com base na classificação de seus posts. Por simplicidade vamos usar a mesma tabela da Formula 1.

points AS (
    SELECT 
        rankO as rank,
        CASE rankO
            WHEN 1 THEN 25
            WHEN 2 THEN 18
            WHEN 3 THEN 15
            WHEN 4 THEN 12
            WHEN 5 THEN 10
            WHEN 6 THEN 8
            WHEN 7 THEN 6
            WHEN 8 THEN 4
            WHEN 9 THEN 2
            WHEN 10 THEN 1
            ELSE 0
        END AS points
    FROM (SELECT DISTINCT rankO FROM ranked_data where rankO <=10)
),

formated_leader_board as (
SELECT * from  ranked_data  r 
JOIN points o on o.rank = r.rankO
JOIN points c on c.rank = r.rankC 
where r.rankO <=10 AND r.rankC <=10
),

all_users AS (
    SELECT DISTINCT SUBSTR(uri, 1, INSTR(uri, '/') - 1) AS user
    FROM (
        SELECT original_uri AS uri FROM formated_leader_board
        UNION ALL
        SELECT comment_uri AS uri FROM formated_leader_board
    )
),
all_months AS (
    SELECT DISTINCT month
    FROM formated_leader_board
),

all_user_months AS (
    SELECT user, month
    FROM all_users, all_months
),

user_positions AS (
    SELECT 
        aum.month,
        aum.user,
        COALESCE(up.points, 0) AS points
    FROM all_user_months aum
    LEFT JOIN (
        SELECT 
            month, 
            SUBSTR(original_uri, 1, INSTR(original_uri, '/') - 1) AS user, 
            pointsC AS points
        FROM formated_leader_board
        WHERE original_uri IS NOT NULL
        UNION ALL
        SELECT 
            month, 
            SUBSTR(comment_uri, 1, INSTR(comment_uri, '/') - 1) AS user, 
            pointsO AS points
        FROM formated_leader_board
    ) up ON aum.user = up.user AND aum.month = up.month
),

user_points as (
SELECT 
    month, 
    user, 
    SUM(points) as total_points
from user_positions
GROUP BY month, user
ORDER BY month, total_points DESC),

monthly_leader_board AS (
    SELECT 
        month,
        user, 
        SUM(total_points) as monthly_points,
        RANK() OVER (PARTITION BY month ORDER BY SUM(total_points) DESC) as monthly_rank
    FROM user_points
    GROUP BY month, user
),
cumulative_points AS (
    SELECT 
        user,
        month,
        SUM(monthly_points) OVER (PARTITION BY user ORDER BY month) as cumulative_points
    FROM monthly_leader_board
),
cumulative_leader_board AS (
    SELECT 
        cp.month,
        cp.user,
        cp.cumulative_points,
        mlb.monthly_points,
        mlb.monthly_rank,
        RANK() OVER (PARTITION BY cp.month ORDER BY cp.cumulative_points DESC) as cumulative_rank
    FROM cumulative_points cp
    JOIN monthly_leader_board mlb ON cp.user = mlb.user AND cp.month = mlb.month
),
rank_deltas AS (
    SELECT 
        clb.month,
        clb.user,
        clb.cumulative_rank,
        LAG(clb.cumulative_rank) OVER (PARTITION BY clb.user ORDER BY clb.month) AS prev_month_rank
    FROM cumulative_leader_board clb 
) ,
year_leaderboard AS (
SELECT 
    clb.month, 
    clb.cumulative_rank,
    clb.cumulative_points, 
    clb.user, 
    clb.monthly_points, 
    clb.monthly_rank, 
    COALESCE(rd.prev_month_rank - clb.cumulative_rank, 0) AS rank_delta
FROM cumulative_leader_board clb
LEFT JOIN rank_deltas rd ON clb.month = rd.month AND clb.user = rd.user
ORDER BY clb.month, clb.cumulative_rank)

SELECT * FROM year_leaderboard where cumulative_rank <= 15;

A Leaderboard mês-a-mês

Janeiro

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	26	victorharry	26	0
2	25	VictorManhani	25	0
2	25	avuenja	25	0
4	18	TheSirion	18	0
5	16	FilipeNevola	16	0
6	15	SezinandoVieira	15	0
6	15	Silva97	15	0
8	12	FelipeBarso	12	0
9	10	programadoraos30	10	0
9	10	rphlfc	10	0
11	8	GTEX	8	0
12	6	GabrielSozinho	6	0
12	6	jjeanjacques10	6	0
14	4	ghostnetrn	4	0
14	4	joaovcoelho	4	0
14	4	tiagocosta	4	0

Fevereiro

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	53	filipedeschamps	53	18
2	28	FilipeNevola	12	3
3	26	victorharry	0	-2
4	25	VictorManhani	0	-2
4	25	avuenja	0	-2
4	25	matheuspazinati	25	15
7	19	maniero	19	12
8	18	MatheusCastro	18	11
8	18	TheSirion	0	-4
10	15	MatheusManuel	15	9
10	15	SezinandoVieira	0	-4
10	15	Silva97	0	-4
10	15	lucab03	15	9
14	14	viniciussantos45	14	5
15	12	FelipeBarso	0	-7

Março

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	96	filipedeschamps	43	0
2	57	FelipeBarso	45	13
3	34	maniero	15	4
4	28	FilipeNevola	0	-2
5	26	victorharry	0	-2
6	25	VictorManhani	0	-2
6	25	avuenja	0	-2
6	25	eliasnsz	25	24
6	25	matheuspazinati	0	-2
10	21	guites	21	20
11	18	MatheusCastro	0	-3
11	18	TheSirion	0	-3
13	15	MatheusManuel	0	-3
13	15	SezinandoVieira	0	-3
13	15	Silva97	0	-3
13	15	lucab03	0	-3

Abril

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	172	filipedeschamps	76	0
2	58	FelipeBarso	1	0
3	34	maniero	0	0
4	28	FilipeNevola	0	0
5	26	victorharry	0	0
6	25	Silva97	10	7
6	25	VictorManhani	0	0
6	25	avuenja	0	0
6	25	eliasnsz	0	0
6	25	luaneduardo	25	33
6	25	matheuspazinati	0	0
12	21	guites	0	-2
13	18	MatheusCastro	0	-2
13	18	TheSirion	0	-2
13	18	jgamaralv	18	26
13	18	viniielopes	18	26

Maio

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	196	filipedeschamps	24	0
2	59	maniero	25	1
3	58	FelipeBarso	0	-1
4	30	FilipeNevola	2	0
5	29	kht	23	32
6	27	matheuspazinati	2	0
7	26	victorharry	0	-2
8	25	Silva97	0	-2
8	25	VictorManhani	0	-2
8	25	avuenja	0	-2
8	25	eliasnsz	0	-2
8	25	luaneduardo	0	-2
8	25	revogabe	25	42
14	21	guites	0	-2
15	18	Edson295	18	35
15	18	MatheusCastro	0	-2
15	18	TheSirion	0	-2
15	18	jgamaralv	0	-2
15	18	joelcarneiro	18	35
15	18	viniielopes	0	-2

Junho

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	247	filipedeschamps	51	0
2	73	maniero	14	0
3	58	FelipeBarso	0	0
4	33	kht	4	1
5	30	FilipeNevola	0	-1
6	27	matheuspazinati	0	0
7	26	victorharry	0	0
8	25	Silva97	0	0
8	25	VictorManhani	0	0
8	25	avuenja	0	0
8	25	eliasnsz	0	0
8	25	juninhown	25	52
8	25	luaneduardo	0	0
8	25	revogabe	0	0
15	21	guites	0	-1

Julho

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	272	filipedeschamps	25	0
2	85	maniero	12	0
3	82	FelipeBarso	24	0
4	53	uriel	43	36
5	39	kht	6	-1
6	30	FilipeNevola	0	-1
7	27	matheuspazinati	0	-1
8	26	victorharry	0	-1
9	25	Silva97	0	-1
9	25	VictorManhani	0	-1
9	25	avuenja	0	-1
9	25	eliasnsz	0	-1
9	25	juninhown	0	-1
9	25	luaneduardo	0	-1
9	25	revogabe	0	-1
9	25	viniciusvas90	25	62

Agosto

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	272	filipedeschamps	0	0
2	96	maniero	11	0
3	82	FelipeBarso	0	0
4	63	kht	24	1
5	53	uriel	0	-1
6	33	VictorManhani	8	3
7	30	FilipeNevola	0	-1
8	27	matheuspazinati	0	-1
9	26	victorharry	0	-1
10	25	Silva97	0	-1
10	25	allangrds	25	70
10	25	avuenja	0	-1
10	25	bernardosimonassi	25	70
10	25	eliasnsz	0	-1
10	25	juninhown	0	-1
10	25	luaneduardo	0	-1
10	25	revogabe	0	-1
10	25	viniciusvas90	0	-1

Setembro

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	290	filipedeschamps	18	0
2	117	maniero	21	0
3	103	kht	40	1
4	94	FelipeBarso	12	-1
5	53	uriel	0	0
6	43	bernardosimonassi	18	4
7	33	VictorManhani	0	-1
8	30	FilipeNevola	0	-1
9	27	matheuspazinati	0	-1
10	26	victorharry	0	-1
11	25	Silva97	0	-1
11	25	allangrds	0	-1
11	25	avuenja	0	-1
11	25	caiquearaujo	25	81
11	25	eliasnsz	0	-1
11	25	juninhown	0	-1
11	25	luaneduardo	0	-1
11	25	revogabe	0	-1
11	25	viniciusvas90	0	-1

Outubro

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	308	filipedeschamps	18	0
2	148	maniero	31	0
3	113	kht	10	0
4	94	FelipeBarso	0	0
5	54	Silva97	29	6
6	53	uriel	0	-1
7	43	bernardosimonassi	0	-1
8	33	VictorManhani	0	-1
9	30	FilipeNevola	0	-1
9	30	eliaseas	15	25
11	27	matheuspazinati	0	-2
12	26	victorharry	0	-2
13	25	allangrds	0	-2
13	25	avuenja	0	-2
13	25	caiquearaujo	0	-2
13	25	eliasnsz	0	-2
13	25	juninhown	0	-2
13	25	luaneduardo	0	-2
13	25	marcosviniciusftd	25	90
13	25	revogabe	0	-2
13	25	viniciusvas90	0	-2

Novembro

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	323	filipedeschamps	15	0
2	191	maniero	43	0
3	119	FelipeBarso	25	1
4	113	kht	0	-1
5	64	uriel	11	1
6	54	Silva97	0	-1
7	43	bernardosimonassi	0	0
8	37	caiquearaujo	12	5
9	33	VictorManhani	0	-1
10	32	Wellington79	18	34
11	30	FilipeNevola	0	-2
11	30	eliaseas	0	-2
13	27	matheuspazinati	0	-2
14	26	daniellimae	18	55
14	26	marcosviniciusftd	1	-1
14	26	victorharry	0	-2

Dezembro

Posição Geral	Pontuação Total	Usuário	Pontuação Mensal	Variação
1	323	filipedeschamps	0	0
2	260	maniero	69	0
3	135	kht	22	1
4	119	FelipeBarso	0	-1
5	64	uriel	0	0
6	54	Silva97	0	0
7	43	bernardosimonassi	0	0
8	41	moacirmoda	25	32
9	38	eliaseas	8	2
10	37	caiquearaujo	0	-2
11	35	clacerda	16	16
12	33	VictorManhani	0	-3
13	32	Wellington79	0	-3
14	30	FilipeNevola	0	-3
15	27	matheuspazinati	0	-2

A Dinâmica Competitiva do Leaderboard: Excelência, Sorte, Timing e Consistência

O Leaderboard da comunidade Tabnews é uma corrida emocionante que combina não apenas a excelência, mas também sorte e timing. Embora a estrutura do ranking vise justiça e reconhecimento da qualidade, não podemos ignorar um elemento de aleatoridade que faz parte de qualquer competição verdadeiramente emocionante.

Na corrida para o topo do Leaderboard, o timing é pode ser tudo. Uma postagem bem elaborada, lançada no momento certo, pode capturar a atenção da comunidade e ganhar uma quantidade significativa de tabcoins, catapultando o autor para os rankings superiores.

Neste sistema de classificação, ganhar uma única "corrida" – ou seja, ter a postagem ou comentário mais bem avaliado do mês – pode levar quase imediatamente a uma posição de destaque no ranking. Isso adiciona um elemento de imprevisibilidade e excitação ao Leaderboard, onde uma única contribuição excepcional pode ser a chave para se destacar rapidamente.

No entanto, o Leaderboard ainda é capaz de reconhecer a consistência. Enquanto algumas pessoas podem aparecer no ranking devido a uma contribuição particularmente bem-sucedida, a consistência de postagens e comentários valiosos ao longo do tempo (nos últimos meses) realmentem começam a pagar. Eu mesmo me juntei à festa apenas nesta última corrida, tendo acumulando pontos de forma consistente desde outubro, mesmo sem uma única corrida esmagadora.

Olhando para o Futuro: Novas Possibilidades

Este projeto é só o começo. Já decidi expandir o conceito para incluir "revistas" mensais com as melhores publicações, criadas com o Pandoc e hospedadas via Nginx, como uma extensão natural do projeto do blog pessoal que começou tudo. Essa é a verdadeira beleza de uma boa arquitetura de software: a facilidade com que podemos construir, adaptar e melhorar.

Convite à Participação

Convido cada um de vocês a se envolver, discutir e contribuir para o aprimoramento contínuo deste leaderboard. Seus insights e experiências são valiosos para todos nós. Lembre-se, dezembro ainda está em andamento, e em 1º de janeiro, celebraremos os vencedores de 2023!

Este leaderboard é mais do que uma funcionalidade; é uma celebração à nossa comunidade. É sobre reconhecer suas contribuições, fomentar uma competição saudável e nos aproximar ainda mais.

A integração desse sistema ao Tabnews abriria portas para atualizações em tempo real e um reconhecimento mais expressivo das contribuições da nossa comunidade.

Um abraço e bons estudos!