#sql to achieve last NOT NULL value from the record | Analytical Function | Data Engg | Data Analyst

Hi All,
My name is Ankit Shrivastava and I am a Data Engineer.
Today this #vlog is regarding #sql to achieve last NOT NULL value from the record list. This question is becoming common now a days for #etl developers #etltesting #dataengineering #dataanalysts #datanalysis #datanalytics #bigdata

Please find the DDL statement below for exercise;-
create table last_not_null (id integer, product_qty integer);
insert into last_not_null values (101,1000);
insert into last_not_null values (102,2000);
insert into last_not_null values (103,null);
insert into last_not_null values (104,3000);
insert into last_not_null values (105,null);
insert into last_not_null values (106,4000);

#sql #sqlinterviewquestions #etl developers #dataengineer #dataanalysts
#sqlinterviewquestions #interviewready #sqlcourse #sqlcommands #sqlcount #postgresql #sqlinterviewquestionsandanswers #value
#sqlserver #amazon #meta #walmart #google #databaseexercises #dataengineeringessentials #salary #oracle #sqlcourse #sqlinterview
#trendinginterview #cartesianproducts #interview #oracle #interviewtips #mysql #interviewready #interviewpreparation #sqljoins #dailylifeofdataengineer #dailyvlog #magic #sqlmagic
#highest #second #interviewready #interviewhacks #complex #complexscenario #scenarios #queries #plsql #postgresql #postgres

[balamuralibattala]

Select id, ifnull(product_qty, lag(product_qty, 1) over(order by id)) as product_qty
From last_not_null. Worked this in mysql.

[vijaygupta]

with cte as(
Select *, count(product_qty)over(order by(select null) rows between unbounded preceding and 0 following) as grp from last_not_null)
Select id, first_value(product_qty)over(partition by grp order by grp) as product_qty from cte

[balamuralibattala]

Everything is good, but one thing missed i am thinking is that the explanation what exactly happend after the use of firstvalue .If that also added then it will be super.Please consider it.Thank you for sharing your knowledge ❤

[satishchaurasiaya]

Nice approach❤. If first_value or last_values is not support in sql version then do you have any other approach? Please share.

[chethanmotebennur]

SELECT ID, PRODUCT_QTY, LAST_VALUE(PRODUCT_QTY IGNORE NULLS) OVER(ORDER BY ID)AS NEW_PRODUCT_QTY FROM LAST_NOT_NULL;

[ntlgwzo]

Why we are getting 2 2times and 3 2times?

You said since we were using Count. Can you elaborate?

[shashank]

Sorry sir, but we didn't understand how query works?