sql - write a query to find the change in revenue generated due to purchases from october to november

Question

I have a table which holds values from both October and November months. So basically I have to find the change in revenue generated under event_type 'purchase' from Oct to Nov. I tried this query but it is throwing errors (code is base on Hive Query Language HQL)

SELECT SUM(November) AS November, SUM(October) AS October, 'DIFFERENCE'=SUM(November)-SUM(October)
FROM 
(
SELECT price AS October, 0 AS November FROM Shopping WHERE date_format(event_time,'MM')=10 AND event_type='purchase' 
UNION ALL 
SELECT 0 AS October, price AS November FROM Shopping WHERE date_format(event_type,'MM')=11 AND event_type='purchase'
) AS a;

Error:

FAILED: ParseException line 2:0 cannot recognize input near 'SELECT' 'price' 'AS' in join source

What's wrong with this code and how can I solve the query using HQL

Answer 1

You can do the same without UNION ALL, in single table scan.

select October, November, November - October Difference
from
( 
SELECT sum(case when date_format(event_time,'MM')=10 then price else 0 end) AS October, 
       sum(case when date_format(event_time,'MM')=11 then price else 0 end) AS November
 FROM Shopping WHERE date_format(event_time,'MM')in (10,11) AND event_type='purchase' 
)s;

You may want to add year and group by year if there are many years