Je ne pense pas que les entrées que vous avez listées correspondent à la sortie que vous espérez obtenir. Si vous souhaitez obtenir des données pour un seul utilisateur, une requête comme celle ci-dessous pourrait servir de point de départ. Elle utilise des expressions de table commune (CTE) pour rendre la requête plus facile à lire et à écrire. Le même modèle pourrait être suivi pour ajouter plus de données aux résultats.
Une chose dont je n’étais pas certain est de savoir si vous voulez la somme des données pour une journée donnée, ou le nombre d’actions qui ont eu lieu chaque jour. Par exemple, voulez-vous savoir que le 2019-10-31, un utilisateur a créé un total de 20 messages sur tous les jours où il a été sur le site, ou voulez-vous savoir que le 2019-10-31, l’utilisateur a créé exactement 3 messages ? La façon dont la requête est actuellement structurée, elle renvoie ce dernier résultat.
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
ORDER BY d.day
Pour augmenter l’activité des utilisateurs, nous prévoyons d’organiser un concours visant à identifier l’utilisateur le plus actif de notre forum, qui sera récompensé.
C’est pourquoi nous souhaitons obtenir des données supplémentaires afin de rendre ce concours encore plus intéressant.
Les données dont nous disposons, grâce à la requête de @simon, sont : les sujets, les publications, le nombre de mots et les likes reçus.
Voici quelques données supplémentaires :
Likes donnés : nombre de likes attribués
Visites : nombre de fois où l’utilisateur a ouvert le forum
Temps de lecture : durée pendant laquelle l’utilisateur est resté sur le forum
Abonnements et abonnés
Vues des sujets : nombre de vues que l’utilisateur obtient pour l’ensemble des sujets qu’il a créés
Réponses aux sujets : nombre de réponses (publications) que l’utilisateur reçoit pour l’ensemble des sujets qu’il a créés
Nous vous prions de nous aider à obtenir ces données supplémentaires.
Pour le pondération de chaque élément, nous prévoyons d’utiliser Excel. Avez-vous des conseils sur la manière de pondérer chaque élément afin de désigner le gagnant ?
Nous souhaitons rendre ce concours sérieux mais amusant. Les meilleurs utilisateurs sont ceux qui combinent une grande activité, une grande utilité et une grande popularité.
Encore une fois, merci beaucoup pour votre aide.
Note : Peut-on obtenir les données par groupes, afin de ne devoir entrer que l’identifiant du groupe ? Notre intention est de regrouper tous les participants dans un même groupe. Pour l’instant, nous devons entrer manuellement l’identifiant utilisateur de chaque participant.
Je suis aussi Mancunien(e)
Mon entreprise envisage de lancer un concours similaire en interne pour stimuler l’engagement de notre communauté auprès des employés.
Avez-vous réussi à organiser ce concours ? Comment avez-vous fini par extraire les données ? Et comment avez-vous pondéré les différentes métriques d’engagement au final ?
