Tentei recriar o relatório “Requisitos para o Nível de Confiança 3” que a equipe pode ver na página de perfil administrativo de um usuário como uma consulta do Data Explorer.
A motivação para isso é que, com o novo recurso que permite limitar uma consulta ao ID do usuário atual, usuários do TL2 podem verificar seu próprio progresso sem precisar pedir ajuda à equipe.
A consulta usa os valores configurados nas configurações do site e os compara com as estatísticas do usuário. Notei algumas pequenas diferenças entre a visão de administrador e os resultados da consulta, provavelmente causadas por arredondamento. A lógica para promoção ao TL3 é bastante complexa, então o SQL é necessariamente um pouco complicado, e ainda podem existir erros. Agradeceria muito qualquer feedback para que eu possa corrigir eventuais falhas.
Aqui está a consulta atual:
Atualmente, ela não está limitada ao current_user_id, pois, para testes, é mais fácil poder selecionar um usuário sobre o qual executar a consulta. Para alterar isso, mude a segunda linha para -- current_user_id :user_id
-- [params]
-- user_id :user_id
WITH settings AS (
SELECT
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_time_period'), 100) AS time_period,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_days_visited'), 50) AS min_days_visited,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_replied_to'), 10) AS min_topics_replied_to,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed'), 25) AS topics_viewed_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_cap'), 500) AS topics_viewed_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read'), 25) AS posts_read_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_cap'), 20000) AS posts_read_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_all_time'), 200) AS min_topics_viewed_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_all_time'), 500) AS min_posts_read_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_given'), 30) AS min_likes_given,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_received'), 20) AS min_likes_received,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_max_flagged'), 5) AS max_flagged
),
period AS (
SELECT
NOW() - (time_period || ' days')::interval AS since,
time_period
FROM settings
),
global_counts AS (
SELECT
(SELECT COUNT(*) FROM topics t, period p
WHERE t.created_at > p.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_recent,
(SELECT COUNT(*)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS posts_recent
),
user_stats AS (
SELECT
u.id AS user_id,
(SELECT COUNT(*) FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
AND uv.posts_read > 0
) AS days_visited,
(SELECT COUNT(DISTINCT p.topic_id)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.user_id = u.id
AND p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND t.user_id <> u.id
) AS topics_replied_to,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id, period pr
WHERE tv.user_id = u.id
AND tv.viewed_at > pr.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_recent,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
WHERE tv.user_id = u.id
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_all_time,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
) AS posts_read_recent,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv
WHERE uv.user_id = u.id
) AS posts_read_all_time,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 1
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_given,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received,
(SELECT COUNT(DISTINCT DATE(ua.created_at))
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_days,
(SELECT COUNT(DISTINCT ua.acting_user_id)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_users
FROM users u
WHERE u.id = :user_id
),
flags AS (
SELECT
COUNT(DISTINCT p.id) AS flagged_posts,
COUNT(DISTINCT pa.user_id) AS flagged_users
FROM posts p
JOIN post_actions pa ON pa.post_id = p.id
JOIN period pr ON true
WHERE p.user_id = :user_id
AND p.created_at > pr.since
AND pa.agreed_at IS NOT NULL
AND pa.user_id <> :user_id
AND (p.spam_count > 0 OR p.inappropriate_count > 0)
),
penalty_history AS (
SELECT
uh.target_user_id AS user_id,
SUM(
CASE
WHEN uh.action = 30 THEN 1
WHEN uh.action = 31 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS silenced_delta,
SUM(
CASE
WHEN uh.action = 10 THEN 1
WHEN uh.action = 11 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS suspended_delta
FROM user_histories uh
WHERE uh.created_at > NOW() - INTERVAL '6 months'
AND uh.target_user_id = :user_id
GROUP BY uh.target_user_id
),
penalties AS (
SELECT
u.id AS user_id,
COALESCE(ph.silenced_delta, 0)
+ CASE
WHEN u.silenced_till IS NOT NULL AND u.silenced_till > NOW()
THEN 1 ELSE 0
END AS silenced,
COALESCE(ph.suspended_delta, 0)
+ CASE
WHEN u.suspended_till IS NOT NULL AND u.suspended_till > NOW()
THEN 1 ELSE 0
END AS suspended
FROM users u
LEFT JOIN penalty_history ph ON ph.user_id = u.id
WHERE u.id = :user_id
)
SELECT
requirement,
required_value,
user_value,
fulfill
FROM (
SELECT
'Tópicos Respondidos (últimos ' || s.time_period || ' dias)' AS requirement,
s.min_topics_replied_to AS required_value,
us.topics_replied_to AS user_value,
CASE
WHEN us.topics_replied_to >= s.min_topics_replied_to
THEN '✔' ELSE '✘'
END AS fulfill
FROM user_stats us, settings s
UNION ALL
SELECT
'Tópicos Visualizados (últimos ' || s.time_period || ' dias)',
LEAST(CEIL(gc.topics_recent * s.topics_viewed_percent / 100.0), s.topics_viewed_cap)::int,
us.topics_viewed_recent,
CASE WHEN us.topics_viewed_recent >=
LEAST(gc.topics_recent * s.topics_viewed_percent / 100, s.topics_viewed_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Tópicos Visualizados (tempo todo)',
s.min_topics_viewed_all_time,
us.topics_viewed_all_time,
CASE WHEN us.topics_viewed_all_time >= s.min_topics_viewed_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Lidos (últimos ' || s.time_period || ' dias)',
LEAST( CEIL(gc.posts_recent * s.posts_read_percent / 100.0), s.posts_read_cap)::int,
us.posts_read_recent,
CASE WHEN us.posts_read_recent >=
LEAST(gc.posts_recent * s.posts_read_percent / 100, s.posts_read_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Posts Lidos: dias únicos (últimos ' || s.time_period || ' dias)',
s.min_days_visited,
us.days_visited,
CASE WHEN us.days_visited >= s.min_days_visited THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Lidos (tempo todo)',
s.min_posts_read_all_time,
us.posts_read_all_time,
CASE WHEN us.posts_read_all_time >= s.min_posts_read_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Sinalizados (últimos ' || s.time_period || ' dias)',
s.max_flagged,
f.flagged_posts,
CASE WHEN f.flagged_posts <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Usuários que Sinalizaram (últimos ' || s.time_period || ' dias)',
s.max_flagged,
f.flagged_users,
CASE WHEN f.flagged_users <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Curtidas Dadas (últimos ' || s.time_period || ' dias)',
s.min_likes_given,
us.likes_given,
CASE WHEN us.likes_given >= s.min_likes_given THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Curtidas Recebidas (últimos ' || s.time_period || ' dias)',
s.min_likes_received,
us.likes_received,
CASE WHEN us.likes_received >= s.min_likes_received THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Curtidas Recebidas: dias únicos (últimos ' || s.time_period || ' dias)',
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int,
us.likes_received_days,
CASE WHEN us.likes_received_days >=
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Curtidas Recebidas: usuários únicos (últimos ' || s.time_period || ' dias)',
CEIL(s.min_likes_received / 4.0)::int,
us.likes_received_users,
CASE WHEN us.likes_received_users >= CEIL(s.min_likes_received / 4.0)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Silenciado (últimos 6 meses)',
0,
p.silenced,
CASE WHEN p.silenced = 0 THEN '✔' ELSE '✘' END
FROM penalties p
UNION ALL
SELECT
'Suspenso (últimos 6 meses)',
0,
p.suspended,
CASE WHEN p.suspended = 0 THEN '✔' ELSE '✘' END
FROM penalties p
) x
Exemplo de resultado:
