PostgreSQL Question posted in
The official documentation can be found here.

Get records based on count greater than 2 in postgresql

I have a table named user_activity.

user_id book_id event_type_id
1 1 1
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
1 3 1

event_type_id represents "User has viewed the book".

I want to write a Postgresql query to fetch book ids viewed more than twice by the user as below –

user id viewed book ids
1 1,2
Tags:

2

Answers


  1. 0

    You could try the following query:

    SELECT user_id, STRING_AGG(book_id::text, ',') AS viewed_book_ids
FROM user_activity
WHERE event_type_id = 1
GROUP BY user_id, book_id
HAVING COUNT(*) > 2

    First you select all the rows where the "User has viewed the book" and then you group them by user_id using the group function STRING_AGG() and filter the "groups" that have more than two rows.
    That should do it.

    Login or Signup to reply.
  2. 0

    You have to group by twice – by user_id, book_id to extract the user-book pairs that qualify (the internal subquery) and then by user_id over the result.

    select user_id,
  string_agg(book_id::text, ',') viewed_book_ids
from (
 select user_id, book_id
 from user_activity
 where event_type_id = 1
 group by user_id, book_id
 having count(*) > 2
) t
group by user_id;

    DB-fiddle
    Unrelated, but what is viewed_book_ids going to be used for? Maybe it will be more useful as an array (use array_agg instead of string_agg) or a JSONB array (use jsonb_agg) rather than a comma-separated list text.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search