How do i user windowed function with count(case when...)

Question

world!

I have a first "level" table, which look loke this:

level	id	level_date
1	A	2021-12-02
2	A	2021-12-04
3	A	2021-12-08
1	B	2021-12-02
2	B	2021-12-05
3	B	2021-12-09

and a second "battles" table:

id	battle_date
A	2021-12-01
A	2021-12-03
A	2021-12-06
A	2021-12-07
B	2021-12-01
B	2021-12-02
B	2021-12-03

What I am trying to do is to find average a battle count, required to get to each level.

When battle_date > level_X-1_date, but battle_date < level_X_date that means that this battle is required to get to level X, and should be counted for level X.

So for player A we have one battle to get to level 1, one battle to get to level 2, and two battles to get to level 3. And for player B we have one battle to get to level 1, two battles to get to level 2, and zero battles to get to level 3

The resulting table should look like this:

level	avg_battle_count
1	1
2	1.5
3	1

I'm pretty sure this is kind of "gaps and islands" problem, but I don't know how exactly should i build a query which consider windowed function to count avg(battle_count) for levels

Answer 1

I did not tried it by I think this should yield the result you are looking for:

select
  level,
  avg(battle_count) avg_battle_count
from (
  select
    x.level,
    x.id,
    count(*) battle_count
  from level x
  left join level x_1 on 
    x.id = x_1.id and 
    x.level = x_1.level-1
  join battles b on 
    x.id = b.id and 
    b.battle_date < x.level_date and 
    (b.battle_date > x_1.level_date or x_1.level_date is null)
  group by
    x.level,
    x.id
)
group by level
order by level

Answer 2

Consider below approach (BigQuery)

select level, avg(battle_count) as avg_battle_count from (
  select level, id, battle_count - ifnull(lag(battle_count) over(partition by id order by level), 0) as battle_count
  from (
    select level, t1.id, count(*) battle_count
    from levels t1 left join battles t2
    on t1.id = t2.id and battle_date < level_date 
    group by level, id
  )
)
group by level               

if applied to sample data in your question

with levels as (
  select 1 level, 'A' id, '2021-12-02' level_date union all
  select 2, 'A', '2021-12-04' union all
  select 3, 'A', '2021-12-08' union all
  select 1, 'B', '2021-12-02' union all
  select 2, 'B', '2021-12-05' union all
  select 3, 'B', '2021-12-09' 
), battles as (
  select 'A' id, '2021-12-01' battle_date union all
  select 'A', '2021-12-03' union all
  select 'A', '2021-12-06' union all
  select 'A', '2021-12-07' union all
  select 'B', '2021-12-01' union all
  select 'B', '2021-12-02' union all
  select 'B', '2021-12-03' 
)

output is