Day 12: Solving Flipkart SQL Interview Questions | 100 Days Challenge | Group By | PostgreSQL

[@vinothkumars7421]

88 days to go.. Please post more of this level. Thanks. Take care

[@saktibiswal6445]

10:52 - How did the query run successfully when there is an order by clause in the cte? In MS SQL Server I always get an error.

[@Chathur732]

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')

[@devarajululanka6427]

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

[@rishabhchauhan8612]

But we have to find average sale not average amount so for that 1stly we add a new column like total_sale
(quantity * amount) or do we direct put avg on amount please reply

[@Savenature635]

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;

[@akshitarora6596]

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

[@Chathur732]

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 )

[@akshitarora6596]

@@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

[@manojmuddana5295]

@@Chathur732 hi chatur

[@santhoshkumars3967]

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;

[@devarajululanka6427]

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

[@zero_analyst]

Great!