CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8995

Adding a feature to an already complex query

I have the following table which logs chat messages

CREATE TABLE public.message_log
(
  id integer NOT NULL DEFAULT nextval('message_log_id_seq'::regclass),
  message text,
  from_id character varying(500),
  to_id character varying(500),
  match_id character varying(500),
  unix_timestamp bigint,
  own_account boolean,
  reply_batch boolean DEFAULT false,
  insert_time timestamp with time zone DEFAULT now(),
  CONSTRAINT message_log_pkey PRIMARY KEY (id),
  CONSTRAINT message_log_message_from_id_to_id_match_id_unix_timestamp_key UNIQUE (message, from_id, to_id, match_id, unix_timestamp)
)

A chat conversation has the same match_id

I have the following query which returns a list of match_ids which the last message related to the match_id (the last message of the chat conversation) is from the non account holder (own_account = false) the query is working fine

select m.* from message_log m where m.from_id <> ? and m.to_id = ? and m.unix_timestamp = 
( 
select max(unix_timestamp) from message_log where match_id = m.match_id group by match_id 
)

I want to modify the query above that it would count the chat conversations that have had been replied to twice or more (I think we would need to use the reply_batch column). I can not get my mind around it. Any help would be appreciated.

Upvotes: 0

Views: 37

Answers (1)

Ing. Gerardo S&#225;nchez
Ing. Gerardo S&#225;nchez

Reputation: 1667

SELECT match_id, replies_count FROM (SELECT match_id, COUNT(*) AS replies_count FROM message_log
WHERE from_id <> ? and to_id = ? GROUP BY match_id) AS replies_counter WHERE replies_count > 1

Upvotes: 0

Related Questions