CODEWITHSUNDEEP
sqldatabasepostgresql
Dapo Michaels
Dapo Michaels

Reputation: 392

How to loop through a cte in main query

I am trying to rank users on my system based on the user's totalArticleViews and the user's totalArticles on my system. The ranking should be based on the formula (totalArticleViews + ( totalArticles * 500 )) / 100

I have a system that allows users to post articles, a record is created every time any of these articles are read by anyone. My database has the following tables. users, articles, reads.

I have tried to get the views to insert into the formula, but i'm having issues getting all the users articles and multiplying it by 500 to insert into the formula to rank them all 

with article_views AS (
 SELECT article_id, COUNT(reads.id) AS views, 1 * 500 AS points
 FROM reads 
 WHERE article_id IN (     
 SELECT id FROM articles WHERE articles.published_on IS NOT NULL AND 
  articles.deleted_at IS NULL
 ) 
GROUP BY article_id
),
published AS (
SELECT COUNT(articles.id) AS TotalArticle, COUNT(articles.id) * 500 AS 
points
FROM articles 
WHERE published_on IS NOT NULL AND deleted_at IS NULL
GROUP BY articles.user_id
)

SELECT 
 users.id AS user_id, 
 ROUND((SUM(article_views.views) + () ) / 100.0, 2)  AS points,
 ROW_NUMBER() OVER (ORDER BY ROUND((SUM(article_views.views) + ()) / 
100.0, 2) DESC)
FROM users 
 LEFT JOIN articles ON users.id = articles.user_id
 LEFT JOIN reads ON articles.id = reads.article_id
 LEFT JOIN article_views ON reads.article_id = article_views.article_id
WHERE 
 users.id IN (SELECT user_id FROM role_user WHERE role_id = 2)
 AND status = 'ACTIVE'
GROUP BY users.id
ORDER BY points DESC NULLS LAST

I'm stuck at this point

(SUM(article_views.views) + () ) / 100.0, 2)

Upvotes: 1

Views: 569

Answers (1)

Parfait
Parfait

Reputation: 107697

Simply use the published CTE by including the GROUP BY column user_id in SELECT and then joining published to users by this field in main level query.

WITH article_views AS (
    SELECT r.article_id, 
           COUNT(r.id) AS views, 
           1 * 500 AS points
    FROM reads r
    WHERE r.article_id IN (     
         SELECT id 
         FROM articles a 
         WHERE a.published_on IS NOT NULL
           AND a.deleted_at IS NULL
    ) 
    GROUP BY r.article_id
),
published AS (
   SELECT a.user_id,
          COUNT(a.id) AS TotalArticle, 
          COUNT(a.id) * 500 AS points
   FROM articles a
   WHERE a.published_on IS NOT NULL 
     AND a.deleted_at IS NULL
   GROUP BY a.user_id
)

SELECT u.id AS user_id, 
       ROUND((SUM(av.views) + (p.TotalArticle)) / 100.0, 2)  AS points,
       ROW_NUMBER() OVER (ORDER BY ROUND((SUM(av.views) + (p.points)) 
                                          / 100.0, 2) DESC) AS rn
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
LEFT JOIN reads r ON a.id = r.article_id
LEFT JOIN article_views av ON r.article_id = av.article_id
LEFT JOIN published p ON u.id = p.user_id
WHERE u.id IN (
     SELECT user_id FROM role_user WHERE role_id = 2
)
AND u.status = 'ACTIVE'
GROUP BY u.id
ORDER BY points DESC NULLS LAST

Upvotes: 2

Related Questions