Counting current and longest streaks of a given value

Question

I have 2 tables Person (ID, NAME, CLAN_ID) and DailyScore (PERSON_ID, CLAN_ID, DAY_NUMBER, SCORE).

SCORE can take the values "A", "B", "C" or "-" ("-" means absent).

I need to make 2 separate queries to get, for a given CLAN_ID:

• the current streak of a given score (let's say A, e.g.) for each Person and the name of the Person, ordered by streak length DESC
• the longest ever streak of a given score (let's say A, e.g.) for each Person and the name of the Person, ordered by streak length DESC

An important constraint is that "-" SCORES are ignored, as they represent absences, not real Scores.

Example data:

Table Person:

_ID  NAME    CLAN_ID
1    John    11
2    Alice   11
3    Bob     12
4    Sara    12

Table DailyScore:

PERSON_ID   CLAN_ID   DAY_NUMBER   SCORE
1           11        1            A
1           11        2            A
1           11        3            A
1           11        4            C
1           11        5            A
2           11        1            B
2           11        2            C
2           11        3            B
2           11        4            A
2           11        5            A
3           12        1            A
3           12        2            A
3           12        3            A
3           12        4            A
3           12        5            B
4           12        1            C
4           12        2            B
4           12        3            C
4           12        4            A
4           12        5            -

Desired result example 1 (CLAN_ID=11, SCORE=A):

Current streak:

Alice   2
John    1

Longest ever streak:

John    3
Alice   2

Desired result example 2 (CLAN_ID=12, SCORE=A):

Current streak:

Sara    1*
Bob     0

*since "-" are ignored, Sara has a current streak of 1 A score

Longest ever streak:

Bob     4
Sara    1

Edit:

In case it helps, here's this example in SQL Fiddle: http://sqlfiddle.com/#!7/2ed69/2

Answer 1

The first query can be:

select
  id, name, max(s) as streak
from (
  select
    p.id,
    p.name,
    count(*) over(partition by p.id order by s.day_number desc) as c,
    sum(case when s.score = 'A' then 1 else 0 end)
      over(partition by p.id order by s.day_number desc) as s
  from person p
  join dailyscore s on s.person_id = p.id
) x
where c = s
group by id, name