Objective

Write a SQL query to identify the top three cities by number of completed trade orders.

Table Schema:

• trades

• users

Task Requirements:

• Filter only trades with status 'Completed'
• Join trades with user details to get city information
• Count the number of completed orders per city
• Return the top three cities with the highest order counts
• Sort results in descending order by order count
  
  

Output columns: city, total_orders

SELECT 
    u.city,
    COUNT(t.order_id) AS total_orders
FROM trades t
INNER JOIN users u ON t.user_id = u.user_id
WHERE t.status = 'Completed'
GROUP BY u.city
ORDER BY total_orders DESC
LIMIT 3;

Explanation

Step 1: Join Trades and Users

Use INNER JOIN to connect trades with user information, linking on user_id.

FROM trades t
INNER JOIN users u ON t.user_id = u.user_id

Step 2: Filter for Completed Trades

Apply WHERE t.status = 'Completed' to include only completed trades.

WHERE t.status = 'Completed'

Step 3: Count Orders per City

Group by city and count order_id to get the total completed orders per city.

COUNT(t.order_id) AS total_orders
GROUP BY u.city

Step 4: Return Top 3 Cities

Sort by total_orders descending and limit to 3 rows.

ORDER BY total_orders DESC
LIMIT 3