This SQL Problem I Could Not Answer in Deloitte Interview | Last Not Null Value | Data Analytics

In this video we will discuss a SQL problem where we need to populate a column with last non null value.
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
script:
create table brands
(
category varchar(20),
brand_name varchar(20)
);
insert into brands values
('chocolates','5-star')
,(null,'dairy milk')
,(null,'perk')
,(null,'eclair')
,('Biscuits','britannia')
,(null,'good day')
,(null,'boost');
#sql #interview #deloitte

[@sathwikdhanya5400]

using only window function:
with cte as(select *,
ROW_NUMBER() over(order by (select 1)) as id
from brands),
cte2 AS (select *,
sum(case when category is NULL then 0 else 1 end) over(order by id) as part
from cte)
SELECT *,
FIRST_VALUE(category) over(partition by part order by id) as fill_NA
from cte2

[@pauldfm1970]

Lovely to see a solution in sql for something I have solved in excel for years.

[@Howto-ty4ru]

True

[@venkataram6460]

Hi Ankit,
I have solved this using below method, you have tought this method for another problem.
with cte1 as
(select *,
row_number() over(order by (select null)) as id,
case when category is null then 0 else 1 end as rn
from brands
)
,cte2 as
(select *
,sum(rn) over(order by id) as roll_sum
from cte1
)
select brand_name,
max(category) over(partition by roll_sum) as category
from cte2

[@hiyaborah9167]

Hi Ankit, I have another solution to this problem:
with cte1 as (
select * ,
row_number() over(order by (select null)) rn
from brands
)
select min(category) over(order by rn rows between unbounded preceding and current row) category,
brand_name
from cte1
Wasn't really confident using rows clause but thanks to your videos I am learning new concepts everyday.

[@ankitbansal6]

Awesome

[@stat_life]

@@ankitbansal6
with temp as
(
select * ,
row_number() over(order by (select null)) rn
from brands
)
select min(category) over(order by rn) category,
brand_name
from temp
sir, won't this query pretty much gives the same solution?
why did we used rows between unbounded preceding and current row here ?

[@LittleBrainFeeds]

Good solution

[@subhojitchatterjee6312]

Easy to understand ,but this will not work in all cases. If the category name changes something to "Freshly Packed" from "Biscuits" then it won't work.

[@ank_kumar12]

i got the same result from this query
with tab1 as (select *,
count(t.category) over(order by t.rnk) as cnt from(
select *,
row_number() over() as rnk
from choco) t)
select category,brand,
first_value(category) over(partition by cnt order by rnk)
from tab1;

[@dfkgjdflkg]

Ever thanks for the invaluable example

[@abdulqadar559]

Hi Ankit, I solved it using running sum
with t1 as
(
select *,row_number() over () rn from brands
),
t2 as
(
select *,sum(case when category is not null then 1 else 0 end) over (order by rn) flag from t1
)
select max(category) over (partition by flag) as category,brand_name from t2

[@amazingfactsandinformation]

Hey Ankit
Just wanted to tell you that you're awesome. There is always so much to learn from you 👏

[@ankitbansal6]

Thank you so much 😀

[@sidindian1982]

@@ankitbansal6 excellent😍😍🥰🥰🥰 sir

[@felipepereira3061]

Thanks a lot Ankit! Yout vídeo simply solved a problem that i had to make a report query on my job! THANK YOU! =D

[@ankitbansal6]

Happy to help😁

[@kanchankumar3355]

Such a wonderful trick 👍

[@dfkgjdflkg]

Very interesting what you mention, which gives me hope to become better as solving SQL problems. You said you could not solve this problem. I guess I have to work harder on the ones I can not solve and work harder. Thanks for the inspiration, as usual.

[@ankitbansal6]

There is always a learning curve 😊

[@siddheshkalgaonkar2752]

Good problem, Ankit :)

[@kurakulaprasad7898]

Thank you so much Sir for the wonderful query

[@swapnilshimpi7588]

Good use case to practice Ankit,
I practiced using combination of rowunm ,join and understood how it works and then able to come up with one line solution .
Done using oracle -
select nvl(category,lag(category) ignore nulls over(order by null) ) category,brand
from
brands ;
or
select category,brand,rownum rn1, last_value(category) ignore nulls over ( order by rownum asc rows between unbounded preceding and current row )
from brands;
Your solution helped to derive the approach. So though I knew how last_value , first value worked , first time I found out use case where rows between... clause and ignore null was useful .
Thanks for providing this example!

[@ankitbansal6]

Great 😊

[@namanmakkar1205]

Can you explain ignore null

[@swapnilshimpi7588]

@@namanmakkar1205 Ignore null , as indicates it ignores null in that window and looks for rows with actual value

[@shekharagarwal1004]

@@swapnilshimpi7588 Can you help to get the same in MY SQL syntax. It is not accepting the ignore null part .

[@shivanandkumar6002]

Much needed for today 😂

[@pallavibs4673]

Hi,
Help me to understand how did inner join between CET1 result and CTE 2 result replaced NULL with category name.. If possible please make a short video on query execution.

[@shubhamsom]

Hi Ankit, I have another solution
I am using running sum to make all nulls be part of same group as of previous non null value
(I learned this from you only)
with brands_rn as (
select *, row_number() over() as rn
from brands
),
brands_grouped as (
select *,
sum(case when category is null then 0 else 1 end) over(order by rn) as gpno
from
brands_rn
)
select
max(category) over(partition by gpno) as category,
brand_name
from brands_grouped

[@parthchamp9252]

WITH temp1
AS (SELECT *,
Row_number()
over(
ORDER BY NULL) AS start
FROM brands),
temp2
AS (SELECT *
FROM temp1
WHERE category IS NOT NULL),
temp3
AS (SELECT *,
( Lead(start, 1, 9999)
over (
ORDER BY start) - 1 ) AS END
FROM temp2)
SELECT b.category,
a.brand_name
FROM temp1 a
join temp3 b
ON a.start BETWEEN b.start AND b.END;

[@vivekkumarsingh9009]

I used the same technique like the ON OFF problem here, tried to maintain a simple strategy for creating a sequence for creating groups. Its just easier for me to remember.
with cte as (select *,row_number() over() as rowed from brands),
cte2 as (
select *
,sum(case when category is not null then 1 else 0 end) over(order by rowed) as grouped
from cte)
select max(category) over (partition by grouped),brand_name
from cte2;

[@ankitbansal6]

Good one

[@siddheshkalgaonkar2752]

Interesting solution, mate. I was surprised to see that max works on the string too...

[@hasanmougharbel8030]

Hey dear, god bless your efforts in this channel.
I have a general enquiry as a new sql learner.
How could i create a pipeline to extract and load data from existing accounting program into our SQL server instances.
How can i know if the export mechanism in the software permits me to undertake this extraction process, and how can i know if an application have an api?
Thanks for taking care of my enquires.
Looking forward to gain more knowledge from you.

[@nithinkumar6555]

with CTE1 as (select *,row_number()over() as rn,case when lead(category)over() is null
and category is not null then 1 else 0 end as num from brands),
CTE2 as (select category,brand_name,sum(num)over(order by rn) as sm from CTE1)
select b1.category,b2.brand_name from CTE2 b1 left join CTE2 b2 on b1.sm=b2.sm
where b1.category is not null

[@PaulFilmer]

I don't believe any solution should rely on the default return order of data to make an assumption on what the correct value would be. In this situation, the only proper course of action is to find the original source data, manually identify, or use a master data source of all chocolate & biscuit brands to match and validate.

[@GowthamR-ro2pt]

Easiest approach would be :
with cte as (select *,ROW_NUMBER() over(order by (select null))rn from brands),cte2 as (
select *,count(category) over (order by rn) cnt from cte)
select *,FIRST_VALUE(category) over (partition by cnt order by cnt)result from cte2

[@shubhamagrawal7068]

In this approach, I am using co-related subquery. I haven't use any join. This code is very small.........
with t1 as (select *, row_number() over() rn from brands)
select
(case
when category is null then (select category from t1 b where b.rn < a.rn and category is not null order by rn desc limit 1)
else category
end) category, brand_name
from t1 a

[@ankimedia5225]

What sub query will return please explain

[@ankimedia5225]

@Subham Agrawal

[@surensurendar7417]

other solution using first value:-
with cte as (
SELECT category,brand_name,
row_number() over(ORDER BY NULL) as rn
FROM brands
)
select category,brand_name,
first_value (category) IGNORE NULLS
over(order by rn desc
range between current row and unbounded following) as result
FROM cte
ORDER BY rn
;

[@saigusidi9122]

select (case: when category is null then category= lag(category) over()
else category
end) as category, chocolate from table.

[@shashankmp6668]

hi ankit, i need to get clarification Microsoft Sql Server is the platform where we can write the sql queries or what is. Microsoft sql server can you give me a brief idea about this

[@vijaygupta7059]

using CTE and aggregate (count) window function
with cte as(
Select *
,count(category)over(order by (Select null) rows between unbounded preceding and 0 following ) as rn
from brands
)
Select first_value(category)over(partition by rn order by rn ) as category, brand_name from cte

[@litheshraju5207]

used between for join:
with cte as(
select *,ROW_NUMBER() over(order by (select null)) rn
from brands)
,cte1 as(
select *,lead(rn-1,1,9999) over(order by rn) btw
from cte
where category is not null
)
select c1.category,c.brand_name
from cte c inner join cte1 c1 on c.rn between c1.rn and c1.btw

[@ankitbansal6]

Looks good

[@MrChilo89]

looking a way to create a dynamic sql and do it on every column of a table

[@sreejitchakraborty6575]

Hi Can you check this:
select max(category) over(partition by ct),brand_name from
(
select category,brand_name,
count(category) over(rows between unbounded preceding and current row) ct from brands
)q

[@sajanpradhan1539]

this works but does it a good query???
with a as (
select *,
row_number() over() as remark
from brands)
select if (category=null,'chocolates','chocolates') category,brand_name
from a where remark between 1 and 4
union all
select category,brand_name from (
with b as (
select *,
row_number() over() as remark
from brands)
select if (category=null,'Biscuits','Biscuits') category,brand_name
from b where remark between 5 and 7) as abc

[@AbhishekTripathi20]

Hi Ankit, can we do this way........
Select (case when rn>1 and rn5 and rn

[@komalpawar5124]

I tried to solve in different way and here my solution :;with cte as
(select ROW_NUMBER() over(order by brand_name) as rw,* from brands)
, cte2 as(
select category ,brand_name,sum ( case when category is null then 0 else 1 end ) over(order by rw) rww from cte)
select FIRST_VALUE(category) over(partition by rww order by (select 1)) as Category, brand_name
from cte2

[@saurabhpandey5296]

with cte as
(select *, row_number() over(order by (select null)) as rnum
from brands),
cte2 as
(select *, sum(case when category is not null then rnum end) over(order by rnum) as flag
from cte)
select first_value(category) over(partition by flag order by flag) as category_new, brand_name
from cte2

[@atharvadeshpande7324]

Hi Ankit, row_number with ordering by (select null) does not work in redshift SQL. Redshift considers the whole table under order by and then gives row numbers. Is there any alternative to designate such serial numbers which will work in redshift?

[@ankitbansal6]

You can do order by true in redshift

[@abb_raj1107]

Bhai ! one thing I want to know is row_number() fun also work with over() clause without adding order by in it , what is the difference between the empty over() and over(order by (select null)).
btw .... thanks u so much

[@ankitbansal6]

Doesn't work in SQL server

[@nivadonga6980]

Hi, here is my solution:
with cte as
(select *, row_number() over(order by (select null)) as rn from brands)
select first_value(category) over(partition by partition_variable order by rn) as Category, brand_name from
(select *, sum(case when category is null then 0 else 1 end) over(order by rn) as partition_variable
from cte) A

[@vijaypalmanit]

Bhai video dekhta hu to sab samajh me aa jata hai par kuchh din baad bhul jata hu

[@rawat7203]

Hi Sir My Way:
select
case
when partition_flag = 1 then 'choclates'
when partition_flag = 2 then 'Biscuits'
end as 'category',
brand_name from (
select *,
case when category is not null then 1 else 0 end as flag,
sum(case when category is not null then 1 else 0 end) over(rows between unbounded preceding and current row) as partition_flag
from brands)temp;

[@amarjadhav3955]

Hi sir you can use the first_value() function to solve this issue in a simple way.

[@smahourkumar2568]

select category1,brand_name from (
select * ,max(category) over(partition by t ) as category1
from (
select *,sum(case when category is not null then 1 else 0 end ) over(order by rn) as t from
(select *,row_number() over() as rn from brands)))

[@debaratiaich16]

this should work fine:
SELECT
COALESCE(category, LAG(category IGNORE NULLS) OVER (ORDER BY brand_name)) AS category,
brand_name
FROM
your_table_name;

[@KnstntnKrgr]

That saved my ass. Thx ❤

[@ganeshsinghn6146]

;with cte as(
select *,ROW_NUMBER()over(order by (select null)) as rw from brands
),cte1 as(
select *,count(category)over(order by rw) as cnt from cte
)
select *,FIRST_VALUE(category)over(partition by cnt order by rw) from cte1

[@SiddharthSingh-yi7vf]

with cte as (
select category,brand_name,
row_number() over (order by (select null)) as product_id
from brands)
, cte2 as (
select category,brand_name,product_id,
count(category) over (order by product_id) as category_group
from cte)
select
first_value(category) over (partition by category_group order by product_id) as category,
brand_name
from cte2;

[@rajeshvodela91]

with cte as (
select *,
row_number() over() as rn
from brands
)
select
case
when rn between 1 and 4 then 'chocolates' else 'biscuits'
end as category,
brand_name
from cte;
i did like this is this correct

[@akashvishwakarma1317]

with
base as
(
Select
*,
case
when category is not null
then 1
else 0
end as flag
from brands
),
base2 as
(
Select
*,
sum(flag) over(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_sum
from base
)
Select
first_value(category) over(partition by running_sum) as category,
brand_name
from base2

[@ankitbansal6]

Good one 👍

[@Satish_____Sharma]

using mysql
with cte as (SELECT category, brand_name,row_number() over () as rn
FROM brands)
,cte1 as (select *,sum(case when category is not null then 1 else 0 end) over (order by rn) as ct from cte)
select first_value(category) over (partition by ct order by rn) as category,brand_name from cte1

[@eRRor4hack]

Alternate Solution: with structureCte as( --creating structure/skeleton so that "order by" can be used
select *,
ROW_NUMBER() over(order by(select null)) as rn
from brands
)
,categoryGrp as(
select *,
count(category) over(order by rn asc rows between unbounded preceding and current row) as grp --grouper(so that partitioning can be done)
from structureCte
)
select
FIRST_VALUE(category) over(partition by grp order by rn) as Category_filled, --Get First_Value in a particular group
categoryGrp.brand_name
from categoryGrp

[@francis.joseph]

can you do a lead lag master class video?

[@ankitbansal6]

It's there already

[@9tanmoy]

Hi Ankit can we do this, if we just make an indexing column like you did with rn and then according to the index we use set function and update the value at once...can we do that

[@ankitbansal6]

Try writing the query 😊

[@2412_Sujoy_Das]

I derived by the same solution Sir!!!!!!!!!!
WITH CTE_1 AS (Select *, ROW_NUMBER() OVER(order by (SELECT NULL)) as rnk from brands),
CTE_2 AS (Select category, rnk, COALESCE(LEAD(rnk) OVER(order by rnk),999999) as next_one
from CTE_1 WHERE category IS NOT NULL)
Select B.category, A.brand_name
FROM CTE_1 A
JOIN CTE_2 B
ON A.rnk >= B.rnk AND (A.rnk

[@sheetalpalod996]

Row_number() over (order by null) is changing the order of names of item and not giving numbering to the exact order that is in table in Netezza. Any suggestions . Tried replacing null with 1, true but still order gets mixed up

[@no-nonsense-here]

try just row_number() over()

[@poornimanaidu6923]

Hi Ankit, I solved this problem using user defined variable with case statement. Please check my approach if its efficient -
select case when category is not null then @category := category
when category is null then @category end as category1,
brand_name from brands;

[@singh_dataengineering_tips]

I tried solving this using while loop, try this one:
select *,row_number() over(order by (select null)) as rw into #brands from brands
--drop table #brands
select * from #brands
declare @rw int
set @rw=1
while (@rw

[@nachiketpalsodkar4356]

ANother Simpler way!!!
select *
, MIN(category)over(order by (select NULL) rows between unbounded preceding and current row) as NEW_CATEGORY
from brands;

[@udhaybhaskarbellamkonda1678]

This works ,great

[@PiyushSharma-jq8rr]

Thanks Ankit but we can write it another way 🙂..simple with CASE statement.
WITH t1 AS(
select category, brand_name,
row_number() over(order by (select null from dual)) as rnk
from brands)
SELECT CASE
WHEN rnk = 1 THEN 'chocolates'
WHEN rnk = 2 THEN 'chocolates'
WHEN rnk = 3 THEN 'chocolates'
WHEN rnk = 4 THEN 'chocolates'
ELSE 'Biscuits'
END AS category,
brand_name, rnk
FROM t1;

[@ankitbansal6]

But you have hard coded the values here. What if more products come in tables

[@sankarpatro5602]

Hi Ankit , here is my solution
with CTE as (
Select category, Brand_Name,
ROW_NUMBER () over (order by (select 0)) as rn
from brands)
select brand_name, min(category) over (order by rn) as New_Category from CTE

[@ankitbansal6]

This is working with this particular data only take one more category with name starts from C or D

[@Alexpudow]

HI, I solved that by recursive cte 🙂
with a as (
select *
,case when category is not null then row_number() over(order by (select null)) end crn
,row_number() over(order by (select null)) cn
from brands),
b as (
select max(cn) mxcn
from a),
c as (
select category, crn mn, lead(crn,1, (select mxcn from b)+1) over(order by crn)-1 mx
from a
where category is not null),
rec as (
select category, mn, mx
from c
union all
select category, mn+1, mx
from rec
where mn

[@anirvansen2941]

MY MYSQL Solution
with base as (select *,case when category is not null then 1 else 0 end as flag from brands),
base_rank as (
select *,sum(flag) over(rows between unbounded preceding and current row) as run_sum from base )
select coalesce(category,max(category) over(partition by run_sum)) as category,brand_name from base_rank;

[@dipeshg8581]

select count(category) as category from brands;
set @cat = 2;
with cte as (select category,brand_name,
ntile(@cat) over() as cat
from brands)
select brand_name,
first_value(category) over(partition by cat) as category
from cte
hope you like this solution

[@shivprasadshelgavkar4816]

SELECT
a.category,
a.brand_name,
MAX(a.category) OVER (PARTITION BY a.RN ORDER BY a.RN) newcategory
FROM (
SELECT *
, SUM(CASE WHEN COALESCE(category,'') = '' THEN 0 ELSE 1 END) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING and current row ) RN
FROM brands
) a
;

[@Shivani-yk7tw]

here is my solution
with t1 as
(
select *,row_number() over (order by (select null)) rn from brands
),
t2 as
(
select *,sum(case when category is not null then 1 else 0 end)
over (order by rn) flag from t1
)
select * from t2
select max(category) over (partition by flag) as category,brand_name from t2

[@anish_bhateja]

my solution:
with cte as (select *,row_number() over () as num from deloitte_brands),
brands as (select category as type,num as row_num from cte where category is not null), cte2 as (select *,lead(row_num,1,9999) over() as next_row_num from brands)
select type,brand_name from cte2,cte where (cte.num >= cte2.row_num and cte.num < cte2.next_row_num ) order by type;

[@naveennoel9496]

Just an observation
To get row number on the whole table the over clause can be left blank too.
select *, row_number() over() from table;

[@gouravsinha268]

with cte as
(SELECT
*
, SUM(CASE WHEN category IS NULL THEN 0 ELSE 1 END) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) flg
FROM brands)
SELECT
FIRST_VALUE(category) OVER(PARTITION BY flg)
, brand_name
FROM cte;
How about this one Ankit?

[@siddhartharao8490]

Thanks bro for the brief explanation!

[@mohitupadhayay1439]

In power BI, you just need to click on the column and click 'Fill values'. Simple.

[@ankitbansal6]

Cool

[@ishanksaxena3329]

But Ankit said that it's a SQL interview so we have to solve this problem with sql😄

[@vikaskumar-qr5tj]

🤣

[@Kishansinghbhandari]

​@@ishanksaxena3329😂

[@gunasekharreddy230]

Hello , This Problem was asked to do in DAX for me in EXL Interview

[@mohdanas7198]

WITH CTE AS
(SELECT *,NTILE(2) OVER() as Bucket from Your_Table)
,CTE2 AS
(SELECT * ,CASE WHEN (Bucket = 1 and category IS NULL)
or (Bucket = 1 and category IS not NULL) then 'Choclate'
else 'Biscuite' end as Category1
from CTE)
SELECT category1 AS category
,brand
FROM CTE2

[@umakantyadav9972]

My Solution Using First_value():
with brand as (
select category,COUNT(category) OVER(order by b.rn) as cnt,brand_name,rn
from(
select *,ROW_NUMBER() OVER(order by NULL) as rn
from brands) as b
)
select first_value(brand.category) over(partition by brand.cnt order by brand.rn) as category,brand.brand_name
from brand

[@calmedbythewind3453]

but we have first_value() in sql

[@vaibhavverma1340]

with cte as
(select *, COUNT(category) over (order by rn)as cnt
from
(select *, row_number() over (order by (select null))rn from brands)a)

select first_value(category) over (partition by cnt order by rn)category_new , brand_name from cte

[@darshikashah3012]

In ct2 there is no rn , how can we write cte1.rn>ct2.rn ? , in ct2 we have just next_rn

[@anuragkumar-ti1vz]

In cte2 we are doing select * from cte1 so all columns of cte1 can be accessed from cte2. Now cte1 & cte2 became 2 tables so in the final select we can pick any columns from both tables.

[@indergaming3053]

/* simple and easy approach*/
select first_value(category) over (partition by new_count order by rn ) as category ,brand_name from (
select *, count(category) over (order by rn) as new_count from (
select *,
row_number () over (order by (select null)) as rn
from brands
)A)B

[@True_living_2112]

use coalesce function which is more simpler than any other process

[@rabink.5115]

could you please update with the queries. It will help a lot. Thanks

[@ShrinchaRani]

@@rabink.5115 WITH CTE AS (
select *,
ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS rn
from bars
)
SELECT category,brand_name from (
SELECT coalesce(category,CASE WHEN rn

[@lakshaykhanna2462]

MySQL solution (Would work in MSSQL as well if 'IF' is replaced by 'Case')
WITH cte_1 AS
(
SELECT category, brand_name, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM brands
), cte_2 AS
(
SELECT *,MAX(IF(category IS NOT NULL, rn, NULL)) OVER(ORDER BY RN) AS grp
FROM cte_1
)
SELECT MAX(category) OVER(PARTITION BY grp ORDER BY rn) category, brand_name
FROM cte_2

[@Deepratan27]

SELECT
CASE
WHEN ROWNUM IN (2, 3, 4) THEN 'chocolates'
WHEN ROWNUM IN (6, 7) THEN 'Biscuits'
ELSE CATEGORY
END AS CATEGORY,
BRAND_NAME
FROM brands; working fine in Oracle Sql.

[@venuamrutham6179]

SELECT @k := IF(category is not null, category, @k) as category,
brand_name FROM table;

[@prabhatgupta6415]

select category,brand_name from (select brand_name,
case when brand_name in ('5-Star','dairy milk','perk','eclair') then 'choclates' else 'Biscuits' end as category from brands1)x
Hi Ankir Sir cant we do like this please verify

[@ankitbansal6]

It's hard coding. Write a generic code

[@radhakrishnanselvaraj518]

Simple solution:
select MIN(category) OVER (ORDER BY (SELECT null) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS category,
brand_name FROM brands;

[@chetanphalak7192]

Why cant we use max, can u please explain a bit

[@sarabanv]

WITH cte1 AS
(SELECT *, ROW_NUMBER() OVER(ORDER BY null) AS id
FROM brands),
cte2 AS
(SELECT *, COUNT(category) OVER(ORDER BY id) AS grp
FROM cte1)
SELECT MAX(category) OVER(PARTITION BY grp ORDER BY id) AS category, brand_name
FROM cte2

[@ganeshsinghn6146]

;
WITH cte_seq
AS (
SELECT *
,row_number() OVER (
ORDER BY (
SELECT NULL
)
) AS rw
FROM brands
)
,cte_null
AS (
SELECT *
,sum(CASE
WHEN category IS NULL
THEN 0
ELSE 1
END) OVER (
ORDER BY rw
) AS rwn
FROM cte_seq
)
SELECT FIRST_VALUE(category) OVER (
PARTITION BY rwn ORDER BY rwn
) AS Category
,brand_name
FROM cte_null

[@gyanubhai86]

First Solution:
WITH cte1 AS(
SELECT *,
SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END)
OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp FROM brands),
cte2 AS(
SELECT category, temp FROM cte1
WHERE category IS NOT NULL)
SELECT c2.category, c1.brand_name FROM cte1 c1 JOIN cte2 c2
ON c1.temp = c2.temp
Second Solution:
with cte as (select *, COUNT(category) over (order by rn)as cnt
from
(select *, row_number() over (order by (select null))rn from brands)a)

select first_value(category) over (partition by cnt order by rn)category_new , brand_name from cte

[@syedmudasir5702]

WITH CTE1 AS
(SELECT ROW_NUMBER() Over (ORDER BY (select(NULL))) as PRODUCT_ID, category, brand_name
FROM dairy..brands),
CTE2 AS
(SELECT PRODUCT_ID, category, brand_name, COUNT(category) OVER (Order by PRODUCT_ID) as C_group
FROM CTE1)
SELECT FIRST_VALUE(category) OVER ( Partition by C_group Order by PRODUCT_ID) as CATEGORY, brand_name
FROM CTE2

[@Deepratan27]

SELECT
LAST_VALUE(CATEGORY IGNORE NULLS) OVER (ORDER BY ROWNUM) AS CATEGORY,BRAND_NAME
FROM brands;

[@deepanshugoyal2531]

My solution is more simple i think:
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER() as rn FROM Brands
)
,CTE1 AS (SELECT *, SUM(CASE WHEN category is NULL THEN 0 ELSE 1 END) OVER(ORDER BY rn) as sm FROM CTE)
SELECT FIRST_VALUE(category) OVER(PARTITION BY sm ORDER BY rn) as Brands,brand_name FROM CTE1

[@saurabhsoni1998]

Great. You missed to add order by in over clause and it won't work in SQL Server if it's empty. -- ROW_NUMBER() OVER(order by (select null))

[@deepanshugoyal2531]

It depends on which SQL database you are using

[@ankitbansal6]

Good one 👍

[@anithapoojary641]

Hi,
I badly need help to solve 2 SQL problem. I'll drop the problems here. If you know how to solve that it would help me a lot.
1. You are given two tables, Medicine and Hospital.
Medicine table containing, (ID, name, exp_date, cld)
Hospital table containing,(CID, branch,name)
Write a Query to display the Hospital name and count of medicines that it procures in ascending order of the hospital name.
2. Given two tables, Orders and Customers
Order table containing (Order_No, Purchase_amt,Cust_id,Salesman_id)
Customer table containing (cust_id,cust_name,city,grade,salesman_id)
Write a Query to display Customer's I'd followed by the grades of the customers who placed the order sort the record as per the grades in descending order.

[@shekharagarwal1004]

Thanks Ankit and really grateful to you for your knowledge sharing session.
Hi Anitha -
1 ) Write a Query to display the Hospital name and count of medicines that it procures in ascending order of the hospital name :
Please advise as I am unable to get the quantity field in Medicine table. I used the below dataset to arrive to the solution
--------------------------------------------------
Table : Medicine
--------------------------------------------------
ID,name,exp_date,cld,qty
--------------------------------------------------
M1,Crocin,10-09-2023,H1,200
M2,Omnee,10-09-2023,H1,100
M3,ibufropen,10-09-2023,H1,205
M4,sumo-cold,10-09-2023,H1,140
M3,ibufropen,10-03-2023,H1,50 ==> This medicine-M3 is repeat prescription by Hospital H1
M1,Crocin,10-09-2023,H2,300
M2,Omnee,10-09-2023,H2,230
M3,ibufropen,10-09-2023,H2,100
M4,sumo-cold,10-09-2023,H3,350
--------------------------------------------------
Table : Hospital
--------------------------------------------------
CID, branch,name
--------------------------------------------------
H1,Kolkata,Apollo
H2,Chennai,Fortis
H3,Delhi,AIIMS
H4,Kolkata,ILS
--------------------------------------------------
Result :
--------------------------------------------------
HospitalName,Total_Medicine_qty,Medicine_type_count
--------------------------------------------------
Kolkata,695,4
Chennai,630,3
Delhi,350,1
--------------------------------------------------
Psuedo Query :
SELECT
H.Name as HospitalName
, sum(M.qty) as Total_Medicine_qty
, count(distinct M.ID) as Medicine_Type_Count
FROM
Medicine M INNER JOIN Hospital H
ON M.CID=H.CID
GROUP BY
H.Name
ORDER BY H.Name;
2) Above approach will be torch-bearers to solve this problem.

[@rakeshkoll7112]

hi Ankit, Can you please write the sql for this

[@rakeshkoll7112]

get the movies played time for each movie (you dont't need to consider pause to resume)

[@ankitbansal6]

Some data please

[@rakeshkoll7112]

@@ankitbansal6 USE [Chinook]
GO
/****** Object: Table [dbo].[movies_playback] Script Date: 7/15/2022 9:37:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[movies_playback](
[device_timestamp] [datetime] NULL,
[session] [varchar](50) NULL,
[name] [varchar](50) NULL,
[action] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:00:00.000' AS DateTime), N'1', N'movie1', N'start')
GO
INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:20:00.000' AS DateTime), N'1', N'movie1', N'pause')
GO
INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:30:00.000' AS DateTime), N'1', N'movie1', N'resume')
GO
INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:42:00.000' AS DateTime), N'1', N'movie1', N'stop')
GO
INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T13:00:00.000' AS DateTime), N'2', N'movie2', N'start')
GO
INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T13:20:00.000' AS DateTime), N'2', N'movie2', N'stop')
GO

[@MrGaurav331]

with cte as (
SELECT
*,
row_number() over() as rn
FROM brands
)
, cte2 as (
SELECT
category,brand_name,rn as start,
lead(rn,1,99) over() as last
FROM cte
where category is not NULL
)

, cte3 as ( SELECT
b.*,
c.category as cat,
c.brand_name as brd
FROM
cte b
LEFT JOIN
cte2 c ON b.rn > c.start AND b.rn < c.last)

select
coalesce(category,cat)as categ,
coalesce(brand_name,brd) as brnd
from cte3

[@pravinmahindrakar6144]

WITH cte AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY NULL) AS rn FROM brands
),
cte2 AS(
SELECT *, COUNT(category) OVER(order by rn) AS cnt FROM cte
)
SELECT MAX(category) OVER(PARTITION BY cnt) AS category, brand_name FROM cte2

[@sonyguptaagrawal6028]

difficult to understand

[@gothams1195]

with cte as (select *,
row_number() over() as rn
from brands)
select *,
count(case when category is null then 0 else 1 end) over(order by rn) as y
from (select
first_value(category) over(partition by x order by rn) as category,
brand_name,rn,x
from
(select *,
count(category) over(order by rn) as x
from cte)a order by rn)b

[@DineshKumar-hf7ts]

Tried without looking into the video...Hope there is a elegant way to do it.
select *
from fill_null
where category is not NULL
union all
select first.category,second.brand_name
from(
select category,ROW_NUMBER()over(order by (select null)) as row1
from fill_null
where category is not NULL) as first
left join (
select category,brand_name,dense_rank()over(order by group_date) as row2
from(
select category,brand_name,DATEADD(day,-1*ROW_NUMBER()over(order by(select null)),con_dat) as group_date
from (select *,DATEADD(day,ROW_NUMBER()over(order by(select null)),2020-01-01) as con_dat from fill_null) as data
where category is NULL) as data1) as second
on first.row1=second.row2

[@ankitbansal6]

It is difficult to read this query because of so many sub queries. That's why I always suggest to use cte so that it is easy to read through it.

[@DineshKumar-hf7ts]

Sure... Will do that from next time 👍

[@atharvadeshpande7324]

with cte1 as
(
SELECT *
, ROW_NUMBER()OVER(ORDER BY (select 100)) as rn
FROM brands
)
SELECT *
,CASE when rn BETWEEN 1 AND 4 THEN (SELECT category from cte1 where rn =1)
when rn BETWEEN 5 AND 7 THEN (SELECT category from cte1 where rn =5) END as cat
from cte1

[@uditnarayanjoshi4115]

Always try to make dynamic query, coz if new data will come with some new categories then u have to write the query again.

[@ankitbansal6]

You can not hard code the range

[@vikramcena7131]

this qustion for freshers or experienced ones

[@ankitbansal6]

Experienced

[@vikramcena7131]

@@ankitbansal6 how they will ask for freshers easy or hard please replay bro

[@ankitbansal6]

@@vikramcena7131 intermediate

[@vikramcena7131]

@@ankitbansal6 thanku so munch bro

[@vikramcena7131]

but i havae a doubt bro how will u execute that if your data base in masters

[@user-zx1ii2cx2j]

with cte1 as
(SELECT *,lag(category) over() prev,row_number() over() rn FROM gdb023.brands)
,cte2 as(
select category,brand_name,
sum(case when category is not null and prev is null then 1 else 0 end) over(order by rn) summ
from cte1)
select c1.category,c2.brand_name from
cte2 c1 join cte2 c2 using (summ) having category is not null
(readable solution!)

[@damoncaffrey4921]

Hi Ankit, once again thank you for this amazing question and the solution as well
Here's my approach :-
WITH cte1 AS(
SELECT *,
SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END)
OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp FROM brands),
cte2 AS(
SELECT category, temp FROM cte1
WHERE category IS NOT NULL)
SELECT c2.category, c1.brand_name FROM cte1 c1 JOIN cte2 c2
ON c1.temp = c2.temp

[@ankitbansal6]

Thanks for posting 👏

[@AyushGupta-pc8ue]

Solved it in first attempt.
@ankit bansal please have a look and give your valuable feedback
with cte1 as(
select category,brand_name,lag(category,1,category) over() as prev, row_number() over() as sn from brands
),
cte2 as(
select sn,category, brand_name, prev, sum(case when prev is NULL and category is not NULL then 1 else 0 end) over(order by sn) as block from cte1
),
cte3 as (
select max(category) over(partition by block) as category, cte2.brand_name from cte2
)
select * from cte3;
+------------+------------+
| category | brand_name |
+------------+------------+
| chocolates | 5-star |
| chocolates | dairy milk |
| chocolates | perk |
| chocolates | eclair |
| Biscuits | britannia |
| Biscuits | good day |
| Biscuits | boost |
+------------+------------+

[@Tanya-og7no]

Hey this is really nice question. @ankitbansal

[@siddharth_1899]

with cte1 as (
select row_number() over(order by (select null)) as rn, *
from brands
)
, cte2 as (
select *
, count(category) over(order by rn) as cnt
from cte1
)
select FIRST_VALUE(category) over(partition by cnt order by rn ) as category
, brand_name
from cte2