Fill Missing Client Data Accenture SQL Interview Question

When accessing Accenture's retailer client's database, you observe that the category column in products table contains null values.

Write a query that returns the updated product table with all the category values filled in, taking into consideration the assumption that the first product in each category will always have a defined category value.

Assumptions:

• Each category is expected to be listed only once in the column and products within the same category should be grouped together based on sequential product IDs.
• The first product in each category will always have a defined category value.
  • For instance, the category for product ID 1 is 'Shoes', then the subsequent product IDs 2 and 3 will be categorised as 'Shoes'.
  • Similarly, product ID 4 is 'Jeans', then the following product ID 5 is categorised as 'Jeans' category, and so forth.

Effective April 16, 2023, the question, assumptions, solution and hints were revised.

Table

Example Input

Example Output

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!

Step 1: Assigning Numbers to Categories using Window Function

We can start by creating an additional column to update the categories for products that have null values.

To achieve this, we can use the function as a window function, which assigns a numeric value to each category type. The function calculates the number of rows with non-null values in the column and the window function allows us to apply it to the entire result set.

The output of the query generates a numbered category for each row in the column.

For example, the first 5 rows of the output will look like this:

Product ID 1 is categorized as 'Shoes' and assigned the value of 1 in the column. For Product IDs 2 and 3, which have no category assigned, the counter remains at 1 in the column. However, Product ID 4 is labeled as 'Jeans', which is a non-null value, and thus the counter gets increased to 2 for this product.

Step 2: Filling Missing Categories using COALESCE() Function

Next, we fill the missing categories using the function.

How function works with two expressions:

• : If the column is not null, the value is returned.
• : If the value is null, it returns the maximum category value within the same partition. In other words, it looks for the non-null category value from other rows that share the same . This ensures that the missing category values are filled with the appropriate value from the same category group based on the column.

For example, product IDs 1, 2, and 3 are assigned the value of 1 in the column. Since the maximum value within this partition is also 1, the function assigns the corresponding category value to product IDs 2 and 3, filling in the missing values based on the category of product ID 1.

Here's the final results:

You may find this Stack Overflow post helpful as it provides a concise explanation of a similar question.

Solution #2: Using subquery