Ho provato a ricreare il rapporto “Requisiti per il Livello di Fiducia 3” che lo staff può vedere nella pagina del profilo amministrativo di un utente, trasformandolo in una query di Data Explorer.
La motivazione è che, con la nuova funzionalità che permette di limitare una query all’ID dell’utente corrente, gli utenti TL2 possono verificare il proprio progresso senza dover chiedere allo staff.
La query utilizza i valori configurati nelle impostazioni del sito e li confronta con le statistiche dell’utente. Ho notato alcune piccole differenze tra la vista amministrativa e i risultati della query, probabilmente causate dall’arrotondamento. La logica per la promozione al TL3 è piuttosto complessa, quindi il SQL è necessariamente un po’ complicato e potrebbero esserci ancora degli errori. Apprezzerei moltissimo qualsiasi feedback per poter correggere eventuali inesattezze.
Ecco la query attuale:
Attualmente, questa non è limitata a current_user_id perché, per i test, è più facile poter selezionare l’utente su cui eseguire la query. Per modificare questo comportamento, dovresti cambiare la seconda riga in -- 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
'Topics Replied To (last ' || s.time_period || ' days)' 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
'Topics Viewed (last ' || s.time_period || ' days)',
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
'Topics Viewed (all time)',
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 Read (last ' || s.time_period || ' days)',
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 Read: unique days (last ' || s.time_period || ' days)',
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 Read (all time)',
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
'Flagged Posts (last ' || s.time_period || ' days)',
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
'Users Who Flagged (last ' || s.time_period || ' days)',
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
'Likes Given (last ' || s.time_period || ' days)',
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
'Likes Received (last ' || s.time_period || ' days)',
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
'Likes Received: unique days (last ' || s.time_period || ' days)',
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
'Likes Received: unique users (last ' || s.time_period || ' days)',
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
'Silenced (last 6 months)',
0,
p.silenced,
CASE WHEN p.silenced = 0 THEN '✔' ELSE '✘' END
FROM penalties p
UNION ALL
SELECT
'Suspended (last 6 months)',
0,
p.suspended,
CASE WHEN p.suspended = 0 THEN '✔' ELSE '✘' END
FROM penalties p
) x
Esempio di risultato:
