Is it possible to collect such a request?

Question

MySQL

Hello!
Can tell you how to make a sql query so that you can get the following (2nd block "Records"):


I.e. there is a table with fields:
id, done(boolean), date(YYYY-MM-DD)

I want to create a query that can give something like this:
date_start, count, date_end
limit 10
Well, how is everything in the "Records" block

Is this possible ?
Thank you!

Answer 1

SELECT pairs.startDate, pairs.endDate, pairs.endDate - pairs.startDate + 1 as count FROM
(SELECT t1.date as startDate, MIN(t3.date) as endDate FROM statistics as t1
   LEFT JOIN statistics t2 ON t1.date - t2.date = 1 and t2.done = 1
   LEFT JOIN statistics as t3 ON t1.date <= t3.date and t3.done = 1
     LEFT JOIN statistics as t4 ON t3.date - t4.date = -1 and t4.done = 1
WHERE t1.done = 1 AND ISNULL(t2.date) AND ISNULL(t4.date)
GROUP BY t1.date) as pairs
ORDER BY pairs.startDate

Explanation:
1) Find all records t1 that have no records with the previous day t2
2) Join with records t3 that have no records with the next day t4 and whose date is greater than t1
3) Calculate for records from t1 the minimum date from t3

Answer 2

Judging by https://www.sqlitetutorial.net/sqlite-window-funct... sqlite already knows how to lag/lead - then everything should turn out quite elegantly.