CODEWITHSUNDEEP
mysqlsqldatetime
DropDropped
DropDropped

Reputation: 1273

Counting consecutive days for all items in SQL

I'm trying to count consecutive days for all items. I've found how to count consecutive days just for one item, sample data

bob 2014-08-10 00:35:22
sue 2014-08-10 00:35:22
bob 2014-08-11 00:35:22
mike 2014-08-11 00:35:22
bob 2014-08-12 00:35:22
mike 2014-08-12 00:35:22

would give me result for Bob like this:

date_created         | streak|
2014-08-10 00:35:22  |      3|

But I'd need to get it for all users like this:

date_created         | streak|username
2014-08-10 00:35:22  |      3|     Bob
2014-08-11 00:35:22  |      2|     Mike

I've been trying to modify sql from this response, but I just can not get it work. I'd be thankful for any advice.

Upvotes: 0

Views: 3006

Answers (2)

zealous
zealous

Reputation: 7503

you can solve this with CTE as follows and here is the sqlfiddle

with ranks as
(
  select
    date,
    name,
    dateadd(day, -row_number() OVER (PARTITION BY name ORDER BY date), date) as rr
  from orders
),
cnt as
(
  select
    name,
    count(*) as ttl
  from ranks
  group by
    name,
    rr
)

select 
  min(date) as date,
  r.name,
  ttl
FROM ranks r
join cnt c
on r.name = c.name
where ttl > 1
group by
  r.name,
  ttl

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25862

one thing you could do is self join the table to itself on consecutive days and count it. note I add one to the count because it wont count the first day

SELECT MIN(e.date_created) as date_created, e.username, COUNT(e.username) + 1 AS streak
FROM example e
LEFT JOIN example ee 
  ON e.username = ee.username 
 AND DATE(e.date_created) = DATE(DATE_ADD(ee.date_created, INTERVAL -1 DAY))
WHERE ee.username IS NOT NULL
GROUP BY e.username;

Sql Fiddle

Upvotes: 1

Related Questions