Get the number of comments that are at least one hour apart

user_15046296 0
Viewed 50

I want to get the number of comments that are at least one hour apart. I wrote this query and it works fine.

SELECT COUNT(`c`.`id`) `count`
FROM `comments` `c`
WHERE `user_id` = '28' AND (TIMESTAMPDIFF(SECOND, (
    SELECT MAX(`created_at`)
    FROM `comments`
    WHERE `user_id` = '28' AND `created_at` < `c`.`created_at`
), `c`.`created_at`) > 3600 OR TIMESTAMPDIFF(SECOND, (
    SELECT MAX(`created_at`)
    FROM `comments`
    WHERE `user_id` = '28' AND `created_at` < `c`.`created_at`
), `c`.`created_at`) IS NULL)

But as you can see, this statement is repeated two times:

    SELECT MAX(`created_at`)
    FROM `comments`
    WHERE `user_id` = '28' AND `created_at` < `c`.`created_at`

How can I write it cleaner?

SQL Fiddle

mysql sql
1 Answers

You use the subquery again just to compare it to NULL.
You can avoid it by applying COALESCE() the 1st time you use it which will return a number greater than 3600 in case it returns NULL, so that it can pass the comparison:

SELECT COUNT(c.id) count
FROM comments c
WHERE c.user_id = '28' 
  AND COALESCE(
        TIMESTAMPDIFF(
          SECOND, 
          (
            SELECT MAX(cc.created_at)
            FROM comments cc
            WHERE cc.user_id = c.user_id AND cc.created_at < c.created_at
          ), 
          c.created_at
        ), 
        3601
      ) > 3600

If your version of MySql is 8.0+ you could use LAG() window function instead of the subquery:

WITH cte AS (
  SELECT *, 
         TIMESTAMPDIFF(
          SECOND,
          LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at),
          created_at
        ) diff
  FROM comments
)
SELECT COUNT(id) count
FROM cte
WHERE user_id = '28' AND (diff IS NULL OR diff > 3600)

See the demo.

user_104988280
Related