笔试题

思路：

先统计1,2,3月数据 

select 
uid,
sum(if(  date_format(otime,'yyyy-MM' )='2017-01',1,0 )) month1count,
sum(if(  date_format(otime,'yyyy-MM' )='2017-02',1,0 )) month2count,
sum(if(  date_format(otime,'yyyy-MM' )='2017-03' and oamount>10,1,0 )) month3count,
sum(if( date_format(otime,'yyyy-MM' )='2017-03' and rk=1 ,oamount,0)) month3first,
sum(if( date_format(otime,'yyyy-MM' )='2017-03' and rk=ct ,oamount,0)) month3last
from   (   select   *,  row_number()over(partition by date_format(otime,'yyyy-MM' ), uid order by otime ) rk,
           count(*)over(partition by date_format(otime,'yyyy-MM' ), uid   ) ct 
           from  t_order  
         )      
group by uid 
having month1count>0 and month2count=0