How would you retrieve the top two orders by amount for each store for a given month?

To retrieve the top two orders by amount for each store for a specific month, you can use a window function with ROW_NUMBER() and filter on the date range. Assuming a table orders with columns store_id, order_id, amount, and order_date, the query would be:

SELECT store_id, order_id, amount, order_date

FROM (

SELECT
    store_id,
    order_id,
    amount,
    order_date,
    ROW_NUMBER() OVER (
        PARTITION BY store_id
        ORDER BY amount DESC
    ) AS rn
FROM orders
WHERE order_date BETWEEN '2023-08-01' AND '2023-08-31'

) t

WHERE rn <= 2

ORDER BY store_id, rn;

The inner query partitions the data by store and orders each partition by amount descending, assigning a row number. The outer query keeps only the first two rows per store. The date filter ensures only orders from the desired month are considered. Complexity is O(n log n) due to sorting within each partition. If the dataset is large, indexing on store_id and order_date can improve performance. This pattern scales to any N‑th top rows per group by changing the window function or the filter condition.