Products Sold but Never Returned
Find active products (discontinued = 0) that were ordered at least once in 2025 (by orders.order_date) and have never been returned (no row in returns for that product, at any date). Some returns rows have a NULL product_id because the item could not be identified; those rows refer to no product.
Return columns product_id, name. Row order does not matter.
Schema
CREATE TABLE products ( product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, discontinued INTEGER NOT NULL ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, order_date TEXT NOT NULL ); CREATE TABLE order_items ( order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL ); CREATE TABLE returns ( return_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER );
Sample data
products
| product_id | name | discontinued |
|---|---|---|
| 1 | Trail Shoes | 0 |
| 2 | Running Socks | 0 |
| 3 | Hydration Vest | 0 |
| 4 | Old Cap | 1 |
| 5 | Headlamp | 0 |
orders
| order_id | order_date |
|---|---|
| 100 | 2025-03-01 |
| 101 | 2025-07-15 |
| 102 | 2026-01-10 |
order_items
| order_id | product_id | quantity |
|---|---|---|
| 100 | 1 | 1 |
| 100 | 2 | 3 |
| 101 | 3 | 1 |
| 101 | 4 | 2 |
| 102 | 5 | 1 |
returns
| return_id | order_id | product_id |
|---|---|---|
| 1 | 100 | 1 |
Hint 1
EXCEPT removes from the first query every row that appears in the second.
Hint 2
NOT IN (subquery) returns no rows at all if the subquery contains a NULL. EXCEPT or NOT EXISTS avoids that.
Good practice for: Data Analyst, Backend Developer, Database Administrator, Business Analyst
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.