How to find the previous year particular month sale is higher or not compared to the same month of current year in Hive

SNS 0
Viewed 21

I have Sales Table with the following fields.

year
month
samount

Requirement is to check if current month's sales amount is higher than the previous year sales amount for the same month.

I tried with the following sql but seems to be missing some comparison. Please advise

select year, month, samount, samount-lag(samount,12) over (order by year,month) as diff_cur_prev_sales from sales;
hive sql
1 Answers

If you need to compare the same months sales with different years, You need to change your LAG function. Simply reverse the order of year and month column -

SELECT year, month, samount, prev_year_amount
  FROM (SELECT year, month, samount, lag(samount,12) over (order by month, year) as prev_year_amount 
          FROM sales
       ) T
 WHERE samount > prev_year_amount;
Ankit Bajpai0
Related