I don’t think the inputs you have listed match the output you are hoping to get. If you would like to get data for a single user, something like the query below might work as a starting point. It’s using Common Table Expressions to make the query easier to read and write. The same pattern could be followed to add more data to the results.
One thing I wasn’t sure of is if you want the sum of the data for a given day, or the number of actions that took place on each day. For example, do you want to know that on 2019-10-31 a user has created a total of 20 posts in all the days they have been on the site, or do you want to know that on 2019-10-31 the user created exactly 3 posts? The way the query is currently structured, it’s returning the latter result.
--[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
To increase user activity, we plan to hold a challenge, who is the most active user in our forum, to be rewarded.
Therefore, we wanted to get some additional data, to make the challenge even more interesting.
The data we have, using the query from @simon is Topics, Posts, Words Count and Likes Receive
Some additional data include:
Likes Given: How many likes were given
Visit: How many times the user opens the forum
Read Time: How long the user is in the forum
Following and Followers
Topics View: How many topic views the user gets for all the topics he created…
Topics replies: How many replies (posts) the user gets for all the topics he created…
Please help us, so that we can get these additional data.
For the weighting of each item, we plan to use excel. Is there any advice on how to weight each item to choose the winner?
We want to make this challenge serious but fun. The best users are the combination of most active, most helpful, and most popular.
Once again, thank you very much for your help.
Note: Can we get the data in groups, so we only enter the group id. Our plan is that we will put all participants into a group. This time, we have to enter the participant’s user id one by one
