big4 sql interview questions and answers | row number | coalesce in sql

Create Statement :
===============
CREATE TABLE ProductPriceChanges (
ProductID INT NOT NULL, -- Product identifier
NewPrice INT NOT NULL, -- Price with two decimal places
ChangeDate DATE NOT NULL -- Date when the price changed
);
INSERT INTO ProductPriceChanges (ProductID, NewPrice, ChangeDate)
VALUES
(1, 10, '2024-08-11'),
(1, 20, '2024-08-12'),
(2, 20, '2024-08-14'),
(1, 40, '2024-08-16'),
(2, 50, '2024-08-15'),
(3, 90, '2024-08-18');
Question : Determine the price of each product as of '2024-08-16'.
If no price change occurred on or before this date, assign a default price of 10.
🔔 Don't forget to Like, Share, and Subscribe to DEwithDhairy for more Data Engineering content!
Need Help ? Connect With me 1:1 - topmate.io/dew...
Let's connect on LinkedIn : / dhirajgupta141
pyspark 30 days challenge : • pyspark 30 days challenge
top interview question and answer in pyspark :
• top interview question...
PySpark Installation and Setup : • Spark Installation | P...
DSA In Python Interview Series : • dsa for data engineer ...
PySpark Interview Series : • pyspark interview ques...
Pandas Interview Series : • pandas interview quest...
SQL Interview Series : • sql interview question...
#sql #dataengineers #big4

[@manjunathb.n7465]

Hi Sir, Thanks for all your videos. Can you please make video on how to read private API data incrementally with credentials and bearer token using databricks?

[@webdeveloper-q1i]

Bhaiya aap DSA for DE playlist continue kro na please. Waisa content poore youtube pe nahi hai kahi.
For aap python ke liye "Ankit bansal" bn jaoge

[@user-gq6cg3ls7f]

with cte as(
select *,
ROW_NUMBER() over (partition by ProductID order by ChangeDate desc) RN
from ProductPriceChanges
)
select ProductID,
case when ChangeDate='2024-08-16' then NewPrice
when ChangeDate'2024-08-16' then cast(10 as varchar)
End newPrice
from cte where RN=1

[@DEwithDhairy]

Great approach.

[@sanrhn]

Nice approach