Day 12: Solving Flipkart SQL Interview Questions | 100 Days Challenge | Group By | PostgreSQL
ฝัง
- เผยแพร่เมื่อ 17 ต.ค. 2024
- Welcome to Day 12 of my 100 Days Challenge! Today, we're diving into UBER SQL interview questions to help you ace your next tech interview.
Join me as I walk through each problem step-by-step, providing detailed explanations and practical tips. Don't forget to like, subscribe, and hit the bell icon to stay updated with daily challenges!
Join the 100 Days Challenge Community:
Discord: / discord
Get the question & datasets
GitHub: github.com/naj...
Relevant Playlists:
SQL Interview Prep: • SQL Challenge - Data A...
#AmazonInterview #SQLInterviewQuestions #100DaysChallenge #DataScience #SQLTutorial #TechInterviews #DataAnalysis #LearnSQL #InterviewPrep #CodingChallenge #TechCareer #SQLPractice #AmazonTech #DataEngineering #PythonInterview #ExcelTips"
![[UNCUT] "บอสณวัฒน์" แฉ! แหล่งซุกเงินแก๊งบอส ขยันผิดที่ไม่กี่ปีก็เข้าคุก l คนดังนั่งเคลียร์ l17ต.ค.67](http://i.ytimg.com/vi/BL56qIfXYZM/mqdefault.jpg)
88 days to go.. Please post more of this level. Thanks. Take care
sql server solution fro 1st question :
with cte as (
select p.category,DATEPART(month,sale_date)as order_month,avg(amount) as avg_amount from products p
left join sales s
on
p.product_id = s.product_id
group by category,DATEPART(month,sale_date)
),
cte2 as (select *,
rank () over ( partition by category order by avg_amount desc ) as rn
from cte
)
select * from cte2
where rn
actually, there is a mistake in the 2nd answer. The total amount spent by a customer should be equal to the SUM (quantity * amount) . But here, you have only considered amount.
Nevertheless, the answer is the same ('Bob')
Hi, could you please provide the solution in MySql too? I believe there is a difference in syntax.
You can mention it in this comment. Also, please use MySQL as a lot of the people, infact majority of the people use MySql
MYSQL SOLUTION:
with cte as (
select C.customer_name as cust_name, C.customer_id as cust_id, sum(quantity * amount) as total_spent
from customers_demo C join Sales_demo S on C.customer_id = S.customer_id
where sale_date between '2023-11-24' and '2023-11-27'
group by C.customer_name, C.customer_id)
select cust_name, cust_id, total_spent
from cte
where total_spent in (select max(total_spent) from cte )
@@Chathur732 could you please tell me how did you do it ? I struggle when it comes to altering the solution into MySql from PostgreSql. You help will he really appreciated
@@Chathur732 hi chatur
Here is the solution for both the questions :
-- Answer 1
with cte as (select p.category,extract(month from sale_date) as month,
avg(amount) as avg_monthly_sales,
dense_rank() over(partition by extract(month from sale_date) order by avg(amount) desc) as rn
from product p
join sales s
on p.product_id=s.product_id
group by 2,1
order by 2,3 desc )
select month,category,avg_monthly_sales
from cte
where rn=1
order by 1,2 desc;
-- 2nd Answer
with cte as (select s.customer_id,c.customer_name,sum(s.amount) as total_amount,
dense_rank() over(order by sum(s.amount) desc) as rn
from customer c
join
sales s
on c.customer_id=s.customer_id
where sale_date between '2023-11-24' and '2023-11-27'
group by 2,1
order by 3 desc)
select customer_name,customer_id,total_amount
from cte
where rn=1;
For second one my solution below
Please check
select customer_name,amount,nov_day from
(
select * , day (sale_date) as nov_day from
(
select * from
(
select c.customer_name,
s.amount,
s.sale_date ,
month(sale_date) as nov_months
from
sales as s
join
customers as c
on
s.customer_id = c.customer_id ) as sub
where nov_months = 11
order by amount desc) as subquery) as subquy
where nov_day BETWEEN 24 and 27 order by amount desc, nov_day asc limit 1;
for 2nd question answer is :
select top 1 s.customer_id,c.customer_name,sale_date,max(amount) as max_amount
from sales s
left join customers c
on
s.customer_id = c.customer_id
where sale_date between '2023-11-24' and '2023-11-27'
group by s.customer_id,c.customer_name,sale_date
order by max_amount desc
Great!