Basic SQL Queries¶
Here are some of the exercises for which you can write SQL queries to self evaluate.
- 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
psqlfor 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
psqlor 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.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
Exercise 1 – Customer order count¶
Get order count per customer for the month of 2014 January.
- Tables – orders and customers
- Data should be sorted in descending order by count and ascending order by customer id.
- Output should contain customer_id, customer_first_name, customer_last_name and customer_order_count.
In [3]:
%%sql
SELECT c.customer_id,
c.customer_fname,
c.customer_lname,
count(o.order_id) AS customer_order_count
FROM orders o JOIN customers c
ON o.order_customer_id = c.customer_id
WHERE to_char(o.order_date, 'yyyy-MM') = '2014-01'
GROUP BY c.customer_id
ORDER BY customer_order_count DESC, c.customer_id
LIMIT 10;
Out[3]:
Exercise 2 – Dormant Customers¶
Get the customer details who have not placed any order for the month of 2014 January.
- Tables – orders and customers
- Data should be sorted in ascending order by customer_id
- Output should contain all the fields from customers
In [7]:
%%sql
SELECT count(1) FROM customers
Out[7]:
In [15]:
%%sql
select c.*
FROM customers c LEFT OUTER JOIN
(SELECT DISTINCT order_customer_id
FROM orders WHERE
to_char(order_date, 'yyyy-MM') = '2014-01'
) o
ON c.customer_id = o.order_customer_id
WHERE o.order_customer_id IS NULL
ORDER BY c.customer_id
LIMIT 10
Out[15]:
In [14]:
%%sql
select count(1)
FROM customers c LEFT OUTER JOIN
(SELECT DISTINCT order_customer_id
FROM orders WHERE
to_char(order_date, 'yyyy-MM') = '2014-01'
) o
ON c.customer_id = o.order_customer_id
WHERE o.order_customer_id IS NULL
Out[14]:
In [12]:
%%sql
SELECT c.*
FROM customers c LEFT OUTER JOIN ORDERS o
ON c.customer_id = o.order_customer_id
AND to_char(o.order_date, 'yyyy-MM') = '2014-01'
WHERE o.order_customer_id IS NULL
LIMIT 10;
Out[12]:
In [13]:
%%sql
SELECT count(1)
FROM customers c LEFT OUTER JOIN ORDERS o
ON c.customer_id = o.order_customer_id
AND to_char(o.order_date, 'yyyy-MM') = '2014-01'
WHERE o.order_customer_id IS NULL
Out[13]:
Exercise 3 – Revenue Per Customer¶
Get the revenue generated by each customer for the month of 2014 January
- Tables – orders, order_items and customers
- Data should be sorted in descending order by revenue and then ascending order by customer_id
- Output should contain customer_id, customer_first_name, customer_last_name, customer_revenue.
- If there are no orders placed by customer, then the corresponding revenue for a given customer should be 0.
- Consider only COMPLETE and CLOSED orders
Solution – Revenue Per Customer¶
In [17]:
%%sql
SELECT c.customer_id, c.customer_fname, c.customer_lname,
CASE
WHEN SUM(oi.order_item_subtotal::numeric) IS NULL
THEN 0
ELSE SUM(oi.order_item_subtotal::numeric)
END AS customer_revenue
FROM customers c
LEFT OUTER JOIN orders o
ON c.customer_id = o.order_customer_id
LEFT OUTER JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
AND to_char(o.order_date, 'yyyy-MM') = '2014-01'
GROUP BY c.customer_id
ORDER BY customer_revenue DESC, c.customer_id
LIMIT 10;
Out[17]:
Exercise 4 – Revenue Per Category¶
Get the revenue generated for each category for the month of 2014 January
- Tables – orders, order_items, products and categories
- Data should be sorted in ascending order by category_id.
- Output should contain all the fields from category along with the revenue as category_revenue.
- Consider only COMPLETE and CLOSED orders
In [19]:
%%sql
SELECT c.*,
round(sum(oi.order_item_subtotal)::numeric, 2) AS category_revenue
FROM categories c JOIN products p
ON c.category_id = p.product_category_id
JOIN order_items oi
ON p.product_id = oi.order_item_product_id
JOIN orders o
ON oi.order_item_order_id = o.order_id
WHERE to_char(o.order_date, 'yyyy-MM') = '2014-01'
AND o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY c.category_id
ORDER BY c.category_id
LIMIT 10;
Out[19]:
Exercise 5 – Product Count Per Department¶
Get the products for each department.
- Tables – departments, categories, products
- Data should be sorted in ascending order by department_id
- Output should contain all the fields from department and the product count as product_count
In [21]:
%%sql
SELECT d.*,
count(p.product_id) AS product_count
FROM departments d
JOIN categories c
ON d.department_id = c.category_department_id
JOIN products p
ON c.category_id = p.product_category_id
GROUP BY d.department_id
ORDER BY d.department_id;
Out[21]: