ICA 6

Database setup and reference

Download northwind.db using the Download here link in the Northwind SQLite repository.

Tables and columns used

Table What one row represents Relevant columns
Products One product ProductID: identifier; ProductName: name; CategoryID: category; UnitPrice: catalog price
Categories One product category CategoryID: identifier; CategoryName: category label
Orders One customer order OrderID: identifier; CustomerID: customer code; OrderDate: order date/time; Freight: order shipping charge
"Order Details" One product line within an order OrderID, ProductID: identify the line; UnitPrice: price on that order; Quantity: units ordered; Discount: fractional discount

Relationships:

Products.CategoryID joins to Categories.CategoryID

Orders.OrderID joins to "Order Details".OrderID.

Revenue definition: For this assignment, revenue is merchandise sales after discounts, excluding freight and taxes:

UnitPrice * Quantity * (1 - Discount)

A discount of 0.10 means 10%. Retain precision during calculations and round monetary output to two decimal places.

Problem 1: Compare products within categories

How does each product’s catalog price compare with the average price in its category?

  1. Join Products to Categories and retain one row per eligible product.
  2. Return ProductID, ProductName, CategoryID, CategoryName, and UnitPrice.
  3. Add category_avg_price using a window average partitioned by category.
  4. Add price_difference: product price minus its category’s average price. Round both calculated columns to two decimals.
  5. Order results by CategoryID, then ProductID.

Problem 2: Rank products and investigate ties

Which products are the most expensive within each category?

Part A: Compare rankings

Return ProductID, ProductName, CategoryID, and UnitPrice, plus:

  • price_row_number: ROW_NUMBER() ordered by price descending, then ProductID ascending, within category.
  • price_rank: RANK() ordered by price descending within category.
  • price_dense_rank: DENSE_RANK() ordered by price descending within category.

Order by CategoryID, descending UnitPrice, then ProductID.

Part B: Select leading price levels

Put the ranked result in a CTE. Return products in the two highest distinct price levels per category, keeping every tie. Display the three ranking columns too.

Problem 3: Compare orders with LAG

How does each order’s freight charge compare with the preceding eligible order from the same customer?

  1. Use all eligible orders, excluding missing freight charges.
  2. Return CustomerID, OrderID, OrderDate, and Freight.
  3. Add previous_freight using LAG(Freight) within each customer, ordered by OrderDate, OrderID.
  4. Add freight_change: current freight minus previous freight, rounded to two decimals. Use a CTE to reuse previous_freight.
  5. Order by CustomerID, OrderDate, then OrderID.

Problem 4: Find the next order with LEAD

How long does each customer wait until their next order?

  1. Return CustomerID, OrderID, and OrderDate for all eligible orders.
  2. Add next_order_date from the next order for the same customer, ordered by OrderDate, OrderID.
  3. In an outer query, calculate days_until_next, rounded to two decimals.
  4. Keep each customer’s final order. Order by CustomerID, OrderDate, then OrderID.

SQLite date helper: JULIANDAY(later_date) - JULIANDAY(earlier_date) gives elapsed days, potentially including fractions.

Probelm 5: Track revenue through time

How are cumulative sales and recent daily sales changing?

Use UnitPrice from "Order Details" for historical sales, rather than the catalog price in Products. Use this supplied CTE to obtain one row per observed order date. Replace the final SELECT with your window calculations.

WITH daily_sales AS (
    SELECT DATE(o.OrderDate) AS order_day,
           SUM(d.UnitPrice * d.Quantity * (1.0 - d.Discount))
               AS daily_revenue
    FROM Orders AS o
    JOIN "Order Details" AS d ON o.OrderID = d.OrderID
    WHERE o.OrderDate IS NOT NULL
    GROUP BY DATE(o.OrderDate)
)
  1. Return order_day and daily_revenue.
  2. Add running_revenue using SUM() from the first daily row through the current row. Write the frame explicitly.
  3. Add moving_avg_3_rows using AVG() over the current row and up to two preceding rows.
  4. Round all three monetary columns to two decimals. Display dates ascending.