Customers in both lists (INTERSECT)
Return each customer who has at least one DELIVERED order and also at least one order with any other status. Show each customer only once.
INTERSECT works in SQLite, PostgreSQL and MySQL 8.0.31 or later. A join or EXISTS also works.
Rows can come back in any order. Column names do not matter; the values do.
Sample database: Company: departments, employees and orders
Three tables. departments (id, name, city). employees (id, name, email, dept_id, manager_id, salary, bonus, hire_date): dept_id and manager_id may be NULL, bonus may be NULL, hire_date is text in the form YYYY-MM-DD. orders (id, employee_id, customer, amount, status, order_date): employee_id is the sales rep (an employee id), amount may be NULL, status is DELIVERED, SHIPPED, PENDING or CANCELLED in capitals, and order_date is text in the form YYYY-MM-DD. Foreign keys are not enforced, so an order may point to an employee who does not exist.
| id | name | city |
|---|---|---|
| 1 | Engineering | Bengaluru |
| 2 | QA | Pune |
| 3 | Sales | Mumbai |
| 4 | HR | Pune |
| 5 | Support | Chennai |
| id | name | dept_id | manager_id | salary | bonus | hire_date | |
|---|---|---|---|---|---|---|---|
| 1 | Asha Rao | asha@shop.in | 1 | NULL | 150000 | 20000 | 2018-03-01 |
| 2 | Vikram Shah | vikram@shop.in | 1 | 1 | 95000 | NULL | 2020-07-15 |
| 3 | Neha Gupta | neha@shop.in | 2 | 1 | 82000 | 5000 | 2021-01-10 |
| 4 | Rohan Mehta | rohan@shop.in | 2 | 3 | 64000 | NULL | 2022-06-01 |
| 5 | Priya Nair | priya@shop.in | 2 | 3 | 64000 | 3000 | 2023-02-20 |
| 6 | Arjun Das | arjun@shop.in | 3 | 1 | 70000 | 15000 | 2019-11-05 |
| 7 | Kavya Iyer | kavya@shop.in | 3 | 6 | 52000 | NULL | 2024-01-08 |
| 8 | Imran Khan | imran@shop.in | 4 | 1 | 58000 | 2000 | 2020-09-30 |
| 9 | Sneha Patil | neha@shop.in | NULL | 1 | 47000 | NULL | 2025-04-14 |
| 10 | Dev Malhotra | dev@shop.in | 1 | 2 | 95000 | 8000 | 2022-12-01 |
| id | employee_id | customer | amount | status | order_date |
|---|---|---|---|---|---|
| 101 | 6 | Acme Ltd | 12000 | DELIVERED | 2026-01-05 |
| 102 | 6 | Bharat Traders | 4500 | CANCELLED | 2026-01-09 |
| 103 | 7 | Acme Ltd | 8000 | DELIVERED | 2026-02-11 |
| 104 | 7 | Cosmo Retail | 15000 | SHIPPED | 2026-02-20 |
| 105 | 6 | Delta Foods | 3000 | PENDING | 2026-03-02 |
| 106 | 7 | Bharat Traders | 9500 | DELIVERED | 2026-03-15 |
| 107 | 12 | Echo Stores | 7000 | DELIVERED | 2026-03-18 |
| 108 | 6 | Acme Ltd | NULL | PENDING | 2026-04-01 |
| 109 | 7 | Cosmo Retail | 15000 | SHIPPED | 2026-02-20 |
| 110 | 8 | Fresh Mart | 2500 | DELIVERED | 2026-04-10 |
Run shows your rows. Submit compares them with the expected rows.