consecutive days in sql

Question

I found many stackoverflow QnAs about consecutive days.
Still answers are too short for me to understand what's going on.

For concreteness, I'll make up a model (or a table)
(I'm using postgresql if it makes a difference.)

CREATE TABLE work (
    id integer NOT NULL,
    user_id integer NOT NULL,
    arrived_at timestamp with time zone NOT NULL
);


insert into work(user_id, arrived_at) values(1, '01/03/2011');
insert into work(user_id, arrived_at) values(1, '01/04/2011');

1. (In simplest form) For a given user, I want to find the last-consecutive date range.

2. (My ultimate goal) For a given user, I want to find his consecutive working days.
   If he came to work yesterday, he still(as of today) has chance of working consecutive days. So I show him consecutive days upto yesterday.
   But if he missed yesterday, his consecutive days is either 0 or 1 depending on whether he came today or not.

Say today is 8th day.

3 * 5 6 7 * = 3 days (5 to 7)
3 * 5 6 7 8 = 4 days (5 to 8)
3 4 5 * 7 * = 1 day (7 to 7)
3 * * * * * = 0 day 
3 * * * * 8 = 1 day (8 to 8)

Answer 1

Here is my solution to this problem using CTE

WITH RECURSIVE CTE(attendanceDate)
AS
(
   SELECT * FROM 
   (
      SELECT attendanceDate FROM attendance WHERE attendanceDate = current_date 
      OR attendanceDate = current_date - INTERVAL '1 day' 
      ORDER BY attendanceDate DESC
      LIMIT 1
   ) tab
   UNION ALL

   SELECT a.attendanceDate  FROM attendance a
   INNER JOIN CTE c
   ON a.attendanceDate = c.attendanceDate - INTERVAL '1 day'
) 
SELECT COUNT(*) FROM CTE;

Check the code at SQL Fiddle

Here is how the query is working:

1. It selects today's record from attendance table. If today's record is not available then it selects yesterday's record
2. It then keeps adding recursively record a day before the least date

If you want to select latest consecutive date range irrespective of when was user's latest attendance(today, yesterday or x days before), then the initialization part of CTE must be replaced by below snippet:

SELECT MAX(attendanceDate) FROM attendance

[EDIT] Here is query at SQL Fiddle which resolves your question#1: SQL Fiddle

Answer 2

You can create an aggregate with the range types:

Create function sfunc (tstzrange, timestamptz)
    returns tstzrange
    language sql strict as $$
        select case when $2 - upper($1) <= '1 day'::interval
                then tstzrange(lower($1), $2, '[]')
                else tstzrange($2, $2, '[]') end
    $$;

Create aggregate consecutive (timestamptz) (
        sfunc = sfunc,
        stype = tstzrange,
        initcond = '[,]'
);

Use the aggregate with the right order the get the consecutive day range for the last arrived_at:

Select user_id, consecutive(arrived_at order by arrived_at)
    from work
    group by user_id;

    ┌─────────┬─────────────────────────────────────────────────────┐
    │ user_id │                     consecutive                     │
    ├─────────┼─────────────────────────────────────────────────────┤
    │       1 │ ["2011-01-03 00:00:00+02","2011-01-05 00:00:00+02"] │
    │       2 │ ["2011-01-06 00:00:00+02","2011-01-06 00:00:00+02"] │
    └─────────┴─────────────────────────────────────────────────────┘

Use the aggregate in a window function:

Select *,
        consecutive(arrived_at)
                over (partition by user_id order by arrived_at)
    from work;

    ┌────┬─────────┬────────────────────────┬─────────────────────────────────────────────────────┐
    │ id │ user_id │       arrived_at       │                     consecutive                     │
    ├────┼─────────┼────────────────────────┼─────────────────────────────────────────────────────┤
    │  1 │       1 │ 2011-01-03 00:00:00+02 │ ["2011-01-03 00:00:00+02","2011-01-03 00:00:00+02"] │
    │  2 │       1 │ 2011-01-04 00:00:00+02 │ ["2011-01-03 00:00:00+02","2011-01-04 00:00:00+02"] │
    │  3 │       1 │ 2011-01-05 00:00:00+02 │ ["2011-01-03 00:00:00+02","2011-01-05 00:00:00+02"] │
    │  4 │       2 │ 2011-01-06 00:00:00+02 │ ["2011-01-06 00:00:00+02","2011-01-06 00:00:00+02"] │
    └────┴─────────┴────────────────────────┴─────────────────────────────────────────────────────┘

Query the results to find what you need:

With work_detail as (select *,
            consecutive(arrived_at)
                    over (partition by user_id order by arrived_at)
        from work)
    select arrived_at, upper(consecutive) - lower(consecutive) as days
        from work_detail
            where user_id = 1 and upper(consecutive) != lower(consecutive)
            order by arrived_at desc
                limit 1;

    ┌────────────────────────┬────────┐
    │       arrived_at       │  days  │
    ├────────────────────────┼────────┤
    │ 2011-01-05 00:00:00+02 │ 2 days │
    └────────────────────────┴────────┘