Exercises – Analytics Functions

Prepare HR Database¶

Here are the steps to prepare HR database.

• Connect to HR DB using psql or SQL Workbench. Here is the sample psql command.

psql -h localhost 
    -p 5432 
    -d itversity_hr_db 
    -U itversity_hr_user 
    -W

• Run scripts to create tables and load the data. You can also drop the tables if they already exists.

i /data/hr_db/drop_tables_pg.sql
i /data/hr_db/create_tables_pg.sql
i /data/hr_db/load_data_pg.sql

• Validate to ensure that data is available in the tables by running these queries.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db

env: DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db

%sql SELECT * FROM employees LIMIT 10

10 rows affected.

%%sql 

SELECT * FROM departments 
ORDER BY manager_id NULLS LAST
LIMIT 10

 * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db
(psycopg2.errors.UndefinedTable) relation "departments" does not exist
LINE 1: SELECT * FROM departments 
                      ^

[SQL: SELECT * FROM departments 
ORDER BY manager_id NULLS LAST
LIMIT 10]
(Background on this error at: http://sqlalche.me/e/13/f405)

Prepare Retail Database¶

Make sure to drop and recreate the tables before taking care of the exercises related to retail database.

• Ensure that we have required database and user for retail data. We might provide the database as part of our labs. Here are the instructions to use psql for setting up the required database (if required) and tables.

psql -U postgres -h localhost -p 5432 -W

CREATE DATABASE itversity_retail_db;
CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD 'retail_password';
GRANT ALL ON DATABASE itversity_retail_db TO itversity_retail_user;

• Create Tables using the script provided. You can either use psql or SQL Workbench.

psql -U itversity_retail_user 
  -h localhost 
  -p 5432 
  -d itversity_retail_db 
  -W

• You can drop the existing tables.

DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS departments CASCADE;

• Once the tables are dropped you can run below script to create the tables for the purpose of exercises.

i /data/retail_db/create_db_tables_pg.sql

• Data shall be loaded using the script provided.

i /data/retail_db/load_db_tables_pg.sql

• Run queries to validate we have data in all the 3 tables.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%sql SELECT * FROM orders LIMIT 10

10 rows affected.

%sql SELECT count(1) FROM orders

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

%sql SELECT * FROM order_items LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

%sql SELECT count(1) FROM order_items

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

%sql SELECT * FROM products LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

%sql SELECT count(1) FROM products

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Exercise 1¶

Get all the employees who is making more than average salary with in each department.

• Use HR database employees and department tables for this problem.

• Compute average salary expense for each department and get those employee details who are making more salary than average salary.

• Make sure average salary expense per department is rounded off to 2 decimals.

• Output should contain employee_id, department_name, salary and avg_salary_expense (derived field).

• Data should be sorted in ascending order by department_id and descending order by salary.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db

Exercise 2¶

Get cumulative salary with in each department for Finance and IT department along with department name.

• Use HR database employees and department tables for this problem.

• Compute cumulative salary expense for Finance as well as IT departments with in respective departments.

• Make sure cumulative salary expense per department is rounded off to 2 decimals.

• Output should contain employee_id, department_name, salary and cum_salary_expense (derived field).

• Data should be sorted in ascending order by department_name and then salary.

Exercise 3¶

Get top 3 paid employees with in each department by salary (use dense_rank)

• Use HR database employees and department tables for this problem.

• Highest paid employee should be ranked first.

• Output should contain employee_id, department_id, department_name, salary and employee_rank (derived field).

• Data should be sorted in ascending order by department_id in ascending order and then salary in descending order.

Exercise 4¶

Get top 3 products sold in the month of 2014 January by revenue.

• Use retail database tables such as orders, order_items and products.

• Consider only those orders which are either in COMPLETE or CLOSED status.

• Highest revenue generating product should come at top.

• Output should contain product_id, product_name, revenue, product_rank. revenue and product_rank are derived fields.

• Data should be sorted in descending order by revenue.

Exercise 5¶

Get top 3 products sold in the month of 2014 January under selected categories by revenue. The categories are Cardio Equipment and Strength Training.

• Use retail database tables such as orders, order_items, products as well as categories.

• Consider only those orders which are either in COMPLETE or CLOSED status.

• Highest revenue generating product should come at top.

• Output should contain category_id, category_name, product_id, product_name, revenue, product_rank. revenue and product_rank are derived fields.

• Data should be sorted in ascending order by category_id and descending order by revenue.