logo

Back to questions

Fill Missing Client Data

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.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.