CODEWITHSUNDEEP
mysqlsqlgroup-bydate-format
user1489719
user1489719

Reputation: 119

How would you handle grouping by weeks across different years?

For the analysis that I do, we work with what happened in a given month or week.

For example:

SELECT date_format(registration_date, '%Y-%u') as 'cohort', count(id) as 'count' 
FROM account 
GROUP BY `cohort`

For months, the changing year doesn't create an issue. Though for weeks, it will create a disjointed week for the last week in 2013 and first in 2014.

Is there any easy way to do groupings based on a rolling 7 day basis?

Thanks!

Upvotes: 0

Views: 105

Answers (1)

Laurence
Laurence

Reputation: 10976

cohort here is "weeks ago". Is this what you mean by a rolling seven day grouping?

select
    cast(datediff(current_date(), registration_date) / 7 as unsigned) `cohort`, 
    count(id) `count`
from
    account 
group by
    `cohort`

Example Fiddle

Upvotes: 1

Related Questions