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 |