Trouver les utilisateurs qui sont plus susceptibles de devenir TL3

Données et rapports
,
17

Ceci est légèrement plus efficace. Si cela ne fonctionne toujours pas pour vous, vous pouvez essayer de supprimer certaines colonnes, avec leurs jointures et requêtes associées.

EDIT Bon, j’ai enfin compris mes types de jointures (ça fait un moment). Cette requête mise à jour est beaucoup plus efficace.

with
t as (
  select
    CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
  ),

-- Nombre de sujets des 100 derniers jours 25%
-- le plus petit de 25% des sujets créés ces 100 derniers jours
-- OU 500, le nombre maximum par défaut du système requis pour TL3
tclhd AS (
    SELECT LEAST(floor(count(id)*.25)::REAL,500) as all_topics
    FROM topics, t
    WHERE created_at > t.start
        AND archetype = 'regular'
        AND deleted_at is null
    ),

-- Nombre de messages des 100 derniers jours 25%
-- le plus petit de 25% des messages créés ces 100 derniers jours
-- OU 20k, le nombre maximum par défaut du système requis pour TL3
pclhd AS (
SELECT LEAST(FLOOR(count(id)*.25)::REAL,20000) AS all_posts
FROM t, posts
WHERE posts.created_at > start
    AND posts.deleted_at is null
    AND posts.hidden_at is null
    AND posts.last_editor_id >0  -- Omettre Discobot et le Système
    AND (action_code is null OR action_code != 'assigned')
    ),

-- Utilisateurs de niveau de confiance 2
tl AS (
SELECT id as user_id, trust_level
FROM users
WHERE trust_level = 2
    ),

-- Utilisateurs, visites et messages lus ces 100 derniers jours
pr AS (
    SELECT user_id,
        count(1) as visits,
        sum(posts_read) as posts_read
    FROM t, user_visits
    INNER JOIN tl using (user_id)
    WHERE visited_at > t.start
      AND visited_at < t.end
    GROUP BY user_id
    ORDER BY visits DESC
    ),

-- Messages lus tout le temps
prat as (
    select user_id,
        sum(posts_read) as posts_read
    from t, user_visits
    INNER JOIN tl using (user_id)
    group by user_id
    ),

-- Sujets auxquels il a été répondu
trt as (
    select user_id,
           count(distinct topic_id) as topic_id
    from t, posts
    INNER JOIN tl using (user_id)
    where posts.created_at > t.start
      and posts.created_at < t.end
    group by user_id
    ),

-- Sujets consultés tout le temps
tvat as (
    select tv.user_id,
        COUNT(distinct tv.topic_id) AS topic_id
    FROM topic_views tv
    LEFT JOIN topics t on tv.topic_id=t.id
    INNER JOIN tl on tv.user_id=tl.user_id
    WHERE
        t.archetype = 'regular'
        AND t.deleted_at is null
    group by tv.user_id
    ),

-- Sujets consultés
tva AS (
SELECT tv.user_id,
    COUNT(distinct tv.topic_id) AS topic_id
FROM t, topic_views tv
    LEFT JOIN topics on topic_id=topics.id
    INNER JOIN tl on tv.user_id=tl.user_id
    WHERE
        topics.archetype = 'regular'
        AND topics.deleted_at is null
        AND viewed_at > t.start
        AND viewed_at < t.end
GROUP BY tv.user_id
    ),

likes AS (
    SELECT user_id,
        likes_given, likes_received
    from user_stats
    INNER JOIN tl using (user_id)
)


SELECT  pr.user_id,
        greatest(50-coalesce(pr.visits,0),0) as "Écart de visites",
        greatest(10-coalesce(trt.topic_id,0), 0)  as "Écart de réponses aux sujets",
        greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Écart de sujets consultés",
        greatest(200-coalesce(tvat.topic_id,0),0) as "Écart de sujets consultés (AT)",
        greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Écart de messages lus",
        greatest(500-coalesce(prat.posts_read,0),0) as "Écart de messages lus (AT)",
        greatest(30-likes.likes_given,0) as "Écart de likes donnés",
        greatest(20-likes.likes_received,0) as "Écart de likes reçus"

FROM pclhd, tclhd, pr
left join trt using (user_id)
LEFT JOIN tva USING (user_id)
left join tvat using (user_id)
left join prat using (user_id)
LEFT JOIN likes using (user_id)


ORDER BY
  pr.visits DESC

LIMIT 50
8 « J'aime »