-
Notifications
You must be signed in to change notification settings - Fork 0
/
Fill Missing Client Data [Accenture SQL Interview Question]
42 lines (36 loc) · 1.63 KB
/
Fill Missing Client Data [Accenture SQL Interview Question]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
with cte as(select *,count(category) over(order by product_id) as num
from products)
select product_id, COALESCE(category,max(category)over (partition by num) )as ct,name
from cte
-------------------------------------
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.
products Table
Column Name Type
product_id integer
category varchar
name varchar
products Example Input
product_id category name
1 Shoes Sperry Boat Shoe
2 Adidas Stan Smith
3 Vans Authentic
4 Jeans Levi 511
5 Wrangler Straight Fit
6 Shirts Lacoste Classic Polo
7 Nautica Linen Shirt
Example Output
product_id category name
1 Shoes Sperry Boat Shoe
2 Shoes Adidas Stan Smith
3 Shoes Vans Authentic
4 Jeans Levi 511
5 Jeans Wrangler Straight Fit
6 Shirts Lacoste Classic Polo
7 Shirts Nautica Linen Shirt