Preparing Tables

Let us prepare retail tables to come up with the solution for the problem statement.

  • 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 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 Alchemy.

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

\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

%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 current_database()

1 rows affected.
current_database
itversity_retail_db 
%%sql

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_retail_db' 
    AND table_schema = 'public' 
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
table_catalog table_schema table_name table_type self_referencing_column_name reference_generation user_defined_type_catalog user_defined_type_schema user_defined_type_name is_insertable_into is_typed commit_action
itversity_retail_db public categories BASE TABLE None None None None None YES NO None
itversity_retail_db public departments BASE TABLE None None None None None YES NO None
itversity_retail_db public products BASE TABLE None None None None None YES NO None
itversity_retail_db public customers BASE TABLE None None None None None YES NO None
itversity_retail_db public orders BASE TABLE None None None None None YES NO None
itversity_retail_db public order_items BASE TABLE None None None None None YES NO None 
%sql SELECT * FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status
1 2013-07-25 00:00:00 11599 CLOSED
2 2013-07-25 00:00:00 256 PENDING_PAYMENT
3 2013-07-25 00:00:00 12111 COMPLETE
4 2013-07-25 00:00:00 8827 CLOSED
5 2013-07-25 00:00:00 11318 COMPLETE
6 2013-07-25 00:00:00 7130 COMPLETE
7 2013-07-25 00:00:00 4530 COMPLETE
8 2013-07-25 00:00:00 2911 PROCESSING
9 2013-07-25 00:00:00 5657 PENDING_PAYMENT
10 2013-07-25 00:00:00 5648 PENDING_PAYMENT 
%sql SELECT * FROM order_items LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_item_id order_item_order_id order_item_product_id order_item_quantity order_item_subtotal order_item_product_price
1 1 957 1 299.98 299.98
2 2 1073 1 199.99 199.99
3 2 502 5 250.0 50.0
4 2 403 1 129.99 129.99
5 4 897 2 49.98 24.99
6 4 365 5 299.95 59.99
7 4 502 3 150.0 50.0
8 4 1014 4 199.92 49.98
9 5 957 1 299.98 299.98
10 5 365 5 299.95 59.99 
%sql SELECT * FROM products LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
product_id product_category_id product_name product_description product_price product_image
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
2 2 Under Armour Men's Highlight MC Football Clea 129.99 http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat
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
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
5 2 Riddell Youth Revolution Speed Custom Footbal 199.99 http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet
6 2 Jordan Men's VI Retro TD Football Cleat 134.99 http://images.acmesports.sports/Jordan+Men%27s+VI+Retro+TD+Football+Cleat
7 2 Schutt Youth Recruit Hybrid Custom Football H 99.99 http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014
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
9 2 Nike Adult Vapor Jet 3.0 Receiver Gloves 50.0 http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves
10 2 Under Armour Men's Highlight MC Football Clea 129.99 http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat 
%sql SELECT count(1) FROM orders

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
68883 
%sql SELECT count(1) FROM order_items

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
172198 
%sql SELECT count(1) FROM products

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
1345
Define Problem Statement – Daily Product Revenue Selecting or Projecting Data