CODEWITHSUNDEEP
sqldb2
dutchess166
dutchess166

Reputation: 1

Count the amount of times a value appears in 28 consecutive days

I am new to this community and I think I need your help. I have a query that shows all players that have made at least 5 goals in 28 consecutive days in 2019. Now I also need to know how many goals they actually made, if they have made at least 5 goals.

You can see the query I already have below. 

select player_id from
(
  select    a.player_id, 
          min(days(a.date)-days(b.date)) as time_period
  from
  (
    select  *
    from
    (
      select    player_id, 
              date, 
              row_number() over (partition by player_id order by date asc) as goals 
      from  matches m
      where             date>date('01.01.2019')
      and       player_id<>''
    )
    where goals >=5
  ) a
  join
  (
    select  player_id, 
            date, 
            row_number() over (partition by player_id order by date asc) as goals
    from    matches m
    where       date>date('01.01.2019')
    and player_id<>'' 
  ) b
  on        a.player_id=b.player_id
  and       a.goals=(b.goals+4)
  group by  a.player_id
) Z
where time_period<=28

The above query provides me all players that have made 5 or more goals in 28 consecutive days, but does not show how many goals they actually have made during this period.

Upvotes: 0

Views: 87

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Consider the following example:

select *
from 
(
  select 
    d, player_id
  , count(1) over (partition by player_id order by days(d) range between 28 preceding and current row) as goals
  from
  (
  values
    (date('2019-01-01'), 1)
  , (date('2019-01-28'), 1)
  , (date('2019-02-01'), 1)
  , (date('2019-02-02'), 1)
  ) t(d, player_id)
)
--where goals>=2
;

D          PLAYER_ID GOALS
---------- --------- -----
2019-01-01         1     1
2019-01-28         1     2
2019-02-01         1     2
2019-02-02         1     3

The GOALS column contains a goals count for not more than 28 previous days relative to a date in the D column for each player_id. There may be multiple dates for each player as in the example, when the player commits the required number of goals since past 28 days.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below

select player_id,total_goal from
(
  select    a.player_id, sum(a.goals) as total_goal,
          min(days(a.date)-days(b.date)) as time_period
  from
  (
    select *
    from
    (
      select    player_id, 
              date, 
              row_number() over (partition by player_id order by date asc) as goals 
      from  matches m
      where             date>date('01.01.2019')
      and       player_id<>''
    )
    where goals >=5
  ) a
  join
  (
    select  player_id, 
            date, 
            row_number() over (partition by player_id order by date asc) as goals
    from    matches m
    where       date>date('01.01.2019')
    and player_id<>'' 
  ) b
  on        a.player_id=b.player_id
  and       a.goals=(b.goals+4)
  group by  a.player_id
) Z
where time_period<=28

Upvotes: 0

Related Questions