I have a table 'x' with columns report_date, card_no, prod_hierarcy, card_grp, tot_bill(in $) as shown in below table with dummy values. I need to write hive query to calculate total spend as column 'y' at card_no level for card_catg wise for all product_hieracy for a year range: 2019- 2020 and for year 2020 and total spend as column 'z'for a year range: 2017 - March,2019
report_date | card_no | prod_hierarcy | card_grp | tot_bill |
---|---|---|---|---|
2019-01-01 | 2437487284 | Gold | Consumer | 900 |
2019-02-01 | 3030455445 | platinum | Consumer | 500 |
2020-01-01 | 3445322435 | Gold | Business | 300 |
2019-01-01 | 2437487284 | Platinum | Consumer | 400 |
2020-02-01 | 3030455445 | Bronze | Consumer | 800 |
2019-01-01 | 3445322435 | Gold | Business | 700 |
2020-05-01 | 2437487284 | Gold | Consumer | 900 |
2017-01-01 | 2437487284 | Gold | Consumer | 900 |
2019-02-01 | 3445322435 | Gold | Consumer | 100 |
2020-05-01 | 2437487284 | Gold | Consumer | 200 |