SQL Duplicates Across Locations | Dataford Interview Questions

How would you write a SQL query to find duplicate product listings across multiple retail store locations?

Assume duplicate listings are active listings for the same product appearing at two or more distinct store locations. Return only products with valid product and store records.

Output

One row per duplicated product, with product_id, product_name, brand, location_count, and locations.
Include the distinct store names in alphabetical order.
Order by location_count descending, then product_id ascending.

Schema

product_listings

Column	Type	Description
listing_idPK	INTEGER	Unique listing identifier
product_id	INTEGER	Referenced product identifier
store_id	INTEGER	Referenced retail store identifier
listing_status	VARCHAR(20)	Listing state, such as active or inactive

retail_stores

Column	Type	Description
store_idPK	INTEGER	Unique retail store identifier
store_name	VARCHAR(120)	Retail store name
city	VARCHAR(80)	Store city

products

Column	Type	Description
product_idPK	INTEGER	Unique product identifier
product_name	VARCHAR(150)	Product display name
brand	VARCHAR(100)	Product brand

Your question is SQL Duplicates Across Locations. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem