{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Exercises - Analytics Functions\n", "\n", "Let us take care of the exercises related to analytics functions. We will be using HR database for the same.\n", "\n", "* Get all the employees who is making more than average salary with in each department.\n", "* Get cumulative salary for one of the department along with department name.\n", "* Get top 3 paid employees with in each department by salary (use dense_rank)\n", "* Get top 3 products sold in the month of 2014 January by revenue.\n", "* Get top 3 products in each category sold in the month of 2014 January by revenue." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Prepare HR Database\n", "\n", "Here are the steps to prepare HR database.\n", "* Connect to HR DB using `psql` or SQL Workbench. Here is the sample `psql` command.\n", "\n", "```shell\n", "psql -h localhost \\\n", " -p 5432 \\\n", " -d itversity_hr_db \\\n", " -U itversity_hr_user \\\n", " -W\n", "```\n", "\n", "* Run scripts to create tables and load the data. You can also drop the tables if they already exists.\n", "\n", "```sql\n", "\\i /data/hr_db/drop_tables_pg.sql\n", "\\i /data/hr_db/create_tables_pg.sql\n", "\\i /data/hr_db/load_data_pg.sql\n", "```\n", "\n", "* Validate to ensure that data is available in the tables by running these queries." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_idfirst_namelast_nameemailphone_numberhire_datejob_idsalarycommission_pctmanager_iddepartment_id
100StevenKingSKING515.123.45671987-06-17AD_PRES24000.00NoneNone90
101NeenaKochharNKOCHHAR515.123.45681989-09-21AD_VP17000.00None10090
102LexDe HaanLDEHAAN515.123.45691993-01-13AD_VP17000.00None10090
103AlexanderHunoldAHUNOLD590.423.45671990-01-03IT_PROG9000.00None10260
104BruceErnstBERNST590.423.45681991-05-21IT_PROG6000.00None10360
105DavidAustinDAUSTIN590.423.45691997-06-25IT_PROG4800.00None10360
106ValliPataballaVPATABAL590.423.45601998-02-05IT_PROG4800.00None10360
107DianaLorentzDLORENTZ590.423.55671999-02-07IT_PROG4200.00None10360
108NancyGreenbergNGREENBE515.124.45691994-08-17FI_MGR12000.00None101100
109DanielFavietDFAVIET515.124.41691994-08-16FI_ACCOUNT9000.00None108100
" ], "text/plain": [ "[(100, 'Steven', 'King', 'SKING', '515.123.4567', datetime.date(1987, 6, 17), 'AD_PRES', Decimal('24000.00'), None, None, 90),\n", " (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', datetime.date(1989, 9, 21), 'AD_VP', Decimal('17000.00'), None, 100, 90),\n", " (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', datetime.date(1993, 1, 13), 'AD_VP', Decimal('17000.00'), None, 100, 90),\n", " (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', datetime.date(1990, 1, 3), 'IT_PROG', Decimal('9000.00'), None, 102, 60),\n", " (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', datetime.date(1991, 5, 21), 'IT_PROG', Decimal('6000.00'), None, 103, 60),\n", " (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', datetime.date(1997, 6, 25), 'IT_PROG', Decimal('4800.00'), None, 103, 60),\n", " (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', datetime.date(1998, 2, 5), 'IT_PROG', Decimal('4800.00'), None, 103, 60),\n", " (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', datetime.date(1999, 2, 7), 'IT_PROG', Decimal('4200.00'), None, 103, 60),\n", " (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', datetime.date(1994, 8, 17), 'FI_MGR', Decimal('12000.00'), None, 101, 100),\n", " (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', datetime.date(1994, 8, 16), 'FI_ACCOUNT', Decimal('9000.00'), None, 108, 100)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM employees LIMIT 10" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "(psycopg2.errors.UndefinedTable) relation \"departments\" does not exist\n", "LINE 1: SELECT * FROM departments \n", " ^\n", "\n", "[SQL: SELECT * FROM departments \n", "ORDER BY manager_id NULLS LAST\n", "LIMIT 10]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql \n", "\n", "SELECT * FROM departments \n", "ORDER BY manager_id NULLS LAST\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Prepare Retail Database\n", "\n", "Make sure to drop and recreate the tables before taking care of the exercises related to retail database.\n", "\n", "* 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.\n", "\n", "```shell\n", "psql -U postgres -h localhost -p 5432 -W\n", "```\n", "\n", "```sql\n", "CREATE DATABASE itversity_retail_db;\n", "CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD 'retail_password';\n", "GRANT ALL ON DATABASE itversity_retail_db TO itversity_retail_user;\n", "```\n", "\n", "* Create Tables using the script provided. You can either use `psql` or **SQL Workbench**.\n", "\n", "```shell\n", "psql -U itversity_retail_user \\\n", " -h localhost \\\n", " -p 5432 \\\n", " -d itversity_retail_db \\\n", " -W\n", "```\n", "\n", "* You can drop the existing tables.\n", "\n", "```sql\n", "DROP TABLE IF EXISTS order_items CASCADE;\n", "DROP TABLE IF EXISTS orders CASCADE;\n", "DROP TABLE IF EXISTS customers CASCADE;\n", "DROP TABLE IF EXISTS products CASCADE;\n", "DROP TABLE IF EXISTS categories CASCADE;\n", "DROP TABLE IF EXISTS departments CASCADE;\n", "```\n", "\n", "* Once the tables are dropped you can run below script to create the tables for the purpose of exercises.\n", "\n", "```sql\n", "\\i /data/retail_db/create_db_tables_pg.sql\n", "```\n", "\n", "* Data shall be loaded using the script provided.\n", "\n", "```sql\n", "\\i /data/retail_db/load_db_tables_pg.sql\n", "```\n", "\n", "* Run queries to validate we have data in all the 3 tables." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_customer_idorder_status
12013-07-25 00:00:0011599CLOSED
22013-07-25 00:00:00256PENDING_PAYMENT
32013-07-25 00:00:0012111COMPLETE
42013-07-25 00:00:008827CLOSED
52013-07-25 00:00:0011318COMPLETE
62013-07-25 00:00:007130COMPLETE
72013-07-25 00:00:004530COMPLETE
82013-07-25 00:00:002911PROCESSING
92013-07-25 00:00:005657PENDING_PAYMENT
102013-07-25 00:00:005648PENDING_PAYMENT
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 11599, 'CLOSED'),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT'),\n", " (3, datetime.datetime(2013, 7, 25, 0, 0), 12111, 'COMPLETE'),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED'),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE'),\n", " (6, datetime.datetime(2013, 7, 25, 0, 0), 7130, 'COMPLETE'),\n", " (7, datetime.datetime(2013, 7, 25, 0, 0), 4530, 'COMPLETE'),\n", " (8, datetime.datetime(2013, 7, 25, 0, 0), 2911, 'PROCESSING'),\n", " (9, datetime.datetime(2013, 7, 25, 0, 0), 5657, 'PENDING_PAYMENT'),\n", " (10, datetime.datetime(2013, 7, 25, 0, 0), 5648, 'PENDING_PAYMENT')]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM orders LIMIT 10" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
68883
" ], "text/plain": [ "[(68883,)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM orders" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_item_idorder_item_order_idorder_item_product_idorder_item_quantityorder_item_subtotalorder_item_product_price
119571299.98299.98
2210731199.99199.99
325025250.050.0
424031129.99129.99
54897249.9824.99
643655299.9559.99
745023150.050.0
8410144199.9249.98
959571299.98299.98
1053655299.9559.99
" ], "text/plain": [ "[(1, 1, 957, 1, 299.98, 299.98),\n", " (2, 2, 1073, 1, 199.99, 199.99),\n", " (3, 2, 502, 5, 250.0, 50.0),\n", " (4, 2, 403, 1, 129.99, 129.99),\n", " (5, 4, 897, 2, 49.98, 24.99),\n", " (6, 4, 365, 5, 299.95, 59.99),\n", " (7, 4, 502, 3, 150.0, 50.0),\n", " (8, 4, 1014, 4, 199.92, 49.98),\n", " (9, 5, 957, 1, 299.98, 299.98),\n", " (10, 5, 365, 5, 299.95, 59.99)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM order_items LIMIT 10" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
172198
" ], "text/plain": [ "[(172198,)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM order_items" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_category_idproduct_nameproduct_descriptionproduct_priceproduct_image
12Quest Q64 10 FT. x 10 FT. Slant Leg Instant U59.98http://images.acmesports.sports/Quest+Q64+10+FT.+x+10+FT.+Slant+Leg+Instant+Up+Canopy
22Under Armour Men's Highlight MC Football Clea129.99http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat
32Under Armour Men's Renegade D Mid Football Cl89.99http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat
42Under Armour Men's Renegade D Mid Football Cl89.99http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat
52Riddell Youth Revolution Speed Custom Footbal199.99http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet
62Jordan Men's VI Retro TD Football Cleat134.99http://images.acmesports.sports/Jordan+Men%27s+VI+Retro+TD+Football+Cleat
72Schutt Youth Recruit Hybrid Custom Football H99.99http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014
82Nike Men's Vapor Carbon Elite TD Football Cle129.99http://images.acmesports.sports/Nike+Men%27s+Vapor+Carbon+Elite+TD+Football+Cleat
92Nike Adult Vapor Jet 3.0 Receiver Gloves50.0http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves
102Under Armour Men's Highlight MC Football Clea129.99http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat
" ], "text/plain": [ "[(1, 2, 'Quest Q64 10 FT. x 10 FT. Slant Leg Instant U', '', 59.98, 'http://images.acmesports.sports/Quest+Q64+10+FT.+x+10+FT.+Slant+Leg+Instant+Up+Canopy'),\n", " (2, 2, \"Under Armour Men's Highlight MC Football Clea\", '', 129.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat'),\n", " (3, 2, \"Under Armour Men's Renegade D Mid Football Cl\", '', 89.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat'),\n", " (4, 2, \"Under Armour Men's Renegade D Mid Football Cl\", '', 89.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat'),\n", " (5, 2, 'Riddell Youth Revolution Speed Custom Footbal', '', 199.99, 'http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet'),\n", " (6, 2, \"Jordan Men's VI Retro TD Football Cleat\", '', 134.99, 'http://images.acmesports.sports/Jordan+Men%27s+VI+Retro+TD+Football+Cleat'),\n", " (7, 2, 'Schutt Youth Recruit Hybrid Custom Football H', '', 99.99, 'http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014'),\n", " (8, 2, \"Nike Men's Vapor Carbon Elite TD Football Cle\", '', 129.99, 'http://images.acmesports.sports/Nike+Men%27s+Vapor+Carbon+Elite+TD+Football+Cleat'),\n", " (9, 2, 'Nike Adult Vapor Jet 3.0 Receiver Gloves', '', 50.0, 'http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves'),\n", " (10, 2, \"Under Armour Men's Highlight MC Football Clea\", '', 129.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat')]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM products LIMIT 10" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
1345
" ], "text/plain": [ "[(1345,)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM products" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Exercise 1\n", "\n", "Get all the employees who is making more than average salary with in each department.\n", "\n", "* Use HR database employees and department tables for this problem.\n", "* Compute average salary expense for each department and get those employee details who are making more salary than average salary.\n", "* Make sure average salary expense per department is rounded off to 2 decimals.\n", "* Output should contain employee_id, department_name, salary and avg_salary_expense (derived field).\n", "* Data should be sorted in ascending order by department_id and descending order by salary.\n", "\n", "|employee_id|department_name|salary|avg_salary_expense|\n", "|---|---|---|---|\n", "|201|Marketing|13000.00|9500.00|\n", "|114|Purchasing|11000.00|4150.00|\n", "|121|Shipping|8200.00|3475.56|\n", "|120|Shipping|8000.00|3475.56|\n", "|122|Shipping|7900.00|3475.56|\n", "|123|Shipping|6500.00|3475.56|\n", "|124|Shipping|5800.00|3475.56|\n", "|184|Shipping|4200.00|3475.56|\n", "|185|Shipping|4100.00|3475.56|\n", "|192|Shipping|4000.00|3475.56|\n", "|193|Shipping|3900.00|3475.56|\n", "|188|Shipping|3800.00|3475.56|\n", "|137|Shipping|3600.00|3475.56|\n", "|189|Shipping|3600.00|3475.56|\n", "|141|Shipping|3500.00|3475.56|\n", "|103|IT|9000.00|5760.00|\n", "|104|IT|6000.00|5760.00|\n", "|145|Sales|14000.00|8955.88|\n", "|146|Sales|13500.00|8955.88|\n", "|147|Sales|12000.00|8955.88|\n", "|168|Sales|11500.00|8955.88|\n", "|148|Sales|11000.00|8955.88|\n", "|174|Sales|11000.00|8955.88|\n", "|149|Sales|10500.00|8955.88|\n", "|162|Sales|10500.00|8955.88|\n", "|156|Sales|10000.00|8955.88|\n", "|150|Sales|10000.00|8955.88|\n", "|169|Sales|10000.00|8955.88|\n", "|170|Sales|9600.00|8955.88|\n", "|163|Sales|9500.00|8955.88|\n", "|151|Sales|9500.00|8955.88|\n", "|157|Sales|9500.00|8955.88|\n", "|158|Sales|9000.00|8955.88|\n", "|152|Sales|9000.00|8955.88|\n", "|100|Executive|24000.00|19333.33|\n", "|108|Finance|12000.00|8600.00|\n", "|109|Finance|9000.00|8600.00|\n", "|205|Accounting|12000.00|10150.00|" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Exercise 2\n", "\n", "Get cumulative salary with in each department for Finance and IT department along with department name.\n", "\n", "* Use HR database employees and department tables for this problem.\n", "* Compute cumulative salary expense for **Finance** as well as **IT** departments with in respective departments.\n", "* Make sure cumulative salary expense per department is rounded off to 2 decimals.\n", "* Output should contain employee_id, department_name, salary and cum_salary_expense (derived field).\n", "* Data should be sorted in ascending order by department_name and then salary.\n", "\n", "|employee_id|department_name|salary|cum_salary_expense|\n", "|---|---|---|---|\n", "|113|Finance|6900.00|6900.00|\n", "|111|Finance|7700.00|14600.00|\n", "|112|Finance|7800.00|22400.00|\n", "|110|Finance|8200.00|30600.00|\n", "|109|Finance|9000.00|39600.00|\n", "|108|Finance|12000.00|51600.00|\n", "|107|IT|4200.00|4200.00|\n", "|106|IT|4800.00|9000.00|\n", "|105|IT|4800.00|13800.00|\n", "|104|IT|6000.00|19800.00|\n", "|103|IT|9000.00|28800.00|" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Exercise 3\n", "\n", "Get top 3 paid employees with in each department by salary (use dense_rank)\n", "\n", "* Use HR database employees and department tables for this problem.\n", "* Highest paid employee should be ranked first.\n", "* Output should contain employee_id, department_id, department_name, salary and employee_rank (derived field).\n", "* Data should be sorted in ascending order by department_id in ascending order and then salary in descending order.\n", "\n", "|employee_id|department_id|department_name|salary|employee_rank|\n", "|---|---|---|---|---|\n", "|200|10|Administration|4400.00|1|\n", "|201|20|Marketing|13000.00|1|\n", "|202|20|Marketing|6000.00|2|\n", "|114|30|Purchasing|11000.00|1|\n", "|115|30|Purchasing|3100.00|2|\n", "|116|30|Purchasing|2900.00|3|\n", "|203|40|Human Resources|6500.00|1|\n", "|121|50|Shipping|8200.00|1|\n", "|120|50|Shipping|8000.00|2|\n", "|122|50|Shipping|7900.00|3|\n", "|103|60|IT|9000.00|1|\n", "|104|60|IT|6000.00|2|\n", "|105|60|IT|4800.00|3|\n", "|106|60|IT|4800.00|3|\n", "|204|70|Public Relations|10000.00|1|\n", "|145|80|Sales|14000.00|1|\n", "|146|80|Sales|13500.00|2|\n", "|147|80|Sales|12000.00|3|\n", "|100|90|Executive|24000.00|1|\n", "|101|90|Executive|17000.00|2|\n", "|102|90|Executive|17000.00|2|\n", "|108|100|Finance|12000.00|1|\n", "|109|100|Finance|9000.00|2|\n", "|110|100|Finance|8200.00|3|\n", "|205|110|Accounting|12000.00|1|\n", "|206|110|Accounting|8300.00|2|" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Exercise 4\n", "\n", "Get top 3 products sold in the month of 2014 January by revenue.\n", "\n", "* Use retail database tables such as orders, order_items and products.\n", "* Consider only those orders which are either in **COMPLETE** or **CLOSED** status.\n", "* Highest revenue generating product should come at top.\n", "* Output should contain product_id, product_name, revenue, product_rank. **revenue** and **product_rank** are derived fields.\n", "* Data should be sorted in descending order by revenue.\n", "\n", "|product_id|product_name|revenue|product_rank|\n", "|---|---|---|---|\n", "|1004|Field & Stream Sportsman 16 Gun Fire Safe|250787.46|1|\n", "|365|Perfect Fitness Perfect Rip Deck|151474.75|2|\n", "|957|Diamondback Women's Serene Classic Comfort Bi|148190.12|3|\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Exercise 5\n", "\n", "Get top 3 products sold in the month of 2014 January under selected categories by revenue. The categories are **Cardio Equipment** and **Strength Training**.\n", "\n", "* Use retail database tables such as orders, order_items, products as well as categories.\n", "* Consider only those orders which are either in **COMPLETE** or **CLOSED** status.\n", "* Highest revenue generating product should come at top.\n", "* Output should contain category_id, category_name, product_id, product_name, revenue, product_rank. revenue and product_rank are derived fields.\n", "* Data should be sorted in ascending order by category_id and descending order by revenue.\n", "\n", "|category_id|category_name|product_id|product_name|revenue|product_rank|\n", "|---|---|---|---|---|---|\n", "|9|Cardio Equipment|191|Nike Men's Free 5.0+ Running Shoe|132286.77|1|\n", "|9|Cardio Equipment|172|Nike Women's Tempo Shorts|870.00|2|\n", "|10|Strength Training|208|SOLE E35 Elliptical|1999.99|1|\n", "|10|Strength Training|203|GoPro HERO3+ Black Edition Camera|1199.97|2|\n", "|10|Strength Training|216|Yakima DoubleDown Ace Hitch Mount 4-Bike Rack|189.00|3|" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }