CODEWITHSUNDEEP
hivehiveqlcommon-table-expression
Aishwarya Wuntkal
Aishwarya Wuntkal

Reputation: 69

Unable to use CTE in hive table to write a query

   WITH month_revenue AS 
   (SELECT SUM(price) AS Oct_Revenue FROM attribute_partition WHERE event_type= 'purchase' AND 
   MONTH(event_time) = '10')  , 
   (SELECT SUM(price) AS Nov_Revenue FROM attribute_partition WHERE event_type= 'purchase' AND 
   MONTH(event_time) = '11')
   SELECT (Oct_Revenue - Nov_Revenue) FROM month_revenue ;

Write a query to find the change in revenue generated due to purchases from October to November. The error I get is :

FAILED: ParseException line 2:0 missing ( at 'SELECT' near ',' in statement line 2:117 missing ) at ',' near ',' in statement line 3:0 cannot recognize input near 'SELECT' 'SUM' '(' in statement

Please help me understand where I am doing wrong

Upvotes: 1

Views: 204

Answers (1)

leftjoin
leftjoin

Reputation: 38325

One CTE is a single query. It is possible to calculate both months in single statement like this:

 WITH month_revenue AS 
   (SELECT 
         SUM(case when MONTH(event_time) = '10' then price else 0 end) AS Oct_Revenue,
         SUM(case when MONTH(event_time) = '11' then price else 0 end) AS Nov_Revenue
    FROM attribute_partition 
    WHERE event_type= 'purchase' 
      AND MONTH(event_time) in ('10', '11')
  )  

   SELECT (Oct_Revenue - Nov_Revenue) FROM month_revenue ;

Upvotes: 1

Related Questions