SQL task: list user IDs and the number of their orders with status COMPLETED, including users with zero such orders.

To display the IDs of all users and the number of their orders with the status COMPLETED, including users without such orders, you should use a LEFT JOIN between the users table and the orders table, then group by the user.

Example SQL query:

SELECT u.id AS user_id, 
       COUNT(o.id) AS completed_orders_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'COMPLETED'
GROUP BY u.id
ORDER BY u.id;

Explanation:

• LEFT JOIN ensures that all users are included in the result, even if they have no orders with the specified status.
• The condition for the COMPLETED status is placed in the ON clause to avoid excluding users without such orders.
• COUNT(o.id) counts the number of orders with the specified status; for users without such orders, the count will be 0.