INTERVIEWERS asked these SQL QUERIES in ADOBE - FLIPKART - AMAZON 🧐 SQL Interview PRO PART-3

[@shashank_mishra]

How many of you were able to solve Question-3 without any help?😀 Paste your solution in the comment section

[@jagatkrishna1543]

Speechless 💕🙏

[@AsutoshNayak-ks9ui]

This is what I have attempted before your solution:
select user1_id,user2_id
from
(select distinct r1.user_id as user1_id,r2.user_id as user2_id,count(r1.follower_id) as count
from Relations r1
inner join Relations r2 on r1.follower_id = r2.follower_id
group by r1.user_id,r2.user_id
order by r1.user_id)X
where user1_id user2_id
and user1_id < user2_id
order by count desc
limit 1;

[@sreejitchakraborty6575]

with cte as(
select r1,r2,count(*) cc
from
(
select r1.user_id as r1,r2.user_id as r2,r2.follower_id
from relations r1 inner join
relations r2
on(r1.follower_id=r2.follower_id)
and(r1.user_id

[@neelbanerjee7875]

How about this (sql server) -
*******************************************************************************
with cte1 as
(
select t1.user_id as user1_id, t2.user_id as user2_id, count(*) as cnt
from Relations t1
join Relations t2
on t1.user_id < t2.user_id and t1.follower_id = t2.follower_id
group by t1.user_id, t2.user_id
)
select user1_id, user2_id
from cte1
where cnt = (select max(cnt) from cte1)
*******************************************************************************

[@AmitSingh-ut4wt]

WITH CTE AS
(SELECT
CASE WHEN u1u1 THEN u2 ELSE u1 END AS u2,
common_followers
FROM (
SELECT
r1.user_id as u1,
r2.user_id as u2,
COUNT(*) as common_followers
FROM relations r1 INNER JOIN relations r2
ON r1.follower_id=r2.follower_id
AND r1.user_id!=r2.user_id
GROUP BY r1.user_id, r2.user_id
) AS relations_inter)
SELECT u1 as user1_id, u2 as user2_id
FROM CTE
ORDER BY common_followers desc
LIMIT 1;

[@akashshaw9862]

Question 1: without window function
SELECT
SALE_DATE,
SUM(CASE WHEN FRUIT = 'apples' THEN SOLD_NUM ELSE -SOLD_NUM END) AS DIFF
FROM SALES
GROUP BY SALE_DATE
ORDER BY SALE_DATE
;

[@rideravi6999]

--Solution of 1st problem:
SELECT sale_date
,SUM(CASE
WHEN fruit = 'apples'
THEN sold_num
ELSE 0
END) - SUM(CASE
WHEN fruit = 'oranges'
THEN sold_num
ELSE 0
END) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date

[@thedatacenter45]

#My approach for Question3
with cte as(
select r1.user_id as user1,r1.follower_id as follower1,r2.user_id as user2,r2.follower_id as follower2
from relations r1 inner join relations r2
on r1.user_id

[@ramanreddy7276]

2. ans
SELECT 'low' as cat,count(*) from sal where salary < 20000
union
SELECT 'medium' as cat,count(*) from sal where salary between 20000 and 50000
UNION
SELECT 'high' as cat ,count(*)from sal where salary > 50000

[@janaktyagi2720]

I am so proud of solving question 3 without any external help- my solution
with cte as (select a.user_id as user1_id,
b.user_id as user2_id,
count(a.follower_id) as cnt from Relations a
inner join Relations b
on a.follower_id = b.follower_id
and a.User_id < b.User_id
where a.User_id != b.User_id
group by a.user_id,b.user_id
order by a.User_id)
select user1_id,user2_id from cte where cnt = (select max(cnt) from cte)

[@sreejitchakraborty6575]

u dont need
where a.User_id != b.User_id since you have already mentioned
a.User_id < b.User_id

[@rakeshchaudhary8255]

My Attempt on Q3 :) I felt good that it was accepted.
with calculated_tbl as (select
r1.user_id as user1_id,
r2.user_id as user2_id,
dense_rank() over (partition by r1.user_id * r1.follower_id * r2.user_id order by r1.user_id) as user_pair_rnk
from relations r1
inner join relations r2 on
r1.user_id != r2.user_id
and r1.follower_id=r2.follower_id),
total_common_follower as (
select user1_id,user2_id,sum(user_pair_rnk) total_common from calculated_tbl where user_pair_rnk=1
group by user1_id,user2_id)
select user1_id,user2_id from total_common_follower where total_common=(select max(total_common) from total_common_follower);
your solution looked simpler though.

[@nischalbpatil1825]

for #Q2
SELECT category, accounts_count
FROM (
SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count
FROM accounts
WHERE income < 20000
UNION
SELECT 'Average Salary' AS category, COUNT(*) AS accounts_count
FROM accounts
WHERE income >= 20000 AND income 50000
) AS result_table
ORDER BY
CASE category
WHEN 'Low Salary' THEN 1
WHEN 'Average Salary' THEN 2
WHEN 'High Salary' THEN 3
END;

[@pankajnegi9278]

My approach for Question 3:
WITH CTE AS
(SELECT
R1.user_id AS user1_id, R2.user_id AS user2_id, COUNT(*) AS cnt,MAX(COUNT(*)) OVER() AS max_cnt
FROM Relations R1, Relations R2
WHERE R1.user_id < R2.user_id AND
R1.follower_id = R2.follower_id
GROUP BY R1.user_id, R2.user_id
)
SELECT user1_id,user2_id FROM CTE WHERE cnt=max_cnt

[@akhandpratapsingh3190]

For ques1:
SELECt sale_date, diff
from (
SELECT *, sold_num - lead(sold_num) over(order by sale_date) as diff, row_number() over(partition by sale_date) as dif
from sales
) yyy
where dif = 1

[@neelbanerjee7875]

Problem#: 3 (sql server) -
*******************************************************************************
with cte1 as
(
select t1.user_id as user1_id, t2.user_id as user2_id, count(*) as cnt
from Relations t1
join Relations t2
on t1.user_id < t2.user_id and t1.follower_id = t2.follower_id
group by t1.user_id, t2.user_id
)
select user1_id, user2_id
from cte1
where cnt = (select max(cnt) from cte1)
*******************************************************************************

[@ramanreddy7276]

1. question ans
select sdate, ( sum(case when fruit = 'apples' then sold else 0 end ) -
sum(case when fruit = 'orange' then sold else 0 end ) ) as diff
from fruit group by sdate order by sdate;

[@Naveenkmr7994]

for the last question:
with t as (select * from logins where time_stamp like '%2020%')
select user_id, max(time_stamp) from t group by user_id;
this displays error however works fine in mysql work bench, do you know why ?

[@sound5510]

Hi sir
I hv completed my master of commerce in 2020 and also I have worked 1.3 year's in BPO sector, So now I'm planning to take course SAP, Salesforce, Data analytics and data science which one is better for me to build my career in future

[@sachinpatel1930]

Bhaiya what is fees in NIT college's for MCA 🙏🙏🙏

[@shaikusman536]

Learning a lot from you sirji.....Thanks..

[@jagatkrishna1543]

Thanks we all love SQL n plsql

[@HARSHRAJ-wz2rp]

Question 3:-
with cte as(
select * FROM Relations ORDER BY user_id
),
cte1 as(
select Relations.user_id as user_id1,Relations.follower_id as follower_id1 FROM
Relations ORDER BY user_id
),cte2 as(
select * FROM cte JOIN cte1 ON cte.user_id!=cte1.user_id1
),cte3 as(
select user_id,user_id1,COUNT(*) FROM cte2 where follower_id=follower_id1
GROUP BY user_id,user_id1
)
select user_id as user1_id,user_id1 as user2_id FROM cte3 where user_id

[@jagatkrishna1543]

Doing GREAT 🙏💕

[@jagatkrishna1543]

#lovedata

[@rishiraj2548]

👍🙏🙏

[@zoey6812]

My solution to Question 3-
WITH inter(user1_id,user2_id) AS
(SELECT r1.user_id AS user1_id,r2.user_id AS user2_id,COUNT(*) AS cnt FROM relations AS r1
JOIN relations AS r2
ON r1.user_id

[@navneetsingh6245]

Q3 was a good question, My solution for that:-
WITH users AS (
SELECT c1.user_id AS user1_id
, c2.user_id AS user2_id
, RANK() OVER(ORDER BY COUNT( c1.follower_id ) DESC) ranking
FROM
common AS c1
INNER JOIN
common AS c2
ON c1.fid = c2.follower_id
AND c2.user_id > c1.user_id
GROUP BY
1
, 2
)
SELECT user1_id
, user2_id
FROM
users
WHERE
ranking = 1