Exercises – SQL Analytics or Windowing Functions using Postgresql

Analytics Functions¶

Let us take care of the exercises related to analytics functions. We will be using HR database for the same.

• Get all the employees who is making more than average salary with in each department.
• Get cumulative salary for one of the department along with department name.
• Get top 3 paid employees with in each department by salary (use dense_rank)
• Get top 3 products sold in the month of 2014 January by revenue.
• Get top 3 products in each category sold in the month of 2014 January by revenue.

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.

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.

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.

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.
• Highest revenue generating product should come at top.
• Consider only those orders which are either in COMPLETE or CLOSED status.
• 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.
• Highest revenue generating product should come at top.
• Consider only those orders which are either in COMPLETE or CLOSED status.
• 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.