CODEWITHSUNDEEP
sqlpostgresqlgroup-bywindow-functionspostgresql-13
Morteza Malvandi
Morteza Malvandi

Reputation: 1724

How select a row and count row in single query in postgresql?

I have a table in postgresql as follow:

 id | chat_id |  content  | time |                 read_times                 
----+---------+-----------+------+-------------------------------------------------------------------------
  1 | chat_10 | content_1 |  t1  | [{"username": "user1", "time": 123}, {"username": "user2", "time": 111}]
  2 | chat_10 | content_2 |  t2  | [{"username": "user2", "time": 1235}]
  3 | chat_10 | content_3 |  t3  | []
  4 | chat_11 | content_4 |  t4  | [{"username": "user1", "time": 125}, {"username": "user3", "time": 121}]
  5 | chat_11 | content_5 |  t5  | [{"username": "user1", "time": 126}, {"username": "user3", "time": 127}]

Note: t1 < t2 < t3 < t4 < t5

After every user read a message, we registered it in read_times column(user2 read a message with id 2 at time 1235), Now I want to get user chat list with unread count chats. for user1 the result is as follow:

 chat_id |  content  |  unread_count
 --------+-----------+--------------
 chat_10 | content_3 |       2
 chat_11 | content_5 |       0

Note: unread_count is messages count that user didn't read in a caht_id.

Is it possible with one query?

Upvotes: 1

Views: 1161

Answers (1)

forpas
forpas

Reputation: 164069

First, you must extract the user names for each chat_id and content with json_array_elements() function and with FIRST_VALUE() window function get the last content of each chat_id.
Then aggregate and combine SUM() window function with MAX() aggregate function to get the column unread_count:

WITH cte AS (
  SELECT t.chat_id, t.content,
         FIRST_VALUE(t.content) OVER (PARTITION BY t.chat_id ORDER BY t.time DESC) last_content,
         (r->>'username') username
  FROM tablename t LEFT JOIN json_array_elements(read_times::json) r ON true
)
SELECT DISTINCT c.chat_id, MAX(c.last_content) "content",
       SUM((MAX((COALESCE(username, '') = 'user1')::int) = 0)::int) OVER (PARTITION BY c.chat_id) unread_count
FROM cte c
GROUP BY c.chat_id, c.content
ORDER BY c.chat_id

See the demo.

Upvotes: 1

Related Questions