How can I find the longest number of consecutive nulls in a column in MySQL?

Question

Supposing I have a table consisting of something like:

    date   | A | B |   C  |
02/01/2020 | 3 | 5 | null |
03/01/2020 | 8 | 3 | null |
04/01/2020 | 3 | 4 | null |
05/01/2020 | 5 | 9 |   3  |
06/01/2020 | 3 | 3 | null |
07/01/2020 | 1 | 2 | null |
08/01/2020 | 5 | 9 |   5  |

How can I write a query to return the largest number of consecutive nulls in column C? In this case, the answer should be 3.

I've tried searching online and asking LLMs but I'm yet to find an answer that aids me.

Kind regards and thank you for your help.

I've tried queries like:

SELECT 
    `C`, 
    COUNT(*) AS table_name
FROM 
    (SELECT 
        `C`, 
        IFNULL(`C`, 0) AS points
    FROM 
        table_name) AS subquery
GROUP BY 
    `C`
HAVING 
    COUNT(*) > 1;

But this isn't looking at consecutive nulls, it's just counting the groups of different values for the columns in general. I'm not sure where to even begin at this point.

Answer 1

Use COUNT OVER to get a running count of values in C and use this as a group key. Then group by this key, count nulls and take the result row with the maximum null count.

Illustration of the groups:

DT	A	B	C	GRP	explanation
2020-01-02	3	5	null	0	no value so far
2020-01-03	8	3	null	0	no value so far
2020-01-04	3	4	null	0	no value so far
2020-01-05	5	9	3	1	3 is the first C value
2020-01-06	3	3	null	1	still only the value 3
2020-01-07	1	2	null	1	still only the value 3
2020-01-07	5	9	5	2	two values now, 3 and 5

The complete query:

select
  count(*) - count(c) as consecutive_nulls
from
(
  select 
    t.*,
    count(c) over (order by dt) as grp
  from mytable t
)
group by grp
order by consecutive_nulls desc
limit 1;