sql_query.txt

Answer 1
SELECT COUNT(conversation_id) AS no_of_msg, DATE_TRUNC('day',sent_time) AS day FROM chat_messages WHERE sent_time BETWEEN 2021-01-01 00:00:00.000000 AND 2021-12-31 23:59:59.999999 GROUP BY day,conversation_id
Answer 2
WITH msg_num AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY DATE_TRUNC('day',sent_time) DESC) AS rn FROM chat_messages) SELECT * FROM msg_num WHERE rn = 2 AND sent_time >=2021-01-01 00:00:00.000000 AND sent_time <=2021-12-31 23:59:59.999999
Answer 3
-