Amazon SQL Interview: Handle NULLs Like a Pro | Advance SQL Interview Question

n this video, we walk through an Amazon SQL interview question where we create and manage a jobs table that contains NULL values in certain rows. We’ll explore how to handle NULL values in SQL and perform calculations like summing the expenditure and person days for various jobs.
🔑 Key Topics Covered:
How to create a table with VARCHAR and INT data types.
Dealing with NULL values in SQL queries.
Using aggregate functions like SUM() to handle NULL safely.
SQL query optimization for better performance.
Perfect for those preparing for Amazon SQL interviews or SQL enthusiasts looking to improve their skills.
👍 Don’t forget to like, comment, and subscribe for more SQL tutorials and interview tips!
.
.
.
.
#amazoninterview #sqltutorial #sqlqueries #sqlinterview #handlingnullvalues #sqlaggregates #jobsandexpenditure #sqltips #sqlskills #databasemanagement #sqlforbeginners #interviewprepsql
.
.
.
.
-------------------------------------CREATE TABLE----------------------------------------
-- Create the table
CREATE TABLE jobs (
workname VARCHAR(50),
person_days INT,
expenditure INT
);
-- Insert the data
INSERT INTO jobs VALUES ('Minor Irrigation Tank', 1500, 256258);
INSERT INTO jobs VALUES (NULL, 586, 356258);
INSERT INTO jobs VALUES (NULL, 586, 65258);
INSERT INTO jobs VALUES (NULL, 125, 65258);
INSERT INTO jobs VALUES ('Feeder Channel', 105, 19000);
INSERT INTO jobs VALUES (NULL, 15, 1900);
INSERT INTO jobs VALUES (NULL, 152, 17100);
INSERT INTO jobs VALUES (NULL, 5, 1500);

[@shahzan525]

subscribed because of this video

[@sravankumar1767]

Nice explanation 👌 👍 👏

[@Thedata.techclub]

Thanks! Glad you found it helpful.

[@sravankumar1767]

If we have N number of rows we can't write N number of lag functions right?

[@Thedata.techclub]

Big Thanks to ask this question.
yes, we can't write if we have 10000 rows for that. That was the temporary solution and demonstration of lag function to deal with nulls.
If we have such situations and we do not want to write LAG so i have a solution for this. Please check the code below and ask me if you have any questions.
with cte as (
select workname,person_days, expenditure,
row_number() over (order by (select null)) rn1
from jobs
),
Ncte as (
select workname,person_days, expenditure,
row_number() over (order by (select null)) - 1 rn2
from cte
)
SELECT
t1.workname,
(SELECT top 1 t2.workname FROM Ncte t2
WHERE t2.rn2 < t1.rn1 AND t2.workname IS NOT NULL
ORDER BY t2.rn2 DESC ) New_workname,t1.person_days, t1.expenditure
FROM cte t1;

[@chandanpatra1053]

No need to write multiple times lag() . Here is the simple script
with cte as
(
select *,
ROW_NUMBER() over(order by (Select Null)) as rnk
from jobs_1
),
cte1 as
(
select *,
sum(case when workname is not null then 1 else 0 end) over(order by rnk) as flag
from cte)
select first_value(workname) over(partition by flag order by rnk) as workname,
person_days,expenditure
from cte1