a.sql

SELECT a.id, u.name, a.last_received_date, u.mobile_number, MAX(c.created_at) AS last_replied_date, p.products_count AS p_count, unique_buyers
      FROM shopo_chats.chats c RIGHT JOIN
      (SELECT
      to_user AS id,
      MAX(created_at) AS last_received_date,
      COUNT(distinct(buyer_id)) AS unique_buyers
      FROM shopo_chats.chats
      WHERE
      to_user=seller_id
      AND
      created_at BETWEEN '#{i_from}' AND '#{i_to}'
      GROUP BY 1) a
      ON
      a.id=c.from_user
      INNER JOIN
      accounts.users u
      ON
      u.id=a.id
JOIN
(select COUNT(id) as products_count,
user_id
from
treasureProduct.products 
where status="ACTIVE"
GROUP BY user_id
) p ON
p.user_id = a.id
      GROUP BY 1,2,3,4
      HAVING MAX(c.created_at) < TIMESTAMP(CURDATE())
ORDER BY last_replied_date #{o};