unresponsive.sql

SELECT a.id, u.name, a.last_received_date, u.mobile_number,MAX(c.created_at) AS last_replied_date, COUNT(DISTINCT p.id) 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(actor_id)) AS unique_buyers
    FROM shopo_chats.chats
    WHERE
    to_user=target_id
    AND
    created_at BETWEEN '#{i_from}' AND '#{i_to}'
    GROUP BY 1) a
    ON
    a.id=c.from_user
    JOIN
    accounts.users u
    ON
    u.id=a.id
    JOIN treasureProduct.products p
    ON
    p.user_id=a.id
    WHERE p.status='active'
    GROUP BY  1,2,3,4
    HAVING MAX(c.created_at) < TIMESTAMP(CURDATE())
    OR MAX(c.created_at) IS NULL
    ORDER BY last_replied_date #{o};