LF5UMmMh

1CREATE TABLE IF NOT EXISTS db.test_data
2(
3 user_id int
4,country string
5,last_modified_date date
6)
7ROW FORMAT DELIMITED
8FIELDS TERMINATED BY ','
9STORED AS orc
10; 
11     
12INSERT INTO TABLE db.test_data VALUES
13(1,'India','2019-08-06'),
14(2,'Ukraine','2019-08-06'),
15(1,'India','2019-08-05'),
16(2,'Ukraine','2019-08-05'),
17(1,'India','2019-08-04'),
18(2,'Ukraine','2019-08-04'); 
19     
20CREATE VIEW db.test_view AS
21select user_id, country, last_modified_date
22from ( select user_id, country, last_modified_date, 
23              max(last_modified_date) over (partition by user_id) as max_modified 
24       from db.test_data ) as sub
25where   last_modified_date = max_modified 
26;
27
28hive> select * from db.test_view;
29
301       India   2019-08-06
312       Ukraine 2019-08-06
32Time taken: 5.297 seconds, Fetched: 2 row(s)