sqlintermediate10 minutes

Fix the JOIN Logic to Retrieve Customers With Recent Orders

You are given a SQL query intended to list all customers who have placed orders in the last 30 days. However, the query returns incorrect results, including customers without recent orders or missing some who should appear. Your task is to identify and fix the bugs related to the JOIN and WHERE conditions so that only customers with orders in the last 30 days are retrieved correctly.

Challenge prompt

The provided SQL query is meant to retrieve customer IDs and names for those who have placed at least one order within the last 30 days from today. It uses orders and customers tables but produces incorrect results due to buggy JOIN and filtering logic. Your task is to fix the query so it correctly lists all and only customers with orders within the last 30 days. Tables: - customers(customer_id, name) - orders(order_id, customer_id, order_date) Query to fix: SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';

Guidance

• Check how the WHERE clause interacts with the LEFT JOIN and affects filtering results.
• Consider whether LEFT JOIN is appropriate or if INNER JOIN would yield correct results based on the filter condition.
• Make sure to correctly filter on order_date while preserving intended customers.

Hints

• A LEFT JOIN with a WHERE condition on the right table can convert the join effectively into an INNER JOIN, but in this case, it is not filtering as expected.
• Try changing the JOIN type or move the date condition inside the JOIN ON clause.
• Test your fixed query by confirming it does not include customers without recent orders.

Starter code

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';

Expected output

Returns customer_id and name only for customers who have orders placed within the last 30 days. No customers without recent orders should appear.

Core concepts

SQL JOINsFiltering with WHEREDate arithmeticLEFT JOIN vs INNER JOIN

Challenge a Friend

Send this duel to someone else and see if they can solve it.