Bdtjtk

I'm having some thoughts on an SQL (Redshift) query I need. So basically, I have the following table

userid | timestamp           | fruit

  1    | 2018-12-10T14:46:50 | banana 

  1    | 2018-12-10T15:46:50 | banana

  1    | 2018-12-10T16:46:50 | apple

  1    | 2018-12-10T17:46:50 | banana

Would it be possible to come up with a new table with the following information

userid | start               | end                 | fruit

  1    | 2018-12-10T14:46:50 | 2018-12-10T16:46:50 | banana 

  1    | 2018-12-10T16:46:50 | 2018-12-10T17:46:50 | apple

  1    | 2018-12-10T17:46:50 |                     | banana

showing the time range where the user kept his favorite fruit selection.

Thanks in advance!

D

It's a typical gaps and islands problem, lag and lead analytic functions might be used as follows:

with fruits(userid,timestamp,fruit) as (

values 

  (1,'2018-12-10T14:46:50','banana'), 

  (1,'2018-12-10T15:46:50','banana'),

  (1,'2018-12-10T16:46:50','apple'),

  (1,'2018-12-10T17:46:50','banana')    

)

 select userid, min(timestamp) as start, max(ld) as end, fruit

   from

   (

        select f2.*,

               sum(case when lg = fruit then 0 else 1 end) over

                         (partition by userid, fruit order by timestamp) sm

          from

          (

             select f1.*,

                    lead(timestamp) over (partition by userid order by timestamp) as ld,

                    lag(fruit) over (partition by userid order by timestamp) as lg

               from fruits f1

          ) f2

   ) f    

  group by userid, fruit, sm

  order by start;



userid     start                    end           fruit

------- ------------------- -------------------   ------

  1     2018-12-10T14:46:50 2018-12-10T16:46:50   banana

  1     2018-12-10T16:46:50 2018-12-10T17:46:50   apple

  1     2018-12-10T17:46:50         NULL          banana

Rextester Demo

Schema (MySQL v8.0)

CREATE TABLE t1 (

  `userid` INTEGER,

  `timestamp` VARCHAR(19),

  `fruit` VARCHAR(6)

);



INSERT INTO t1

  (`userid`, `timestamp`, `fruit`)

VALUES

  ('1', '2018-12-10T14:46:50', 'banana'),

  ('1', '2018-12-10T15:46:50', 'banana'),

  ('1', '2018-12-10T16:46:50', 'apple'),

  ('1', '2018-12-10T17:46:50', 'banana');

Query #1

Simple approach if you dont mind multiple records for consecutive fruits

select userid, fruit, timestamp `start`, 

  lead(timestamp) over (order by timestamp) `end`

from t1;



| userid | fruit  | start               | end                 |

| ------ | ------ | ------------------- | ------------------- |

| 1      | banana | 2018-12-10T14:46:50 | 2018-12-10T15:46:50 |

| 1      | banana | 2018-12-10T15:46:50 | 2018-12-10T16:46:50 |

| 1      | apple  | 2018-12-10T16:46:50 | 2018-12-10T17:46:50 |

| 1      | banana | 2018-12-10T17:46:50 |                     |

Or
Query #2

SELECT t2.* 

FROM   ( 

                SELECT   userid, 

                         fruit, 

                         timestamp `tstart`, 

                         CASE 

                                  WHEN fruit = Lead(fruit) over(ORDER BY timestamp) THEN lead(timestamp, 2) over ( ORDER BY timestamp)

                                  ELSE lead(timestamp, 1) over ( ORDER BY timestamp) 

                         end `tend`, 

                         CASE 

                                  WHEN fruit = lag(fruit) over (ORDER BY timestamp) THEN 1 

                                  ELSE 0 

                         end del 

                FROM     t1 ) t2 

WHERE  del = 0;





| userid | fruit  | tstart              | tend                | del |

| ------ | ------ | ------------------- | ------------------- | --- |

| 1      | banana | 2018-12-10T14:46:50 | 2018-12-10T16:46:50 | 0   |

| 1      | apple  | 2018-12-10T16:46:50 | 2018-12-10T17:46:50 | 0   |

| 1      | banana | 2018-12-10T17:46:50 |                     | 0   |

View on DB Fiddle