hive - Parse error while using ROW_NUMBER OVER PARTITION BY -

i have used below query on hive , got following parse error though query not seem have issues.

select to_date(o.order_date), profit,         row_number() on (partition year(o.order_date) order profit desc) n  (     select to_date(o.order_date), sum(price) revenue,             sum(price-cost) profit      products p, order_details d, orders o      (d.prod_id=p.prod_id) , (d.order_id=o.order_id)      group o.order_date ) 

error is:

error while compiling statement: failed: parseexception line 6:22 cannot recognize input near '' '' '' in subquery source

i see 3 issues hql:

1. the group in sub query needs match non-aggregate columns in select statement

2. you need alias to_date(o.order_date) bit in sub query in order reference in top level query (or refer system generated alias). choke if attempt refer o.order_date in super query if don't pass o.order_date sub query (and alias sub query o). applies o.order_date call in on statement

3. this might not causing issues of versions of hive have used choke if don't alias sub-queries.

the below code should fix issues (assuming there no data issues that can't see looking @ hql):

select      a.dt,      a.profit,      row_number() on (partition year(a.dt) order profit desc) n      (select           to_date(o.order_date) dt,           sum(price) revenue,           sum(price-cost) profit                 products p, order_details d, orders o                 (d.prod_id = p.prod_id)           , (d.order_id = o.order_id)       group           to_date(o.order_date) )