impala | fastest way to try out Impala is a quickstart Docker

You can't per the 6.1 documentation, PIVOT is not a current functionality.

https://www.cloudera.com/documentation/enterprise/6/6.1/topics/impala_reserved_words.html

I think you can use split_part() here.

class - split_part(split_part(col, 'class:',2),';',1)
subclass - split_part(split_part(col, 'subclass:',2),';',1)

Inner split will split on class word and take second part('104;teacher:ted;school:first;subclass:404'). Then outermost split part will split on ; and pick up first part (104).

Your SQL should be like -

You can use unix_timestamp(timestamp) to convert both fields to unixtime (int) format. This is actually seconds from 1970-01-01 and very suitable to calculate date time differences in seconds. Once you have seconds from 1970-01-01, you can easily minus them both to know the differences. Your sql should be like this -

Your understanding is correct PARTITION clause is optional. and this is correct behavior of COMPUTE INCREMENTAL STATS.
Incremental stats gather stats as usual but if it finds a new partition, it gather stats and show message that it found a new partition and gather stats for that.

When you run COMPUTE INCREMENTAL STATS mytab for the first time, it will gather all the stats of all partitions and you will see message like Updated 4 partition(s) and 200 column(s)..
When you run COMPUTE INCREMENTAL STATS mytab again (without adding new partition), it doesn't find any new partition to gather stats. So it will show this message No partitions selected for incremental stats update. and gather stats of existing data.

We can use aggregation here along with GROUP_CONCAT:

In Hive v.3.0 and up, you have INFORMATION_SCHEMA db that can be queried from Hue to get column info that you need.

Impala is still behind, with JIRAs IMPALA-554 Implement INFORMATION_SCHEMA in Impala and IMPALA-1761 still unresolved.

after various tries, I was able to get required output. here is how I managed. not efficient but working.

Do not confuse the mean and the median:

the median is the value separating the higher half from the lower half of a population (wikipedia)

Impala uses in-memory analytics engine so being minimilastic in every aspect does the trick.

1. Filters - Use as many filters as you can. Use subquery and filter inside subquery if you can.
2. Joins - Main reason of memory issue - you need to use joins intelligently. As per rule of the thumb, in case of inner join - use the driving table first, then tinyiest table and then next tiny table and so on. For left joins you can use same thumb rule. So, move the tables as per their size (columns and count). Also, use as many filters as you can.
3. Operations like distinct, regexp, IN, concat/function in a join condition or filter can slow things down. Please make sure they are absolutely necessary and there is no way you can avoid them.
4. Number of columns in select statement, subquery - keep them minimal.
5. Operations in select statement, subquery - keep them minimal.
6. Partitions - keep them optimized so you have optimum performance. More partition will slow INSERT and less partition will slow down SELECT.
7. Statistics - Create a daily plan to gather statistics of all tables and partitions to make things faster.
8. Explain Plan - Get the explain plan while the query is running. Query execution give you a unique query link. You will see lots of insights in the operations of the SQL.