Interview Guides

Filter Active Products by Price | Dataford Interview Questions - Dataford - Ace your Interview

Filter Active Products by Price

Easy

SQL & Data Manipulation

JoinsAggregations

Problem

Task

ShopKart wants a simple report of products that are currently active and priced above a threshold. Write a SQL query to return the product name, category, and price for active products with a price greater than 50.

Requirements

Return only rows where status = 'active' and price > 50
Show product_name, category, and price
Order the result by price in descending order

Table Definition

products

Column	Type	Description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Product price
status	VARCHAR(20)	Product status such as active or discontinued

Sample Data

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Current listed price
status	VARCHAR(20)	Lifecycle status such as active or discontinued

products

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active
7	USB Cable	Electronics	0.00	active
8	Standing Desk	Furniture	250.00	discontinued
9	Coffee Mug	Kitchen	12.00
10	Monitor Stand		50.00	active
11	Bluetooth Speaker	Stationery	null	inactive
12	Water Bottle	Home Office	13	inactive
13	Air Purifier	null	278	limited
14	Power Bank	Gaming	-47	testing
15	Floor Mat	Accessories	219	draft
16	Floor Mat	null	294	coming_soon
17	Stapler	Wellness	83	paused
18	Laptop Sleeve	null	230	backordered
19	Office Chair	Lighting	26	seasonal
20	Whiteboard Markers	Cables	188	backordered
21	Whiteboard Markers	null	57	expired
22	Mechanical Keyboard	Outdoor	262	retired
23	Monitor Stand	Kitchen	205	draft
24	Paper Shredder	Decor	-6	expired
25	Power Bank	Organization	283	seasonal

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Problem

Task

Requirements

Return only rows where status = 'active' and price > 50
Show product_name, category, and price
Order the result by price in descending order

Table Definition

products

Column	Type	Description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Product price
status	VARCHAR(20)	Product status such as active or discontinued

Sample Data

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Current listed price
status	VARCHAR(20)	Lifecycle status such as active or discontinued

products

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active
7	USB Cable	Electronics	0.00	active
8	Standing Desk	Furniture	250.00	discontinued
9	Coffee Mug	Kitchen	12.00
10	Monitor Stand		50.00	active
11	Bluetooth Speaker	Stationery	null	inactive
12	Water Bottle	Home Office	13	inactive
13	Air Purifier	null	278	limited
14	Power Bank	Gaming	-47	testing
15	Floor Mat	Accessories	219	draft
16	Floor Mat	null	294	coming_soon
17	Stapler	Wellness	83	paused
18	Laptop Sleeve	null	230	backordered
19	Office Chair	Lighting	26	seasonal
20	Whiteboard Markers	Cables	188	backordered
21	Whiteboard Markers	null	57	expired
22	Mechanical Keyboard	Outdoor	262	retired
23	Monitor Stand	Kitchen	205	draft
24	Paper Shredder	Decor	-6	expired
25	Power Bank	Organization	283	seasonal

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Filter Active Products by Price

Easy

SQL & Data Manipulation

JoinsAggregations

Problem

Task

Requirements

Return only rows where status = 'active' and price > 50
Show product_name, category, and price
Order the result by price in descending order

Table Definition

products

Column	Type	Description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Product price
status	VARCHAR(20)	Product status such as active or discontinued

Sample Data

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Current listed price
status	VARCHAR(20)	Lifecycle status such as active or discontinued

products

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active
7	USB Cable	Electronics	0.00	active
8	Standing Desk	Furniture	250.00	discontinued
9	Coffee Mug	Kitchen	12.00
10	Monitor Stand		50.00	active
11	Bluetooth Speaker	Stationery	null	inactive
12	Water Bottle	Home Office	13	inactive
13	Air Purifier	null	278	limited
14	Power Bank	Gaming	-47	testing
15	Floor Mat	Accessories	219	draft
16	Floor Mat	null	294	coming_soon
17	Stapler	Wellness	83	paused
18	Laptop Sleeve	null	230	backordered
19	Office Chair	Lighting	26	seasonal
20	Whiteboard Markers	Cables	188	backordered
21	Whiteboard Markers	null	57	expired
22	Mechanical Keyboard	Outdoor	262	retired
23	Monitor Stand	Kitchen	205	draft
24	Paper Shredder	Decor	-6	expired
25	Power Bank	Organization	283	seasonal

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Problem

Task

Requirements

Return only rows where status = 'active' and price > 50
Show product_name, category, and price
Order the result by price in descending order

Table Definition

products

Column	Type	Description
product_id	INT	Primary key for each product
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Product price
status	VARCHAR(20)	Product status such as active or discontinued

Sample Data

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Schema

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
price	DECIMAL(10,2)	Current listed price
status	VARCHAR(20)	Lifecycle status such as active or discontinued

products

product_id	product_name	category	price	status
1	Wireless Mouse	Electronics	25.99	active
2	Office Chair	Furniture	120.00	active
3	Notebook Set	Stationery	15.50	discontinued
4	Desk Lamp	Furniture	55.00	active
5	Water Bottle	Kitchen	18.00	active
6	Bluetooth Speaker	Electronics	75.00	active
7	USB Cable	Electronics	0.00	active
8	Standing Desk	Furniture	250.00	discontinued
9	Coffee Mug	Kitchen	12.00
10	Monitor Stand		50.00	active
11	Bluetooth Speaker	Stationery	null	inactive
12	Water Bottle	Home Office	13	inactive
13	Air Purifier	null	278	limited
14	Power Bank	Gaming	-47	testing
15	Floor Mat	Accessories	219	draft
16	Floor Mat	null	294	coming_soon
17	Stapler	Wellness	83	paused
18	Laptop Sleeve	null	230	backordered
19	Office Chair	Lighting	26	seasonal
20	Whiteboard Markers	Cables	188	backordered
21	Whiteboard Markers	null	57	expired
22	Mechanical Keyboard	Outdoor	262	retired
23	Monitor Stand	Kitchen	205	draft
24	Paper Shredder	Decor	-6	expired
25	Power Bank	Organization	283	seasonal

Expected Output

product_name	category	price
Office Chair	Furniture	120.00
Bluetooth Speaker	Electronics	75.00
Desk Lamp	Furniture	55.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next