{ "cells": [ { "cell_type": "markdown", "id": "e74edf74", "metadata": {}, "source": [ "# Window Functions in Action: SQL Analytics for Northwind Traders" ] }, { "cell_type": "code", "execution_count": 4, "id": "7a798832", "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\n", "\n", "connection_string = f'postgresql://postgres:{password}@localhost:5432/northwind'\n", "\n", "%sql $connection_string" ] }, { "cell_type": "markdown", "id": "168208d2", "metadata": {}, "source": [ "#### Exploring the Northwind Database - Getting to Know the Data" ] }, { "cell_type": "code", "execution_count": 5, "id": "20cb9a01", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "14 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", "
nametype
territoriesBASE TABLE
order_detailsBASE TABLE
employee_territoriesBASE TABLE
us_statesBASE TABLE
customersBASE TABLE
ordersBASE TABLE
employeesBASE TABLE
shippersBASE TABLE
productsBASE TABLE
categoriesBASE TABLE
suppliersBASE TABLE
regionBASE TABLE
customer_demographicsBASE TABLE
customer_customer_demoBASE TABLE
" ], "text/plain": [ "[('territories', 'BASE TABLE'),\n", " ('order_details', 'BASE TABLE'),\n", " ('employee_territories', 'BASE TABLE'),\n", " ('us_states', 'BASE TABLE'),\n", " ('customers', 'BASE TABLE'),\n", " ('orders', 'BASE TABLE'),\n", " ('employees', 'BASE TABLE'),\n", " ('shippers', 'BASE TABLE'),\n", " ('products', 'BASE TABLE'),\n", " ('categories', 'BASE TABLE'),\n", " ('suppliers', 'BASE TABLE'),\n", " ('region', 'BASE TABLE'),\n", " ('customer_demographics', 'BASE TABLE'),\n", " ('customer_customer_demo', 'BASE TABLE')]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " table_name as name,\n", " table_type as type\n", "FROM information_schema.tables\n", "WHERE table_schema = 'public' AND table_type IN ('BASE TABLE', 'VIEW');\n" ] }, { "cell_type": "code", "execution_count": 6, "id": "11a35d51", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "5 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", "
customer_idcompany_namecontact_namecontact_titleaddresscityregionpostal_codecountryphonefax
ALFKIAlfreds FutterkisteMaria AndersSales RepresentativeObere Str. 57BerlinNone12209Germany030-0074321030-0076545
ANATRAna Trujillo Emparedados y heladosAna TrujilloOwnerAvda. de la Constitución 2222México D.F.None05021Mexico(5) 555-4729(5) 555-3745
ANTONAntonio Moreno TaqueríaAntonio MorenoOwnerMataderos 2312México D.F.None05023Mexico(5) 555-3932None
AROUTAround the HornThomas HardySales Representative120 Hanover Sq.LondonNoneWA1 1DPUK(171) 555-7788(171) 555-6750
BERGSBerglunds snabbköpChristina BerglundOrder AdministratorBerguvsvägen 8LuleåNoneS-958 22Sweden0921-12 34 650921-12 34 67
" ], "text/plain": [ "[('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', None, '12209', 'Germany', '030-0074321', '030-0076545'),\n", " ('ANATR', 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Owner', 'Avda. de la Constitución 2222', 'México D.F.', None, '05021', 'Mexico', '(5) 555-4729', '(5) 555-3745'),\n", " ('ANTON', 'Antonio Moreno Taquería', 'Antonio Moreno', 'Owner', 'Mataderos 2312', 'México D.F.', None, '05023', 'Mexico', '(5) 555-3932', None),\n", " ('AROUT', 'Around the Horn', 'Thomas Hardy', 'Sales Representative', '120 Hanover Sq.', 'London', None, 'WA1 1DP', 'UK', '(171) 555-7788', '(171) 555-6750'),\n", " ('BERGS', 'Berglunds snabbköp', 'Christina Berglund', 'Order Administrator', 'Berguvsvägen 8', 'Luleå', None, 'S-958 22', 'Sweden', '0921-12 34 65', '0921-12 34 67')]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM customers\n", "LIMIT 5;" ] }, { "cell_type": "code", "execution_count": 7, "id": "f1d842bf", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "5 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", "
order_idcustomer_idemployee_idorder_daterequired_dateshipped_dateship_viafreightship_nameship_addressship_cityship_regionship_postal_codeship_country
10248VINET51996-07-041996-08-011996-07-16332.38Vins et alcools Chevalier59 rue de l'AbbayeReimsNone51100France
10249TOMSP61996-07-051996-08-161996-07-10111.61Toms SpezialitätenLuisenstr. 48MünsterNone44087Germany
10250HANAR41996-07-081996-08-051996-07-12265.83Hanari CarnesRua do Paço, 67Rio de JaneiroRJ05454-876Brazil
10251VICTE31996-07-081996-08-051996-07-15141.34Victuailles en stock2, rue du CommerceLyonNone69004France
10252SUPRD41996-07-091996-08-061996-07-11251.3Suprêmes délicesBoulevard Tirou, 255CharleroiNoneB-6000Belgium
" ], "text/plain": [ "[(10248, 'VINET', 5, datetime.date(1996, 7, 4), datetime.date(1996, 8, 1), datetime.date(1996, 7, 16), 3, 32.38, 'Vins et alcools Chevalier', \"59 rue de l'Abbaye\", 'Reims', None, '51100', 'France'),\n", " (10249, 'TOMSP', 6, datetime.date(1996, 7, 5), datetime.date(1996, 8, 16), datetime.date(1996, 7, 10), 1, 11.61, 'Toms Spezialitäten', 'Luisenstr. 48', 'Münster', None, '44087', 'Germany'),\n", " (10250, 'HANAR', 4, datetime.date(1996, 7, 8), datetime.date(1996, 8, 5), datetime.date(1996, 7, 12), 2, 65.83, 'Hanari Carnes', 'Rua do Paço, 67', 'Rio de Janeiro', 'RJ', '05454-876', 'Brazil'),\n", " (10251, 'VICTE', 3, datetime.date(1996, 7, 8), datetime.date(1996, 8, 5), datetime.date(1996, 7, 15), 1, 41.34, 'Victuailles en stock', '2, rue du Commerce', 'Lyon', None, '69004', 'France'),\n", " (10252, 'SUPRD', 4, datetime.date(1996, 7, 9), datetime.date(1996, 8, 6), datetime.date(1996, 7, 11), 2, 51.3, 'Suprêmes délices', 'Boulevard Tirou, 255', 'Charleroi', None, 'B-6000', 'Belgium')]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM orders\n", "LIMIT 5;" ] }, { "cell_type": "code", "execution_count": 8, "id": "47c21508", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "5 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", "
order_idproduct_idunit_pricequantitydiscount
102481114.0120.0
10248429.8100.0
102487234.850.0
102491418.690.0
102495142.4400.0
" ], "text/plain": [ "[(10248, 11, 14.0, 12, 0.0),\n", " (10248, 42, 9.8, 10, 0.0),\n", " (10248, 72, 34.8, 5, 0.0),\n", " (10249, 14, 18.6, 9, 0.0),\n", " (10249, 51, 42.4, 40, 0.0)]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM order_details\n", "LIMIT 5;" ] }, { "cell_type": "code", "execution_count": 9, "id": "4c4afb57", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "5 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", "
product_idproduct_namesupplier_idcategory_idquantity_per_unitunit_priceunits_in_stockunits_on_orderreorder_leveldiscontinued
1Chai8110 boxes x 30 bags18.0390101
2Chang1124 - 12 oz bottles19.01740251
3Aniseed Syrup1212 - 550 ml bottles10.01370250
4Chef Anton's Cajun Seasoning2248 - 6 oz jars22.053000
5Chef Anton's Gumbo Mix2236 boxes21.350001
" ], "text/plain": [ "[(1, 'Chai', 8, 1, '10 boxes x 30 bags', 18.0, 39, 0, 10, 1),\n", " (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19.0, 17, 40, 25, 1),\n", " (3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10.0, 13, 70, 25, 0),\n", " (4, \"Chef Anton's Cajun Seasoning\", 2, 2, '48 - 6 oz jars', 22.0, 53, 0, 0, 0),\n", " (5, \"Chef Anton's Gumbo Mix\", 2, 2, '36 boxes', 21.35, 0, 0, 0, 1)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM products\n", "LIMIT 5;" ] }, { "cell_type": "markdown", "id": "83ace3dc", "metadata": {}, "source": [ "Combine `orders` and `employees` tables to see who is responsible for each order:" ] }, { "cell_type": "code", "execution_count": 10, "id": "2e6d4d90", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\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", "
employee_nameorder_idorder_date
Steven Buchanan102481996-07-04
Michael Suyama102491996-07-05
Margaret Peacock102501996-07-08
Janet Leverling102511996-07-08
Margaret Peacock102521996-07-09
Janet Leverling102531996-07-10
Steven Buchanan102541996-07-11
Anne Dodsworth102551996-07-12
Janet Leverling102561996-07-15
Margaret Peacock102571996-07-16
" ], "text/plain": [ "[('Steven Buchanan', 10248, datetime.date(1996, 7, 4)),\n", " ('Michael Suyama', 10249, datetime.date(1996, 7, 5)),\n", " ('Margaret Peacock', 10250, datetime.date(1996, 7, 8)),\n", " ('Janet Leverling', 10251, datetime.date(1996, 7, 8)),\n", " ('Margaret Peacock', 10252, datetime.date(1996, 7, 9)),\n", " ('Janet Leverling', 10253, datetime.date(1996, 7, 10)),\n", " ('Steven Buchanan', 10254, datetime.date(1996, 7, 11)),\n", " ('Anne Dodsworth', 10255, datetime.date(1996, 7, 12)),\n", " ('Janet Leverling', 10256, datetime.date(1996, 7, 15)),\n", " ('Margaret Peacock', 10257, datetime.date(1996, 7, 16))]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " e.first_name || ' ' || e.last_name as employee_name,\n", " o.order_id,\n", " o.order_date\n", "FROM orders o\n", "JOIN employees e ON o.employee_id = e.employee_id\n", "LIMIT 10;" ] }, { "cell_type": "markdown", "id": "66c9f902", "metadata": {}, "source": [ "Combine `orders` and `customers` tables to get more detailed information about each customer:" ] }, { "cell_type": "code", "execution_count": 11, "id": "5b73e928", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\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", "
order_idcompany_namecontact_nameorder_date
10248Vins et alcools ChevalierPaul Henriot1996-07-04
10249Toms SpezialitätenKarin Josephs1996-07-05
10250Hanari CarnesMario Pontes1996-07-08
10251Victuailles en stockMary Saveley1996-07-08
10252Suprêmes délicesPascale Cartrain1996-07-09
10253Hanari CarnesMario Pontes1996-07-10
10254Chop-suey ChineseYang Wang1996-07-11
10255Richter SupermarktMichael Holz1996-07-12
10256Wellington ImportadoraPaula Parente1996-07-15
10257HILARION-AbastosCarlos Hernández1996-07-16
" ], "text/plain": [ "[(10248, 'Vins et alcools Chevalier', 'Paul Henriot', datetime.date(1996, 7, 4)),\n", " (10249, 'Toms Spezialitäten', 'Karin Josephs', datetime.date(1996, 7, 5)),\n", " (10250, 'Hanari Carnes', 'Mario Pontes', datetime.date(1996, 7, 8)),\n", " (10251, 'Victuailles en stock', 'Mary Saveley', datetime.date(1996, 7, 8)),\n", " (10252, 'Suprêmes délices', 'Pascale Cartrain', datetime.date(1996, 7, 9)),\n", " (10253, 'Hanari Carnes', 'Mario Pontes', datetime.date(1996, 7, 10)),\n", " (10254, 'Chop-suey Chinese', 'Yang Wang', datetime.date(1996, 7, 11)),\n", " (10255, 'Richter Supermarkt', 'Michael Holz', datetime.date(1996, 7, 12)),\n", " (10256, 'Wellington Importadora', 'Paula Parente', datetime.date(1996, 7, 15)),\n", " (10257, 'HILARION-Abastos', 'Carlos Hernández', datetime.date(1996, 7, 16))]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " o.order_id,\n", " c.company_name,\n", " c.contact_name,\n", " o.order_date\n", "FROM orders o\n", "JOIN customers c ON o.customer_id = c.customer_id\n", "LIMIT 10;" ] }, { "cell_type": "markdown", "id": "65dc2089", "metadata": {}, "source": [ "Combine `order_details`, `products`, and `orders` to get detailed order information including the product name and quantity:" ] }, { "cell_type": "code", "execution_count": 12, "id": "eae620d2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\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", "
order_idproduct_namequantityorder_date
10248Queso Cabrales121996-07-04
10248Singaporean Hokkien Fried Mee101996-07-04
10248Mozzarella di Giovanni51996-07-04
10249Tofu91996-07-05
10249Manjimup Dried Apples401996-07-05
10250Jack's New England Clam Chowder101996-07-08
10250Manjimup Dried Apples351996-07-08
10250Louisiana Fiery Hot Pepper Sauce151996-07-08
10251Gustaf's Knäckebröd61996-07-08
10251Ravioli Angelo151996-07-08
" ], "text/plain": [ "[(10248, 'Queso Cabrales', 12, datetime.date(1996, 7, 4)),\n", " (10248, 'Singaporean Hokkien Fried Mee', 10, datetime.date(1996, 7, 4)),\n", " (10248, 'Mozzarella di Giovanni', 5, datetime.date(1996, 7, 4)),\n", " (10249, 'Tofu', 9, datetime.date(1996, 7, 5)),\n", " (10249, 'Manjimup Dried Apples', 40, datetime.date(1996, 7, 5)),\n", " (10250, \"Jack's New England Clam Chowder\", 10, datetime.date(1996, 7, 8)),\n", " (10250, 'Manjimup Dried Apples', 35, datetime.date(1996, 7, 8)),\n", " (10250, 'Louisiana Fiery Hot Pepper Sauce', 15, datetime.date(1996, 7, 8)),\n", " (10251, \"Gustaf's Knäckebröd\", 6, datetime.date(1996, 7, 8)),\n", " (10251, 'Ravioli Angelo', 15, datetime.date(1996, 7, 8))]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " o.order_id,\n", " p.product_name,\n", " od.quantity,\n", " o.order_date\n", "FROM order_details od\n", "JOIN products p ON od.product_id = p.product_id\n", "JOIN orders o ON od.order_id = o.order_id\n", "LIMIT 10;" ] }, { "cell_type": "markdown", "id": "ae99ad83-81c2-40e3-bff9-0a549cc2307b", "metadata": {}, "source": [ "#### Rank employees by sales performance" ] }, { "cell_type": "code", "execution_count": 13, "id": "dadbbfb8-7d11-4c4b-88d6-0bd5bf32c2cc", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "9 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", "
employee_idfirst_namelast_nameSales Rank
4MargaretPeacock1
3JanetLeverling2
1NancyDavolio3
2AndrewFuller4
8LauraCallahan5
7RobertKing6
9AnneDodsworth7
6MichaelSuyama8
5StevenBuchanan9
" ], "text/plain": [ "[(4, 'Margaret', 'Peacock', 1),\n", " (3, 'Janet', 'Leverling', 2),\n", " (1, 'Nancy', 'Davolio', 3),\n", " (2, 'Andrew', 'Fuller', 4),\n", " (8, 'Laura', 'Callahan', 5),\n", " (7, 'Robert', 'King', 6),\n", " (9, 'Anne', 'Dodsworth', 7),\n", " (6, 'Michael', 'Suyama', 8),\n", " (5, 'Steven', 'Buchanan', 9)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "WITH EmployeeSales AS (\n", " SELECT Employees.Employee_ID, Employees.First_Name, Employees.Last_Name,\n", " SUM(Unit_Price * Quantity * (1 - Discount)) AS \"Total Sales\"\n", " FROM Orders \n", " JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID\n", " JOIN Employees ON Orders.Employee_ID = Employees.Employee_ID\n", "\n", " GROUP BY Employees.Employee_ID\n", ")\n", "SELECT Employee_ID, First_Name, Last_Name,\n", " RANK() OVER (ORDER BY \"Total Sales\" DESC) AS \"Sales Rank\"\n", "FROM EmployeeSales;\n" ] }, { "cell_type": "markdown", "id": "6db4f6ea", "metadata": {}, "source": [ "We can see that Margeret Peacock is the top-selling employee and Steven Buchanan is the lowest-selling employee." ] }, { "cell_type": "markdown", "id": "edbf8c56-2cc9-434a-a992-bbae92d3858f", "metadata": {}, "source": [ "#### Calculate running total of sales per month" ] }, { "cell_type": "code", "execution_count": 14, "id": "8e9e175e-f00a-45ef-8829-c6b9d50422e3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "23 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", "
MonthRunning Total
1996-07-0127861.89512966156
1996-08-0153347.17020040483
1996-09-0179728.57033299239
1996-10-01117244.29527847127
1996-11-01162844.3404896083
1996-12-01208083.97098282274
1997-01-01269342.0411508011
1997-02-01307825.6761011254
1997-03-01346372.8962108522
1997-04-01399405.8485997937
1997-05-01453187.13842493534
1997-06-01489549.9407597378
1997-07-01540570.7982783426
1997-08-01587858.4679665978
1997-09-01643487.7103683471
1997-10-01710236.9361440743
1997-11-01753770.7449116395
1997-12-01825169.1733755233
1998-01-01919391.2835824591
1998-02-011018806.5709654673
1998-03-011123660.7259656242
1998-04-011247459.4082211715
1998-05-011265793.0386533642
" ], "text/plain": [ "[(datetime.date(1996, 7, 1), 27861.89512966156),\n", " (datetime.date(1996, 8, 1), 53347.17020040483),\n", " (datetime.date(1996, 9, 1), 79728.57033299239),\n", " (datetime.date(1996, 10, 1), 117244.29527847127),\n", " (datetime.date(1996, 11, 1), 162844.3404896083),\n", " (datetime.date(1996, 12, 1), 208083.97098282274),\n", " (datetime.date(1997, 1, 1), 269342.0411508011),\n", " (datetime.date(1997, 2, 1), 307825.6761011254),\n", " (datetime.date(1997, 3, 1), 346372.8962108522),\n", " (datetime.date(1997, 4, 1), 399405.8485997937),\n", " (datetime.date(1997, 5, 1), 453187.13842493534),\n", " (datetime.date(1997, 6, 1), 489549.9407597378),\n", " (datetime.date(1997, 7, 1), 540570.7982783426),\n", " (datetime.date(1997, 8, 1), 587858.4679665978),\n", " (datetime.date(1997, 9, 1), 643487.7103683471),\n", " (datetime.date(1997, 10, 1), 710236.9361440743),\n", " (datetime.date(1997, 11, 1), 753770.7449116395),\n", " (datetime.date(1997, 12, 1), 825169.1733755233),\n", " (datetime.date(1998, 1, 1), 919391.2835824591),\n", " (datetime.date(1998, 2, 1), 1018806.5709654673),\n", " (datetime.date(1998, 3, 1), 1123660.7259656242),\n", " (datetime.date(1998, 4, 1), 1247459.4082211715),\n", " (datetime.date(1998, 5, 1), 1265793.0386533642)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- Exe 1\n", "WITH MonthlySales AS (\n", " SELECT DATE_TRUNC('month', Order_Date)::DATE AS \"Month\", \n", " SUM(Unit_Price * Quantity * (1 - Discount)) AS \"Total Sales\"\n", " FROM Orders \n", " JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID\n", " GROUP BY DATE_TRUNC('month', Order_Date)\n", ")\n", "SELECT \"Month\", \n", " SUM(\"Total Sales\") OVER (ORDER BY \"Month\") AS \"Running Total\"\n", "FROM MonthlySales\n", "ORDER BY \"Month\";" ] }, { "cell_type": "markdown", "id": "d5166f84-40eb-4b26-ab40-c902b3612c1a", "metadata": {}, "source": [ "#### Calculate the month-over-month sales growth rate" ] }, { "cell_type": "code", "execution_count": 15, "id": "8c0c051c-7e44-4746-8a6d-8c2d73a624f3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "23 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", "
yearmonthGrowth Rate
19967None
19968-8.530001451294545
199693.51624637896504
19961042.20520805162909
19961121.54915112904513
199612-0.7903823696967553
1997135.40798079057388
19972-37.17785290199861
199730.16522649038887202
1997437.579187910257275
199751.4110800973551207
19976-32.38763433709323
1997740.31057631048775
19978-7.316983704141531
1997917.64005874784288
19971019.98945679265288
199711-34.780054357730286
19971264.00685004404939
1998131.966644412344674
199825.511633272346428
199835.47085640480519
1998418.06750267107856
19985-85.1907709370056
" ], "text/plain": [ "[(Decimal('1996'), Decimal('7'), None),\n", " (Decimal('1996'), Decimal('8'), -8.530001451294545),\n", " (Decimal('1996'), Decimal('9'), 3.51624637896504),\n", " (Decimal('1996'), Decimal('10'), 42.20520805162909),\n", " (Decimal('1996'), Decimal('11'), 21.54915112904513),\n", " (Decimal('1996'), Decimal('12'), -0.7903823696967553),\n", " (Decimal('1997'), Decimal('1'), 35.40798079057388),\n", " (Decimal('1997'), Decimal('2'), -37.17785290199861),\n", " (Decimal('1997'), Decimal('3'), 0.16522649038887202),\n", " (Decimal('1997'), Decimal('4'), 37.579187910257275),\n", " (Decimal('1997'), Decimal('5'), 1.4110800973551207),\n", " (Decimal('1997'), Decimal('6'), -32.38763433709323),\n", " (Decimal('1997'), Decimal('7'), 40.31057631048775),\n", " (Decimal('1997'), Decimal('8'), -7.316983704141531),\n", " (Decimal('1997'), Decimal('9'), 17.64005874784288),\n", " (Decimal('1997'), Decimal('10'), 19.98945679265288),\n", " (Decimal('1997'), Decimal('11'), -34.780054357730286),\n", " (Decimal('1997'), Decimal('12'), 64.00685004404939),\n", " (Decimal('1998'), Decimal('1'), 31.966644412344674),\n", " (Decimal('1998'), Decimal('2'), 5.511633272346428),\n", " (Decimal('1998'), Decimal('3'), 5.47085640480519),\n", " (Decimal('1998'), Decimal('4'), 18.06750267107856),\n", " (Decimal('1998'), Decimal('5'), -85.1907709370056)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "WITH MonthlySales AS (\n", " SELECT EXTRACT('month' from Order_Date) AS Month, \n", " EXTRACT('year' from Order_Date) AS Year, \n", " SUM(Unit_Price * Quantity * (1 - Discount)) AS TotalSales\n", " FROM Orders \n", " JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID\n", " GROUP BY EXTRACT('month' from Order_Date), EXTRACT('year' from Order_Date)\n", "),\n", "LaggedSales AS (\n", " SELECT Month, Year, \n", " TotalSales, \n", " LAG(TotalSales) OVER (ORDER BY Year, Month) AS PreviousMonthSales\n", " FROM MonthlySales\n", ")\n", "SELECT Year, Month,\n", " ((TotalSales - PreviousMonthSales) / PreviousMonthSales) * 100 AS \"Growth Rate\"\n", "FROM LaggedSales;\n" ] }, { "cell_type": "markdown", "id": "79ca1e3b-26a5-4701-9217-b77b4f494cd5", "metadata": {}, "source": [ "#### Identify customers with above-average order values" ] }, { "cell_type": "code", "execution_count": 16, "id": "5df0d97a-15c8-42db-911d-7cbe7ce2502e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\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", "
customer_idorder_idOrder ValueValue Category
VINET10248439.99999809265137Below Average
TOMSP102491863.4000644683838Above Average
HANAR102501552.600023412704Above Average
VICTE10251654.0599855789542Below Average
SUPRD102523597.9001445159315Above Average
HANAR102531444.7999839782715Below Average
CHOPS10254556.62000967741Below Average
RICSU102552490.4999780654907Above Average
WELLI10256517.8000068664551Below Average
HILAA102571119.899953842163Below Average
" ], "text/plain": [ "[('VINET', 10248, 439.99999809265137, 'Below Average'),\n", " ('TOMSP', 10249, 1863.4000644683838, 'Above Average'),\n", " ('HANAR', 10250, 1552.600023412704, 'Above Average'),\n", " ('VICTE', 10251, 654.0599855789542, 'Below Average'),\n", " ('SUPRD', 10252, 3597.9001445159315, 'Above Average'),\n", " ('HANAR', 10253, 1444.7999839782715, 'Below Average'),\n", " ('CHOPS', 10254, 556.62000967741, 'Below Average'),\n", " ('RICSU', 10255, 2490.4999780654907, 'Above Average'),\n", " ('WELLI', 10256, 517.8000068664551, 'Below Average'),\n", " ('HILAA', 10257, 1119.899953842163, 'Below Average')]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "WITH OrderValues AS (\n", " SELECT Orders.Customer_ID, \n", " Orders.Order_ID, \n", " SUM(Unit_Price * Quantity * (1 - Discount)) AS \"Order Value\"\n", " FROM Orders \n", " JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID\n", " GROUP BY Orders.Customer_ID, Orders.Order_ID\n", ")\n", "SELECT Customer_ID, \n", " Order_ID, \n", " \"Order Value\",\n", " CASE \n", " WHEN \"Order Value\" > AVG(\"Order Value\") OVER () THEN 'Above Average'\n", " ELSE 'Below Average'\n", " END AS \"Value Category\"\n", "FROM OrderValues LIMIT 10;" ] }, { "cell_type": "markdown", "id": "bb025688-fc4f-42ab-b5d9-f46f05a61702", "metadata": {}, "source": [ "#### Calculate the percentage of total sales for each product category" ] }, { "cell_type": "code", "execution_count": 17, "id": "be6b4eba-3bc8-44e4-9c4c-cc0483fb7736", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "8 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", "
category_idcategory_nameSales Percentage
8Seafood10.195732374296789
7Produce7.813322138303922
1Beverages21.331025404054813
5Grains/Cereals7.510473482122698
2Condiments8.400470714786334
4Dairy Products18.556754766640605
6Meat/Poultry12.902483709246834
3Confections13.289737410548023
" ], "text/plain": [ "[(8, 'Seafood', 10.195732374296789),\n", " (7, 'Produce', 7.813322138303922),\n", " (1, 'Beverages', 21.331025404054813),\n", " (5, 'Grains/Cereals', 7.510473482122698),\n", " (2, 'Condiments', 8.400470714786334),\n", " (4, 'Dairy Products', 18.556754766640605),\n", " (6, 'Meat/Poultry', 12.902483709246834),\n", " (3, 'Confections', 13.289737410548023)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "WITH CategorySales AS (\n", " SELECT Categories.Category_ID, Categories.Category_Name,\n", " SUM(Products.Unit_Price * Quantity * (1 - Discount)) AS \"Total Sales\"\n", " FROM Categories\n", " JOIN Products ON Categories.Category_ID = Products.Category_ID\n", " JOIN Order_Details ON Products.Product_ID = Order_Details.Product_ID\n", " GROUP BY Categories.Category_ID\n", ")\n", "SELECT Category_ID, Category_Name,\n", " \"Total Sales\" / SUM(\"Total Sales\") OVER () * 100 AS \"Sales Percentage\"\n", "FROM CategorySales;" ] }, { "cell_type": "markdown", "id": "8df728df", "metadata": {}, "source": [ "Beverages is the top category in terms of sales percentages, followed closely by Dairy Products. Produce and Grains/Cereals are the categories with the smallest sales percentage." ] }, { "cell_type": "markdown", "id": "e4a23afd-0407-4513-812e-311b0a3f65a2", "metadata": {}, "source": [ "#### Find the top 3 products sold in each category" ] }, { "cell_type": "code", "execution_count": 18, "id": "cae98dcd-1cec-4847-a73b-553b8d1f41aa", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost:5432/northwind\n", "24 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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
category_idproduct_idproduct_nameTotal Sales
138Côte de Blaye153897.1748863291
143Ipoh Coffee25109.09997367859
12Chang17719.399970583618
263Vegie-spread18343.61561246872
261Sirop d'érable15022.349960759282
265Louisiana Fiery Hot Pepper Sauce14893.926944906489
362Tarte au sucre50737.09416846588
320Sir Rodney's Marmalade24199.559986554086
326Gumbär Gummibärchen21662.689146941742
459Raclette Courdavault76683.74989898875
460Camembert Pierrot49877.31995112449
472Mozzarella di Giovanni27086.57939014256
556Gnocchi di nonna Alice45351.09995948523
564Wimmers gute Semmelknödel23487.467487137765
542Singaporean Hokkien Fried Mee8986.599987879395
629Thüringer Rostbratwurst84783.77159642408
617Alice Mutton35105.849979020655
653Perth Pasties22623.799456167217
751Manjimup Dried Apples43846.89994909987
728Rössle Sauerkraut27936.839044377804
77Uncle Bob's Organic Dried Pears22453.49998757243
818Carnarvon Tigers30728.12496125698
810Ikura21653.499964892864
840Boston Crab Meat19055.039585784674
" ], "text/plain": [ "[(1, 38, 'Côte de Blaye', 153897.1748863291),\n", " (1, 43, 'Ipoh Coffee', 25109.09997367859),\n", " (1, 2, 'Chang', 17719.399970583618),\n", " (2, 63, 'Vegie-spread', 18343.61561246872),\n", " (2, 61, \"Sirop d'érable\", 15022.349960759282),\n", " (2, 65, 'Louisiana Fiery Hot Pepper Sauce', 14893.926944906489),\n", " (3, 62, 'Tarte au sucre', 50737.09416846588),\n", " (3, 20, \"Sir Rodney's Marmalade\", 24199.559986554086),\n", " (3, 26, 'Gumbär Gummibärchen', 21662.689146941742),\n", " (4, 59, 'Raclette Courdavault', 76683.74989898875),\n", " (4, 60, 'Camembert Pierrot', 49877.31995112449),\n", " (4, 72, 'Mozzarella di Giovanni', 27086.57939014256),\n", " (5, 56, 'Gnocchi di nonna Alice', 45351.09995948523),\n", " (5, 64, 'Wimmers gute Semmelknödel', 23487.467487137765),\n", " (5, 42, 'Singaporean Hokkien Fried Mee', 8986.599987879395),\n", " (6, 29, 'Thüringer Rostbratwurst', 84783.77159642408),\n", " (6, 17, 'Alice Mutton', 35105.849979020655),\n", " (6, 53, 'Perth Pasties', 22623.799456167217),\n", " (7, 51, 'Manjimup Dried Apples', 43846.89994909987),\n", " (7, 28, 'Rössle Sauerkraut', 27936.839044377804),\n", " (7, 7, \"Uncle Bob's Organic Dried Pears\", 22453.49998757243),\n", " (8, 18, 'Carnarvon Tigers', 30728.12496125698),\n", " (8, 10, 'Ikura', 21653.499964892864),\n", " (8, 40, 'Boston Crab Meat', 19055.039585784674)]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "WITH ProductSales AS (\n", " SELECT Products.Category_ID, \n", " Products.Product_ID, Products.Product_Name,\n", " SUM(Products.Unit_Price * Quantity * (1 - Discount)) AS \"Total Sales\"\n", " FROM Products\n", " JOIN Order_Details ON Products.Product_ID = Order_Details.Product_ID\n", " GROUP BY Products.Category_ID, Products.Product_ID\n", ")\n", "SELECT Category_ID, \n", " Product_ID, Product_Name,\n", " \"Total Sales\"\n", "FROM (\n", " SELECT Category_ID, \n", " Product_ID, Product_Name,\n", " \"Total Sales\", \n", " ROW_NUMBER() OVER (PARTITION BY Category_ID ORDER BY \"Total Sales\" DESC) AS rn\n", " FROM ProductSales\n", ") tmp\n", "WHERE rn <= 3;\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.11.3" } }, "nbformat": 4, "nbformat_minor": 5 }