Select T2.user_id,t2.start_time, T2.score, T2.win_coins from (
Select D.user_id,d.start_time, D.score, D.win_coins from (
Select user_id, To_char (start_time, ' YYYYMMDD ') as Start_time,score,win_coins
, Row_number () over (partition by User_id,to_char (start_time, ' YYYYMMDD ') Order by To_date (To_char, ' YYYYMMDD '), ' YYYYMMDD ') asc,score ASC) as RN
From Drivereport where To_char (start_time, ' yyyy-mm-dd ') between ' 2015-05-01 ' and ' 2015-05-11 '
D where rn=1) T2,
(SELECT distinct (c.user_id) as user_id from
(SELECT b.user_id, (MAX (To_char (b.start_time, ' YYYYMMDD '))-MIN (To_char (b.start_time, ' YYYYMMDD ')) +1) as Days
From (SELECT a.*, To_number (To_char (a.start_time, ' YYYYMMDD ')-rownum) as days
From (
SELECT user_id, To_date (To_char (start_time, ' yyyymmdd '), ' YYYYMMDD ') as Start_time, Min (score) as MI
From Drivereport where To_char (start_time, ' YYYYMMDD ') >= ' 20150501 ' and To_char (start_time, ' YYYYMMDD ') <= ' 20150511 '
GROUP by user_id, To_char (start_time, ' YYYYMMDD ') Order by user_id, To_char (start_time, ' YYYYMMDD ')
) A) B
GROUP by b.user_id, B.days) C
WHERE C.days > 2 ORDER by c.user_id ASC) T3
where t3.user_id = t2.user_id
======================================
--for at least 3 consecutive days to open driving records, such as User 1:1th ~3 record, User 2:2nd number ~4, User 3:4th number ~6 record; User 4:7th ~10 number
--The following SQL mainly gets the ID of the user who has at least 3 consecutive days
SELECT distinct (c.user_id) as user_id
From (SELECT b.user_id
, (MAX (To_char (b.start_time, ' YYYYMMDD '))-
MIN (To_char (b.start_time, ' YYYYMMDD ')) + 1) as days
From (SELECT a.user_id
, A.start_time
, To_number (To_char (a.start_time, ' YYYYMMDD ')-
rownum) as Days
From (SELECT user_id
, To_date (To_char (start_time, ' yyyymmdd '), ' YYYYMMDD ') as Start_time
From Drivereport
WHERE to_char (start_time, ' yyyy-mm-dd ') >=
' 2015-05-01 '
and To_char (start_time, ' yyyy-mm-dd ') <=
' 2015-05-11 '
GROUP by user_id
, To_char (start_time, ' YYYYMMDD ')
ORDER BY user_id
, To_char (start_time, ' YYYYMMDD ')) (a) b
GROUP by b.user_id
, B.days) C
WHERE c.days > 2
ORDER BY c.user_id ASC