SQL Interview Questions and answers Part 5 | Amazon SQL Interview Question And Answer Part-3

SQL / ORACLE Scenario Based Interview Questions Part 5 | Amazon SQL Interview Question And Answer Part-3 | SQL Scenario based Interview Question and Answer

This question has been asked in Amazon interview for the role of Data Analyst
Order_Tbl has four columns namely ORDER_DAY, ORDER_ID, PRODUCT_ID, QUANTITY and PRICE
(a) Write a SQL to get all the products that got sold on both the days and the number of times the product is sold.
(b) (b) Write a SQL to get products that was ordered on 02-May-2015 but not on 01-May-2015

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :
*Twitter:

-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------

CREATE TABLE [Order_Tbl](
[ORDER_DAY] date,
[ORDER_ID] varchar(10) ,
[PRODUCT_ID] varchar(10) ,
[QUANTITY] int ,
[PRICE] int
)

INSERT INTO [Order_Tbl] VALUES ('2015-05-01','ODR1', 'PROD1', 5, 5)
INSERT INTO [Order_Tbl] VALUES ('2015-05-01','ODR2', 'PROD2', 2, 10)
INSERT INTO [Order_Tbl] VALUES ('2015-05-01','ODR3', 'PROD3', 10, 25)
INSERT INTO [Order_Tbl] VALUES ('2015-05-01','ODR4', 'PROD1', 20, 5)
INSERT INTO [Order_Tbl] VALUES ('2015-05-02','ODR5', 'PROD3', 5, 25)
INSERT INTO [Order_Tbl] VALUES ('2015-05-02','ODR6', 'PROD4', 6, 20)
INSERT INTO [Order_Tbl] VALUES ('2015-05-02','ODR7', 'PROD1', 2, 5)
INSERT INTO [Order_Tbl] VALUES ('2015-05-02','ODR8', 'PROD5', 1, 50)
INSERT INTO [Order_Tbl] VALUES ('2015-05-02','ODR9', 'PROD6', 2, 50)
INSERT INTO [Order_Tbl] VALUES ('2015-05-02','ODR10','PROD2', 4, 10)

#AmazonDataEngineer #AmazonSQLInterview #AmazonInterview #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sql InterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #ITJunction4All

[schymi]

It's really cool that you're posting those insert statements in the description. Really helpful for creating the examples in local db.

[FlauntWith_Kanika]

Questions are really great for practice :) Thank You !! Here is the solution from my perspective -- Sol 1:
select distinct product_id, count from (
select *, lag(order_date) over(partition by product_id order by order_date) as prev_date
, count(product_id) over(partition by product_id ) as count
from t) where prev_date < order_date;

Sol2: (derived from Sol1)
select distinct product_id, count from (
select *, lag(order_date) over(partition by product_id order by order_date) as prev_date
, count(product_id) over(partition by product_id ) as count
from t ) where prev_date is null and order_date = '2015-05-02';

[okygy]

Really awesome. Thanks for giving us the complete details in description to try.

[TheEsisia]

Thanks for sharing such a great series of interview questions!

The solution for (a) is correct only under the assumption that there are only 2 distinct ORDER_DAYs. In order to generalize the solution:

SELECT PRODUCT_ID, COUNT(PRODUCT_ID) AS [COUNT] FROM [Order_Tbl]
GROUP BY PRODUCT_ID
HAVING COUNT(DISTINCT ORDER_DAY) = (SELECT COUNT(DISTINCT ORDER_DAY) FROM [Order_Tbl] )

[prajjwaljaiswal]

I really appreciate the video and description section. Please keep doing this.


You can share your feedback on this solution.

Part 1
with cte as (select order_day, product_id, count(Product_id) as ct_prd_id from order_Tbl group by order_day, product_id)
select product_id, sum(ct_prd_id) from cte group by product_id having sum(ct_prd_id) > 1;
;

[TheUltimateTruth]

This exercises are really helpful..Thanks alot for sharing..!!

[shakiraafshan]

all your videos are really interesting

[srinivaspachika]

Awesome series of videos Mr. Sunil. Thank you so much for making this series. we would love to see more videos on SQL and data warehouse concepts.

[ajaxaj]

Good solution. I got a complicated idea of using both lag and lead functions

[paroolsingh]

Very helpful and thanks for putting the insert statements in the comments.

[keta]

I would have written:
SELECT product_id, count(distinct order_id)
FROM Order
GROUP BY product_id
HAVING count(distinct order_day) > 1

Would this be correct?

[Rakesh]

Please make more scenario based questions videos. All your videos are good. Thanks for making these videos.

[NomanKhan]

Sunil bahi great and very unique videos, amazing.... Please provide Input table also in the

[prabhatgupta]

1-select product_id, count(product_id) from order_tbl group by product_id having count(distinct order_day)=2;


2-select product_id from (select product_id from order_tbl where order_day='2015-05-02')x where product_id not in
(select product_id from order_tbl where order_day='2015-05-01')


please verify sir.
and when you r going to bring python videos.? Its been a long. We are waiting.
Thanks

[mohitbaranawal]

My solution
Ques-1
select product_id, count(order_id) counta
from Order_Tbl
group by PRODUCT_ID
having count(distinct ORDER_DAY)>1


ques -2
select distinct product_id from Order_Tbl
where ORDER_DAY = '2015-05-02' and PRODUCT_ID
not in (select distinct product_id from Order_Tbl
where ORDER_DAY = '2015-05-01')

[tejaskifamily]

Please create more videos on SQL interview questions

[lakshitbhardwaj]

Another approach would be to use an Anti Join. It would run better for larger datasets.

[shakiraafshan]

a) with cte as (select distinct product_id,
count(*) over (partition by product_id order by product_id) as count
from [order_tbl])
select * from cte where count > 1

b) select product_id from order_tbl
where order_day like '2015-05-02' and order_day not like '2015-05-01'
order by product_id

[ranajparida]

Hello Bro,

Thanks for your nice video.. In this 1st scenario I think the dates should be mentioned in place of No column name.. I mean to say that i which particular date those product got sold. Any how I have attached the code below which fulfilled the missing part .

SELECT PRODUCT_ID prod_id, COUNT(distinct(order_date)) sold_cnt,
listagg(order_date, ' <<-->> ') within group(order by order_date DESC) Date_involved
FROM
TRANSCATION
GROUP BY PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY product_id;
Regards,
Ranaj

[vijay.s-llyq]

with cte as
(select *, count(product_id) over (partition by product_id) product_count from Order_Tbl)
select product_id, count(product_count) as count from cte
group by PRODUCT_ID
having count(product_count) >1


select product_id from Order_Tbl where [ORDER_DAY] ='2015-05-02'

except
select product_id from Order_Tbl where [ORDER_DAY] ='2015-05-01'