Checking for maximum length of consecutive days which satisfy specific condition

mysql, sql

Solution

This solution seems to perform quite well as long as there is a composite index on users_id and beverages_id -

SELECT *
FROM (
    SELECT t.*, IF(@prev + INTERVAL 1 DAY = t.d, @c := @c + 1, @c := 1) AS streak, @prev := t.d
    FROM (
        SELECT DATE(timestamp) AS d, COUNT(*) AS n
        FROM beverages_log
        WHERE users_id = 1
        AND beverages_id = 1
        GROUP BY DATE(timestamp)
        HAVING COUNT(*) >= 5
    ) AS t
    INNER JOIN (SELECT @prev := NULL, @c := 1) AS vars
) AS t
ORDER BY streak DESC LIMIT 1;

Problem

I have a MySQL table with the structure: beverages_log(id, users_id, beverages_id, timestamp) I'm trying to compute the maximum streak of consecutive days during which a user (with id 1) logs a beverage (with id 1) at least 5 times each day. I'm pretty sure that this can be done using views as follows: ``` CREATE or REPLACE VIEW daycounts AS SELECT count(*) AS n, DATE(timestamp) AS d FROM beverages_log WHERE users_id = '1' AND beverages_id = 1 GROUP BY d; CREATE or REPLACE VIEW t AS SELECT * FROM daycounts WHERE n >= 5; SELECT MAX(streak) AS current FROM ( SELECT DATEDIFF(MIN(c.d), a.d)+1 AS streak FROM t AS a LEFT JOIN t AS b ON a.d = ADDDATE(b.d,1) LEFT JOIN t AS c ON a.d <= c.d LEFT JOIN t AS d ON c.d = ADDDATE(d.d,-1) WHERE b.d IS NULL AND c.d IS NOT NULL AND d.d IS NULL GROUP BY a.d) allstreaks; ``` However, repeatedly creating views for different users every time I run this check seems pretty inefficient. Is there a way in MySQL to perform this computation in a single query, without creating views or repeatedly calling the same subqueries a bunch of times?

Original source