BOSCH SQL Interview Question - FIRST_VALUE() WINDOWS Function
HTML-код
- Опубликовано: 6 фев 2025
- One of the SQL questions recently asked in BOSCH interview.
Given us brands table, we need to forward fill the categories wherever we have nulls for all brands in the table.
In order to solve this questions, we used ROW_NUMBER(), COUNT() and FIRST_VALUE() Functions. You will understand how all these functions works in this video.
Let us first create brands table
create table brands (category varchar(50), brand_name varchar(50))
Insert the records
insert into brands values ('chocolates', '5-star'),(NULL, 'dairy milk'),(NULL, 'perk'),(NULL, 'eclair'),('Biscuits', 'Britania'),(NULL, 'good day'),(NULL, 'boost')
Contact us:
info@cloudchallengers.com
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
With cte as ( select * , count (category) over (order by (select null) rows between unbounded preceding and current row) as rn from brands )
Select coalesce (category, max(category) over (partition by rn)) as category,
Brand_name
From cte;
with cte as(select * , 1 as num from brands),
cte2 as (select *,row_number()over(order by num) as rn from cte),
cte3 as(select *,min(category) over(order by rn asc) as category2 from cte2)
select category2 as category,brand_name from cte3
Yes, it works. Thanks for posting the different approach Ram.
My Solution:
select case when brand_name in ('5-star','dairymilk','perk','eclair') then 'chocolate'
when brand_name in ('Britania','goodday','boost') then 'Biscuits' end as category,brand_name
from chocolate_category;
You can think of any other better solution.
very good solution
Sir u are a nice person
Thanks for sharing your valuable feedback
simplest solution
DECLARE @v varchar(50);
UPDATE brands
SET @v = category = CASE when category is null then @v else category end
Using lag and case:
WITH temp_table AS (
SELECT category,
brand_name,
LAG(category) OVER (ORDER BY category, brand_name) AS prev_category
FROM snacks
)
SELECT
(CASE
WHEN category IS NULL THEN prev_category
ELSE category
END) AS category,
brand_name
FROM temp_table;
Nice explanation
with cte_null as
(
select *,ROW_NUMBER() over(order by (select null)) as rn from brands
),
cte_2 as
(
select *,case when category is null then 0 else 1 end cnt from cte_null
),
cte_3 as
(
select category,brand_name,sum(cnt) over(order by rn) as mn from cte_2
)
select first_value(category) over (partition by mn order by mn) as category,brand_name from cte_3
Can we use the below query for the same ?
select nvl(CATEGORY,'chocolates') as CATEGORY , BRAND_NAME
from brands
where BRAND_NAME not in ('good day' ,'boost')
union
select nvl(CATEGORY,'Biscuits') as CATEGORY , BRAND_NAME
from brands
where BRAND_NAME in ('good day' ,'boost')
Can use NTIle with first_value also :
Select first_value(category) over(partition by s.n) as category, brand_name
from (Select *, ntile(2) over() as n from brands) as s;
@TheBlacklist244, thanks for posting different approach. But your query seems incomplete. Can you share the full query here?
with CTE as
(
Select
row_number() over (order by (select null))as rn,
category,Brand_name,
(case when category is null then 0 else 1 end) as m
from brands
), cte2 as
(Select rn,category,Brand_name,sum(m) over (order by rn)as n from cte)
select rn,first_value(category)over(partition by n order by rn ) as category,Brand_name from cte2
Wecan use lag and case for this query,
Thanks Nandhini. It would be much appreciated if you could share the query here.
can you please share query.
You can opt simple solution using LAG() and CAse.
SELECT CASE
WHEN category is null THEN LAG(category) over()
ELSE category
END AS category, brand_name
FROM brands;
not getting required op with this. can you check?
@LeaningGCP, your query seems incomplete. Can you share the full query here?
@@CloudChallengers @DE_Pranav Correct, My bad. It's not full filling the desired outcome. Tested with just one row update :(
@@Pranav_1407
1 SELECT
2 CASE
3 WHEN CATEGORY IS NOT NULL THEN CATEGORY
4 ELSE LAG(CATEGORY IGNORE NULLS) OVER (ORDER BY ROWNUM)
5 END "CATEGEORY",
6 BRAND_NAME "BRAND NAME"
7 FROM
8* brands
9 /
CATEGEORY BRAND NAME
-------------------------------------------------- --------------------------------------------------
chocolates 5-star
chocolates dairy milk
chocolates perk
chocolates eclair
Biscuits Britania
Biscuits good day
Biscuits boost
7 rows selected.
Hey brother you have not been uploading vedio since last week
Why are you not uploading video please upload
@rajkumarpanigrahi2013, Thanks for the comment.
New video on SQL is out now. Please watch and like the video.
with cte as
(select brand_name,
case when brand_name='dairy milk' then 'chocolate'
when brand_name='perk' then 'chocolate'
when brand_name='eclair' then 'chocolate'
when brand_name='good day' then 'biscuits'
when brand_name='boost' then 'biscuits'
else category end category
from brands)
select category,brand_name
from cte;
mazak chal rha hai???🥲🥲🥲
with cte as(
select * ,case when category is null then 0 else 1 end nn,row_number() over(order by (select null)) rn
from brands
),f as(
select category,brand_name,sum(nn) over(order by rn)m,rn
from cte)
select first_value(category) over(partition by m order by rn)category ,brand_name
from f
Thanks for sharing different approach Vaibhav.
provide your mail address in the description box so that someone can mail you if he/she is having any question to ask.
please try to solve this question.
id (timestamp) emp_id (integer)
1/13/2024 9:25 10
1/13/2024 19:35 10
1/16/2024 9:10 10
1/16/2024 18:10 10
2/11/2024 9:07 10
2/11/2024 19:20 10
2/17/2024 8:40 17
2/17/2024 18:04 17
3/23/2024 9:20 10
3/23/2024 18:30 10
query to find the employees total working hours during weekends.
output should be
emp_id Total weekend
Working_hours
17 9.4
10 29.55
Thanks for suggestion Chandan, I added email id in the description box now. I also noted down the questions you are posting. I will post the videos on those questions soon.
Try this code -
with cte as(
select emp_id,CAST(datehour AS date)date,CAST(datehour AS time)hm,DATEPART(WEEKDAY,datehour)wd
from emp_work
),cte2 as(
select DATE, emp_id,MIN(hm)start_time,MAX(hm)end_time from cte
where wd in(1,7)
group by DATE ,emp_id
),cte3 as(
select *,DATEDIFF(MINUTE,start_time,end_time)*1.0/60 diff from cte2
)select distinct(emp_id),SUM(diff) over (partition by emp_id)Total_Weekend_Working_Hours from cte3
-- Create the table
CREATE TABLE work_hours (
id TIMESTAMP,
emp_id INTEGER
);
-- Insert the records
INSERT ALL
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-13 09:25:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-13 19:35:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-16 09:10:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-16 18:10:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-11 09:07:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-11 19:20:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-17 08:40:00', 'YYYY-MM-DD HH24:MI:SS'), 17)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-17 18:04:00', 'YYYY-MM-DD HH24:MI:SS'), 17)
INTO work_hours VALUES (TO_TIMESTAMP('2024-03-23 09:20:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-03-23 18:30:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
SELECT * FROM dual; SQL>
SQL> SELECT
2 emp_id,
3 ROUND(SUM(EXTRACT(HOUR FROM working_hours) + EXTRACT(MINUTE FROM working_hours) / 60), 2) AS Total_weekend_Working_hours
4 FROM (
5 SELECT
6 emp_id,
7 MAX(id) - MIN(id) AS working_hours
8 FROM
9 work_hours
10 WHERE
11 TO_CHAR(id, 'DY', 'NLS_DATE_LANGUAGE=American') IN ('SAT', 'SUN') -- Filter weekends
12 GROUP BY
13 emp_id,
14 TO_CHAR(id, 'YYYY-MM-DD') -- Group by date to get total hours for each weekend day
15 ) weekends
16 GROUP BY
17 emp_id
18 ORDER BY
19 emp_id;
EMP_ID TOTAL_WEEKEND_WORKING_HOURS
---------- ---------------------------
10 29.55
17 9.4
video is out on this this question. ruclips.net/video/qPIMa7YtXqg/видео.htmlsi=YKGISgDmBN-NP3Cy
with cte as(select *,date(id) as date_r ,coalesce(lead(id) over(order by id),0)as logout_time,
dayname(id)as day_name from employee_logs),
cte2 as(select * from cte where day_name 'Tuesday' group by date_r),
cte3 as(select *,timestampdiff(hour,id,logout_time)as hrs from cte2 order by emp_id)
select emp_id,sum(hrs) as total_weekend_hrs from cte3 group by emp_id
Select first_value(Category) over (Partition by Seg order by RN) as Category,Brand_Name from
(Select *,Sum(Flag) over (Order by Rn) as Seg from
(Select *,Case When Category Is not null then 1 else 0 end as Flag,row_number() over (Order by (Select Null)) as Rn
from Brands)N)N1;
-- Solution using approach unbounded preceding
WITH brand_vw AS (
SELECT
*,
LAST_VALUE(category IGNORE NULLS) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS new_category
FROM
brands
)
SELECT new_category, brand_name
FROM brand_vw
ORDER BY id;
you have used CTE not view.
That's right Sai. Thanks for highlighting.
my solution in MSSQL DB ::
with cte as
(
Select *,count(category)over( order by (select null) rows between unbounded preceding and 0 following ) as new from brands
)
Select FIRST_VALUE(category)over(partition by new order by category desc) as category,brand_name from cte
Thanks for posting different approach Vijay. Keep posting different approaches for upcoming videos as well.
with cte as(
select *,ROW_NUMBER() over(order by (select null)) as flag from brands)
,
cte1 as(
select *,sum(case when category is not null then 1 else 0 end) over (order by flag) as
new_flag
from cte)
select FIRST_VALUE(category) over(partition by new_flag order by flag) as category,brand_name from cte1
Thanks for quick response Ajay. This query works as well.
with cte_null AS
(SELECT
*,
ROW_NUMBER() over(order by (select null)) AS rn
FROM brands)
SELECT
first_value(category) OVER(PARTITION BY cn ORDER BY rn) AS category,
brand_name
FROM
(SELECT
*,
COUNT(category) OVER(ORDER BY rn) AS cn
FROM cte_null) category_cnt;
------ solution 1 ---------------------------------------------------------------------------------------
with cte as (
select *
,count(category) over (order by brand_name) as grp
from #brands
)
select first_value(category) over (partition by grp order by grp) as filled_category
,brand_name
from cte
------ solution 2 ---------------------------------------------------------------------------------------
with cte as (
select *
,count(category) over (order by brand_name) as grp
from #brands
)
SELECT isnull( category, max(category) over(partition by grp order by grp)) AS category
, brand_name
FROM cte;
@shashank_1180, these queries are not giving expected output. Bcz of the reason, you are doing sorting on brand_name.