SQLite: select top n products from each category

sqlite_top_n.py

"""
Table 'Products' contains different products, their categories and prices.
We want to select top n products per each category rated by their price
SOLUTION: Window function RANK()

1. TABLE 'Products'
┌───────────┬────────────┬───────┐
│  product  │  category  │ price │
├───────────┼────────────┼───────┤
│ product_A │ category_A │ 55    │
│ product_B │ category_A │ 67    │
│ product_C │ category_A │ 12    │
│ product_D │ category_A │ 34    │
│ product_E │ category_A │ 101   │
│ product_F │ category_A │ 87    │
│ product_G │ category_A │ 43    │
│ product_A │ category_B │ 99    │
│ product_B │ category_B │ 79    │
│ product_C │ category_B │ 67    │
│ product_D │ category_B │ 122   │
│ product_E │ category_B │ 71    │
│ product_F │ category_B │ 38    │
│ product_G │ category_B │ 98    │
│ product_A │ category_C │ 19    │
│ product_B │ category_C │ 34    │
│ product_C │ category_C │ 51    │
│ product_D │ category_C │ 74    │
│ product_E │ category_C │ 96    │
│ product_F │ category_C │ 108   │
└───────────┴────────────┴───────┘

2. SQL
SELECT * FROM (
    SELECT *,
        RANK () OVER (PARTITION BY category ORDER BY price DESC) AS R
    FROM Products
    ORDER BY category
    )
WHERE R <= 2;

3. RESULT
┌───────────┬────────────┬───────┬───┐
│  product  │  category  │ price │ R │
├───────────┼────────────┼───────┼───┤
│ product_E │ category_A │ 101   │ 1 │
│ product_F │ category_A │ 87    │ 2 │
│ product_D │ category_B │ 122   │ 1 │
│ product_A │ category_B │ 99    │ 2 │
│ product_F │ category_C │ 108   │ 1 │
│ product_E │ category_C │ 96    │ 2 │
└───────────┴────────────┴───────┴───┘
"""

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute("CREATE TABLE Products (product TEXT, category TEXT, price INTEGER);")
cursor.execute("""
    INSERT INTO Products VALUES
    ('product_A', 'category_A', 55),
    ('product_B', 'category_A', 67),
    ('product_C', 'category_A', 12),
    ('product_D', 'category_A', 34),
    ('product_E', 'category_A', 101),
    ('product_F', 'category_A', 87),
    ('product_G', 'category_A', 43),
    ('product_A', 'category_B', 99),
    ('product_B', 'category_B', 79),
    ('product_C', 'category_B', 67),
    ('product_D', 'category_B', 122),
    ('product_E', 'category_B', 71),
    ('product_F', 'category_B', 38),
    ('product_G', 'category_B', 98),
    ('product_A', 'category_C', 19),
    ('product_B', 'category_C', 34),
    ('product_C', 'category_C', 51),
    ('product_D', 'category_C', 74),
    ('product_E', 'category_C', 96),
    ('product_F', 'category_C', 108);
""")

cursor.execute("""
    SELECT * FROM (
        SELECT *,
            RANK () OVER (PARTITION BY category ORDER BY price DESC) AS R
        FROM Products
        ORDER BY category
        )
    WHERE R <= 2;
""").fetchall()