BOSCH SQL Interview Question - FIRST_VALUE() WINDOWS Function

Подписаться 1,7 тыс.

Просмотров 4,4 тыс.

50% 1

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

Опубликовано:

28 апр 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 30

@TheBlacklist244 Месяц назад

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;

@CloudChallengers Месяц назад

@TheBlacklist244, thanks for posting different approach. But your query seems incomplete. Can you share the full query here?

@iamram436 Месяц назад

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

@CloudChallengers Месяц назад

Yes, it works. Thanks for posting the different approach Ram.

@monasanthosh9208 17 дней назад

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;

@sravankumar1767 Месяц назад

Nice explanation

@Vaibha293 Месяц назад

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

@CloudChallengers Месяц назад

Thanks for sharing different approach Vaibhav.

@LeaningGCP Месяц назад

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;

@DE_Pranav Месяц назад

not getting required op with this. can you check?

@CloudChallengers Месяц назад

@LeaningGCP, your query seems incomplete. Can you share the full query here?

@LeaningGCP Месяц назад

@@CloudChallengers @DE_Pranav Correct, My bad. It's not full filling the desired outcome. Tested with just one row update :(

@tagurreddy3549 14 дней назад

@@DE_Pranav 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.

@vijaygupta7059 Месяц назад

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

@CloudChallengers Месяц назад

Thanks for posting different approach Vijay. Keep posting different approaches for upcoming videos as well.

@NandhiniSubramani-dy6pn Месяц назад

Wecan use lag and case for this query,

@CloudChallengers Месяц назад

Thanks Nandhini. It would be much appreciated if you could share the query here.

@DE_Pranav Месяц назад

can you please share query.

@ajaykrishnanj5633 Месяц назад

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

@CloudChallengers Месяц назад

Thanks for quick response Ajay. This query works as well.

@93sai Месяц назад

you have used CTE not view.

@CloudChallengers Месяц назад

That's right Sai. Thanks for highlighting.

@chandanpatra1053 Месяц назад

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

@CloudChallengers Месяц назад

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.

@devendrabarasker4987 Месяц назад

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

@tagurreddy3549 Месяц назад

-- 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

@CloudChallengers Месяц назад

video is out on this this question. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qPIMa7YtXqg.htmlsi=YKGISgDmBN-NP3Cy

@anirbanbiswas7624 15 дней назад

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

@shashank_1180 Месяц назад

------ 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;

@CloudChallengers Месяц назад

@shashank_1180, these queries are not giving expected output. Bcz of the reason, you are doing sorting on brand_name.