select
distinct a.user_id
from
phone_record a,
phone_record b
where
a.user_id=b.user_id
and
month_between(a.phone_month,b.phone_month)=1
and
a.cost<b.cost
and
exists(select
*
from
phone_record
where
user_id=a.user_id
and
month_between(b.phone_month,phone_month)=1
and
cost>b.cost)
distinct a.user_id
from
phone_record a,
phone_record b
where
a.user_id=b.user_id
and
month_between(a.phone_month,b.phone_month)=1
and
a.cost<b.cost
and
exists(select
*
from
phone_record
where
user_id=a.user_id
and
month_between(b.phone_month,phone_month)=1
and
cost>b.cost)
from
(select a.user_id,(case when cost<lead(a.cost,1,0)over(partition by a.user_id order by a.phone_month) and lead(a.cost,1,0)over(partition by a.user_id order by a.phone_month)<lead(a.cost,2,0)over(partition by a.user_id order by a.phone_month) then 1 else 0 end) as flag
from phone_record a) b
where b.flag=1
FROM (SELECT user_id, phone_month, cost,
add_months(phone_month, -dense_rank() over(PARTITION BY user_id ORDER BY phone_month)) tmp_col
FROM phone_record t)
GROUP BY user_id, tmp_col
HAVING COUNT(DISTINCT phone_month) >= 3