SubQuery's in Hive

 SubQuery's in Hive(hands on explanation)

-----------------------------
USING IN CLAUSE
hive>select first_name , customer_id  from customers_temp
 where customer_id
in (select cust_id from sales);

USING EXISTS

hive>select first_name,customer_id from customers_temp
where exists
(select cust_id from sales where customers_temp.customer_id = sales.cust_id and sales.qty >1 );

IN FROM CLAUSE

hive >From
(select  City  , avg(temp) avgtemp from  citymonthlytemp group by city ) t
select max(t.avgtemp) maxtemperature


Hint: displaying the maximum temperature .

hive (customersales_stg)> describe product;    
OK
column_name    data_type    comment
product_name               string                                     
description             varchar(20)                                
category                string                                     
quantity_onhand         int                                        
product_id                bigint                                     
packaged_with           array<string>                              
product_price            decimal(4,2)                               

describe  sales;
column_name        data_type    comment
customer_id             int                                        
productid_insales       int                                        
quantity_brought        int                                        
date_sold            string                                     
sales_id                int                                        

select t.custid, sum(t.price) from              
                     (select  s.productid_insales  pdno, s.customer_id  custid,
                     p.productprice  price from sales s join product p
                     on s.productid_insales = p.product_id ) t   group by t.custid;


or below(both produces same results)

from (select s.productid_insales  pdno, s.customer_id  custid,  p.productprice  price from sales s join product p
on s.productid_insales = p.product_id ) t  select t.custid as customerid , sum(t.price) total_purchase_amount group by t.custid;