Get the maximum consecutive count of a name in PostgreSQL

Question

I was asked this question in a job interview:

There is a table with vehicle names mentioned in a column. Output when we check for name=car we must get as 4 i.e the maximum count of continuous occurrence of 'car' in the column.

Can somebody help me get this solution in PostgreSQL?

I tried using rank and dense rank but it didn't work.

Answer 1

Disclaimer: As already stated by @Mureinik in the comments: This can only work if there is a dedicated column for ordering (e.g. an increasing ID or a timestamp). The reason is, that in the database the column order is not determined. So you always need a sort order criterion if you'd deal with specific orders. In the example below I added an id column.

---

step-by-step demo: db<>fiddle

SELECT
    name,
    COUNT(*) OVER (PARTITION BY group_id)                        -- 2
FROM (
    SELECT                                                       -- 1
        *,
        SUM(eq) OVER (ORDER BY id) as group_id
    FROM (
        SELECT
            *,
            (name != lag(name) OVER (ORDER BY id))::int as eq
        FROM mytable
    ) s
) s
ORDER BY count DESC                                              -- 3
LIMIT 1                                                          -- 4

1. First part is similar to what I already described here, for example. You can find this in the subqueries. It is about window function partitioning while keeping the original sort order. It creates unique group ids for each specific name group.
2. Using COUNT() window function to count the records of each group.
3. Afterwards order the biggest count first.
4. Return only the first record (which contains the highest count number)