Consecutive Occurrences of Values

user_15141174 0
Viewed 102

I have to conduct a count of CONSECUTIVE occurrences by date of student absences.

Within two categories: 2 consecutive fouls/absences and 3 or more consecutive fouls.

Data example:

Name Date Present
Student 1 2022/01/01 false
Student 1 2022/01/02 false
Student 1 2022/01/03 true
Student 1 2022/01/04 false
Student 1 2022/01/05 false
Student 1 2022/01/06 false
Student 1 2022/01/07 true
Student 1 2022/01/08 false
Student 1 2022/01/09 false
Student 1 2022/01/10 false
Student 1 2022/01/11 false
Student 1 2022/01/12 true
Student 1 2022/01/13 false
Student 1 2022/01/14 false
Student 1 2022/01/15 true

Expected outcome:

Students Count 2 Consecutive Absences Count 3 consecutives or more Total of Absences
Student 1 2 2 11

I tried to conduct this code using LAG and OVER, but I wasn't successful.

CASE WHEN LAG(present) OVER (order by date) is false AND present is false THEN 1 END as test
google-bigquery
2 Answers

Consider below approach

select name, 
  countif(absences = 2) as Count_2_Consecutive_Absences,
  countif(absences > 2) as Count_3_or_more_Consecutive_Absences,
  sum(absences) as Total_Absences,
from (
  select name, countif(not present) absences
  from (
    select *, countif(new_grp) over(partition by name order by date) grp
    from (
      select *, ifnull(present != lag(present) over(partition by name order by date), true) new_grp
      from your_table
    )
  )
  group by name, grp
  having absences > 0
)
group by name

if applied to sample data in your question - output is

enter image description here

user_52219440

This is a gaps and islands problem, and once approach uses the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) rn1,
              ROW_NUMBER() OVER (PARTITION BY Name, Present ORDER BY Date) rn2
    FROM yourTable
),
cte2 AS (
    SELECT Name,
           COUNT(CASE WHEN Present = false THEN 1 END) AS num_consec_absent
    FROM cte
    GROUP BY Name, rn1 - rn2
)

SELECT Name,
       COUNT(CASE WHEN num_consec_absent = 2
                  THEN 1 END) AS Count_2_Consecutive_Absences,
       COUNT(CASE WHEN num_consec_absent > 2
                  THEN 1 END) AS Count_3_or_more_Consecutive_Absences,
       SUM(num_consec_absent) AS Total_Absences
FROM cte2
GROUP BY Name;

Here is a running demo for SQL Server.

user_18632290
Related