Junior

CREATE TABLE orders ( driver_id varchar, city varchar, order_id varchar ); -- Get the top 10 drivers by number of orders in each city.

sobes.tech AI

Answer from AI

To get the top 10 drivers by the number of orders in each city, you can use the window functions ROW_NUMBER() or RANK() in SQL. Example query:

SELECT driver_id, city, order_count
FROM (
    SELECT
        driver_id,
        city,
        COUNT(order_id) AS order_count,
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY COUNT(order_id) DESC) AS rn
    FROM orders
    GROUP BY city, driver_id
) sub
WHERE rn <= 10
ORDER BY city, order_count DESC;

Here, we group orders by driver and city, count the number of orders, then assign a rank to drivers within each city based on the number of orders in descending order, and select only the top 10.

Previous Next