Showcase: Northwind Traders Company

Case Introduction

  • Northwind Traders is a company that imports and exports food globally. The database captures all the sales transactions that occurs between the company i.e. Northwind traders and its customers as well as the purchase transactions between Northwind and its suppliers.

  • The following queries are examples responding to the requests from different departments in Northwind to facilitate their decision making processes.

the Entity Relationship Diagram

1. Human Resource Department

1.1 Request - sales performance analysis

Provide a chart with the list of employee name, the number of orders they have processed, the percentage of total order volume that employee has contributed to, and also the difference between their order number and the average orders per employee. Categorize employees with under 50 orders as Associates, 51-100 orders as Senior Associates, and 101+ as Principals. Order by the number of orders processed per employee in descending order.

with t1 as (select o.employeeid, concat(e.firstname, ' ', e.lastname) fullname,
                   count(o.orderid) orders
            from orders o
            join employees e
            on o.employeeid = e.employeeid
            group by 1,2)

select *, round(orders /(select sum(orders)
                        from t1)::numeric,2) pct_of_order,
       round(orders - (select avg(orders)
                       from t1)::numeric,2) order_differential,
        case when orders < 50 then 'Associates'
             when orders <100 then 'Senior Associates'
             else 'Principals' end as title
from t1
order by 3 desc;
employeeid fullname orders pct_of_order order_differential title
4 Margaret Peacock 156 0.19 63.78 Principals
3 Janet Leverling 127 0.15 34.78 Principals
1 Nancy Davolio 123 0.15 30.78 Principals
8 Laura Callahan 104 0.13 11.78 Principals
2 Andrew Fuller 96 0.12 3.78 Senior Associates
7 Robert King 72 0.09 -20.22 Senior Associates
6 Michael Suyama 67 0.08 -25.22 Senior Associates
9 Anne Dodsworth 43 0.05 -49.22 Associates
5 Steven Buchanan 42 0.05 -50.22 Associates



2. Sales Department

2.1 Request - sales order analysis

There is a sales discount policy regarding the late orders that after the 1st late order for a customer, sales department gives a 20% refund for all subsequent late orders per customer. The 20% refund is applied to the total order value (quantity x unitprice). Provide a chart calculating amount of refunds Northwind has paid per productname in total as a result of late orders. Order results by total_value_of_late_orders in descending order.

with t2 as (select orderid
            from (select orderid, customerid, 
                 row_number() over (partition by customerid order by orderdate)
                 from orders
                 where shippeddate > requireddate) as t1
            where row_number != 1)


select p.productname, sum(od.unitprice * od.quantity)::int total_value_of_late_orders, 
       (sum(od.unitprice * od.quantity) * 0.2)::int total_refunded_value
from orderdetails od
join t2
on od.orderid = t2.orderid
join products p
on od.productid = p.productid
group by p.productname
order by 2 desc
limit 10;
productname total_value_of_late_orders total_refunded_value
Mishi Kobe Niku 6402 1280
Schoggi Schokolade 5268 1054
Camembert Pierrot 3359 672
Northwoods Cranberry Sauce 1600 320
Manjimup Dried Apples 1484 297
Rössle Sauerkraut 1368 274
Thüringer Rostbratwurst 1238 248
Ikura 1085 217
Vegie-spread 1054 211
Pavlova 873 175


2.2 Request - customer analysis

For German customers, provide a list in chronological order their order IDs, order dates, order totals (quantity x unitprice with discount applied), running order total, and average order total. Sort by average order total in descending order.

with t1 as (select o.orderid, round(sum(od.unitprice*od.quantity*(1-od.discount))::numeric,3) order_total
from orderdetails od
join orders o
on od.orderid = o.orderid
join customers c
on o.customerid = c.customerid
where c.country = 'Germany'
group by o.orderid
order by 1)

select t1.orderid, o.orderdate, t1.order_total,
       round(sum(t1.order_total) over (order by o.orderdate),2) running_total,
       round(avg(t1.order_total) over (order by o.orderdate),3) average_order_total
from t1
join orders o
on t1.orderid = o.orderid
order by 5 desc
limit 10;
orderid orderdate order_total running_total average_order_total
10267 1996-07-29 3536.600 6904.65 2301.550
10273 1996-08-05 2037.280 8941.93 2235.483
10277 1996-08-09 1200.800 10142.73 2028.546
10893 1998-02-18 5502.110 197326.39 1953.727
10929 1998-03-05 1174.750 198501.14 1946.090
10938 1998-03-10 2731.875 201733.02 1939.741
10865 1998-02-02 16387.500 189916.35 1937.922
10878 1998-02-10 1539.000 191455.35 1933.892
10934 1998-03-09 500.000 199001.14 1932.050
10945 1998-03-12 245.000 201978.02 1923.600



3. Warehouse Department

3.1 Request - product analysis

Provide a list of the product categories that have the above average line item total discounted value (this is defined as discount * unitprice * quantity at the order details level). Sort the results by the product category alphabetically.

select categoryname, avg_line_item_discount
from (select c.categoryname, round(avg(od.discount*od.unitprice*od.quantity)::numeric, 2)avg_line_item_discount
      from orderdetails od
      join products p
      on od.productid = p.productid
      join categories c
      on p.categoryid = c.categoryid
      group by c.categoryname) table1           --Subquery1: calculate the average discounted value for each category
where avg_line_item_discount > (select avg(discount*unitprice*quantity)
                                from orderdetails) --Subquery2: calculate the average discounted value for all orders
order by 1;
categoryname avg_line_item_discount
Beverages 46.19
Dairy Products 45.97
Meat/Poultry 87.67