Search⌘ K
AI Features
Home
Courses
Grokking the Data Science Interview

Ranking Functions

Explore how to accurately retrieve the top two products by revenue within each category using SQL ranking functions. Understand the differences between ROW_NUMBER, RANK, and DENSE_RANK, and learn how to handle ties, apply deterministic ordering, and correctly filter results by staging ranked outputs in queries.

We'll cover the following...

The interviewer asks, “For each product category, return the two products with the highest revenue during the previous calendar week.” The result must contain one row for each selected product within its category, with up to two products per category. Grouping only by category calculates total revenue per category but does not provide the product-level revenue needed to rank individual products.

The synthetic orders(order_id, product_id, category, revenue, order_date) table contains multiple orders per product, so the first stage aggregates revenue at the category and product_id grain. We then need a window function, a calculation that examines related result rows while preserving each row. OVER (...) defines the rows available to that calculation. PARTITION BY category creates a partition, the separate set of product rows ranked within each category.

The ranking order must use total product revenue descending. When two products have the same revenue, the business request must tell us whether two rows are enough or whether every tied product belongs in the result.

python310
import sqlite3
def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE orders (
          order_id   TEXT NOT NULL,
          product_id TEXT NOT NULL,
          category   TEXT NOT NULL,
          revenue    INTEGER NOT NULL,
          order_date TEXT NOT NULL
        );
        """
    )
    # Synthetic orders include multiple orders per product and tied product-level revenues
    # within a category over the last-week window.
    orders = [
        # Window: 2024-06-03 <= order_date < 2024-06-10
        # books product totals: book_10=120, book_17=100, book_22=100, book_30=80
        ("o001", "book_10", "books", 70, "2024-06-04"),
        ("o002", "book_10", "books", 50, "2024-06-06"),
        ("o003", "book_17", "books", 40, "2024-06-05"),
        ("o004", "book_17", "books", 60, "2024-06-08"),
        ("o005", "book_22", "books", 100, "2024-06-07"),
        ("o006", "book_30", "books", 80, "2024-06-09"),
        # electronics product totals: elec_01=200, elec_02=150, elec_03=150, elec_04=90
        ("o101", "elec_01", "electronics", 200, "2024-06-03"),
        ("o102", "elec_02", "electronics", 100, "2024-06-04"),
        ("o103", "elec_02", "electronics", 50, "2024-06-07"),
        ("o104", "elec_03", "electronics", 150, "2024-06-06"),
        ("o105", "elec_04", "electronics", 90, "2024-06-08"),
        # Outside window rows to prove the date filter is doing work
        ("o901", "book_10", "books", 999, "2024-06-10"),
        ("o902", "elec_01", "electronics", 999, "2024-06-02"),
    ]
    cur.executemany(
        "INSERT INTO orders(order_id, product_id, category, revenue, order_date) VALUES (?, ?, ?, ?, ?);",
        orders,
    )
    query = """
    WITH product_revenue AS (
      SELECT
        category,
        product_id,
        SUM(revenue) AS product_revenue
      FROM orders
      WHERE order_date >= '2024-06-03'
        AND order_date <  '2024-06-10'
      GROUP BY category, product_id
    )
    SELECT
      category,
      product_id,
      product_revenue,
      ROW_NUMBER() OVER (
        PARTITION BY category
        ORDER BY product_revenue DESC, product_id ASC
      ) AS row_num,
      RANK() OVER (
        PARTITION BY category
        ORDER BY product_revenue DESC
      ) AS rnk,
      DENSE_RANK() OVER (
        PARTITION BY category
        ORDER BY product_revenue DESC
      ) AS dense_rnk
    FROM product_revenue
    ORDER BY category, row_num;
    """
    rows = cur.execute(query).fetchall()
    print("category\tproduct_id\tproduct_revenue\trow_num\trnk\tdense_rnk")
    for r in rows:
        print(
            f"{r['category']}\t{r['product_id']}\t{r['product_revenue']}\t{r['row_num']}\t{r['rnk']}\t{r['dense_rnk']}"
        )
if __name__ == "__main__":
    main()

  • Lines 23–41: The inserted data is shaped so that multiple order-grain rows roll up to one product row, and at least one category has a product-level revenue tie (e.g., books has two products at 100) so RANK() vs DENSE_RANK() differences are visible.

  • Lines 49–58: Revenue is first aggregated at the required output grain for ranking (category × product_id) before any window function runs.

  • ...