n8NkgRpg

1USE hob2021153_hw_test;
2
3DROP TABLE IF EXISTS task3;
4CREATE EXTERNAL TABLE task3 (
5    subtype STRING,
6    content STRUCT <
7        totalSum: FLOAT, 
8        userInn: STRING, 
9        dateTime: STRUCT< context_date: TIMESTAMP>
10        >
11)
12ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
13WITH serdeproperties(
14    'ignore.malformed.json' = 'true',
15    'mapping.subtype'='subtype',
16    'mapping.content'='content',
17    'mapping.totalSum'='totalSum',
18    'mapping.userInn'='userInn',
19    'mapping.dateTime'='dateTime',
20    'mapping.context_date'='$date'
21)
22LOCATION '/data/hive/fns2';
23
24SELECT userinn, date, totalsum FROM (
25    SELECT userinn, date, totalsum, rank() OVER(PARTITION BY userinn ORDER BY totalsum DESC) as rank
26    FROM (
27            SELECT content.userInn as userinn, DAY(content.dateTime.context_date) as date, COALESCE(SUM(content.totalSum), 0) as totalsum FROM task3
28            WHERE content.userInn is NOT NULL
29            GROUP BY content.userInn,  DAY(content.dateTime.context_date)
30    ) as t ) as t2
31    WHERE rank = 1 SORT BY totalsum DESC;