Kaggle : ecommerce-events-history-in-cosmetics-shop

 Refer : https://www.kaggle.com/mkechinov/ecommerce-events-history-in-cosmetics-shop

CREATE EXTERNAL TABLE IF NOT EXISTS ecom_events (

event_time timestamp, 

event_type string,

product_id string,

category_id string,

category_code string,

brand string,

price float,

user_id bigint,

user_session string

 )

 COMMENT 'ecom_events Table'

 ROW FORMAT DELIMITED

 FIELDS TERMINATED BY ','

 LOCATION '/tmp/ecomdata'; 

 ;

 

 ALTER TABLE ecom_events SET TBLPROPERTIES ("skip.header.line.count"="1");

 ALTER TABLE ecom_events SET TBLPROPERTIES ("timestamp.formats"="yyyy-MM-dd HH:mm:ss 'UTC'");

 set hive.cli.print.header=true;

===================

select sum(price) as oct_revenue from ecom_events where event_type='purchase' and month(event_time)=10;

----------------------------------

| oct_revenue   |

----------------------------------

| 1211538.4295325726 |

----------------------------------

2. Write a query to yield the total sum of purchases per month in a single output. 

Write a query to find the change in the revenue generated due to purchases made from October to November.

Find distinct categories of products.

Find the total number of products available under each category. 

Which brand had the maximum sales in October and November combined?

Which brands increased their sales from October to November?

Write a query to generate a list of top 10 users who spend the most on purchases.