logo

Back to questions

Fill Missing Client Data [Accenture SQL Interview Question]

Medium

When you log in to your retailer client's database, you notice that their product catalog data is full of gaps in the column. Can you write a SQL query that returns the product catalog with the missing data filled in?

Assumptions

  • Each category is mentioned only once in a category column.
  • All the products belonging to same category are grouped together.
  • The first product from a product group will always have a defined category.
    • Meaning that the first item from each category will not have a missing value.

Table:

Column NameType
product_idinteger
categoryvarchar
namevarchar

Example Input:

product_idcategoryname
1ShoesSperry Boat Shoe
2Adidas Stan Smith
3Vans Authentic
4JeansLevi 511
5Wrangler Straight Fit
6ShirtsLacoste Classic Polo
7Nautica Linen Shirt

Example Output:

product_idcategoryname
1ShoesSperry Boat Shoe
2ShoesAdidas Stan Smith
3ShoesVans Authentic
4JeansLevi 511
5JeansWrangler Straight Fit
6ShirtsLacoste Classic Polo
7ShirtsNautica Linen Shirt

Explanation:

Shoes will replace all values below the product Sperry Boat Shoe until Jeans appears. Similarly, Jeans will replace s for the product Wrangler Straight Fit, and so on.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14