How do I calculate the running sum of active days for this query?

Avrahad 0
Viewed 40

I have a table with a column for "JobID", "EmployeeID", and a column called "Date" for each day that an employee worked on the job for example:

JobID EmployeeID Date
abc 1 2010-01-01
abc 2 2010-01-01
xyz 1 2010-01-01
xyz 1 2011-31-12
def 3 2011-31-12

So one job can be worked on through multiple days, can have multiple employees working on it on the same day, and one employee can work multiple jobs on the same day.

I am trying to calculate the running sum of total days the jobs were worked on (and I wanted to group it by year) so for the example above the final output should be:

Year DaysWorked CumulativeDays
2010 2 2
2011 2 4

Basically, job 'abc' was worked 1 day (two employees but still only one day), 'xyz' was worked 2 days, and 'def' was worked 1 day, so the total count by 2011 should be 4 days.

I wrote this query but it does not work, I'm not sure what I need to modify.

WITH t1 as (
 SELECT
  extract(year from Date) AS Year, JobId, count(distinct Date) AS DaysWorked
 FROM Data
 GROUP BY 1, 2
)
SELECT Year, DaysWorked, sum(DaysWorked) over (order by Year) AS CumulativeDays
FROM t1

The output I get is:

Year DaysWorked CumulativeDays
2010 2 4
2011 2 4
google-bigquery sql
1 Answers

Here's a solution in Sql Server. They have window functions in BigQuery so it should work the same.

select distinct year(date)                                          as 'Year'
      ,sum(days_worked) over(partition by year(date) order by date) as DaysWorked
      ,sum(days_worked) over(order by year(date))                   as CumulativeDays

from   (
       select *
             ,case Date when lag(Date) over(partition by JobID, year(date) order by date) then null else 1 end as days_worked
       from   t
        ) t
Year DaysWorked CumulativeDays
2010 2 2
2011 2 4

Fiddle

DannySlor0
Related