Clearance candidates
easyClearance candidates
Target SQL Interview Question
At the end of each quarter, Target's operations team picks products for its clearance sale. A product is a clearance candidate if it is no longer active or if it has no stock left.
List the product_name of every clearance candidate. Each product should appear only once.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
- ML Engineer
- AI Engineer
productsTable15 rows
| Column Name | Type |
|---|---|
| product_id | BIGINT |
| product_name | VARCHAR |
| category | VARCHAR |
| brand | VARCHAR |
| price | DOUBLE |
| discount_pct | BIGINT |
| stock_quantity | BIGINT |
| launch_date | DATE |
| is_active | BOOLEAN |
productsExample Input
| product_id | product_name | category | brand | price | discount_pct | stock_quantity | launch_date | is_active |
|---|---|---|---|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | Logitech | 24.99 | 10 | 150 | 2023-02-14 | true |
| 3 | Noise Canceling Headphones | Electronics | Sony | 299.99 | 15 | 0 | 2022-11-20 | true |
| 6 | Desk Lamp | Furniture | NULL | 39.5 | NULL | 80 | 2024-01-15 | true |
| 8 | Notebook Set | Stationery | Moleskine | 18 | NULL | 0 | 2022-09-01 | false |
| 12 | Monitor Arm | Accessories | NULL | 89 | 25 | 0 | 2022-12-12 | false |
| 15 | Whiteboard | Stationery | Quartet | 65 | NULL | 18 | 2021-11-11 | false |
Example Output
| product_name |
|---|
| Monitor Arm |
| Noise Canceling Headphones |
| Notebook Set |
| Whiteboard |
Explanation
The Noise Canceling Headphones are still active but have no stock, so they qualify. The Whiteboard still has 18 in stock but is no longer active, so it also qualifies. The Notebook Set and Monitor Arm meet both conditions and still appear only once each.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Target
Difficulty
easy
Topic
logical operators
Your query
Starting the SQL engine...