Meesho SQL HackerRank Question | Medium-Level

[@DataWithIvan]

Thank you for the explanation. I am even more thankful for the ability to copy sample tables and input them into my SQL. At 0:58 you say that the moving average should start calculating once 3 months of data are available. At the same time Expected Output contains average adata for January and February which contradicts the condition (as there is no 3 months history for Jan and Feb). I thought we have to omit these first 2 months as they are lacking data.

[@anikeetdey3411]

Nice explanation ..keep going .

[@dawgydawg2232]

postgresql: with month_table as( select *, extract(month from order_date) as month from Orders_2 ), total_sales as( select month, sum(amount) as total_sale from month_table group by month order by month ) select month, total_sale, round((avg(total_sale) over (order by month rows between 2 preceding and current row)),2) as moving_avg from total_sales

[@nirmalaswain4598]

please continue uploading this types of video

[@echodelta7680]

So CUSTOMERS table wasn't needed here. A nice question for practice.✌✌

[@siddardhch9655]

I got question in meesho interview to find who are cousins from table containing columns father and son

[@datasciencewithnish]

@@siddardhch9655 Can you please share the complete question on my Email?

[@HARSHRAJ-gp6ve]

Bhai can you provide dml for same

[@khilafat9468]

​@@datasciencewithnish 1 little suggestion. Plzz try to upload your videos in 1080p 60Fps, or try to zoom in the My SQL canvas becoz it's hard to see the text. Thnks. You are really doing a great work

[@karansaikumargali1939]

select * from table t1 join table t2 on t1.father!=t2.father

[@Pooja_Bhanu]

How the avg sales amount will calculate for Jan Feb months ? How we get put 100 and 225 can u give exp. For this?

[@anukritisahni9684]

I agree. For Jan & Feb, there should be no values.

[@devarajululanka6427]

SQL SERVER : with monthly_sales as ( select datepart ( month,order_date ) as ordered_month,sum (amount) as total_amount from orders group by datepart ( month,order_date ) ) select ordered_month, total_amount, round(avg (total_amount) over ( order by ordered_month rows between 2 preceding and current row ),2) as running_sales from monthly_sales group by ordered_month, total_amount

[@VARUNTEJA73]

with cte as( select order_id,total_value,ranks,lag(ranks)over(order by order_id)counts from( select order_id,total_value,lag(total_value)over(order by order_id)ranks from( select *,row_number()over(partition by month(order_date)order by order_id )dates from( select order_id,customer_id,order_date,sum(amount)over(partition by month(order_date))total_value from orders )t1)t2 where t2.dates=1)t3) select order_id,total_value,ranks,counts,( select sum(sum_value)from (values (total_value),(ranks),(counts))as x(sum_value))/ (select count(count_value)from(values(total_value),(ranks),(counts))as x(count_value)) as sm_value from cte

[@HARSHRAJ-gp6ve]

This is my query with cte as( select SUBSTRING(Order_Date,1,7) as date_month,Amount FROM Orders ),cte1 as( select date_month,SUM(Amount) as amount1 FROM cte GROUP BY date_month ),cte2 as( select cte1.*,COALESCE(LAG(amount1)OVER(ORDER BY date_month),0) as amount2, COALESCE(LAG(amount1,2)OVER(ORDER BY date_month),0) as amount3 FROM cte1 ),cte3 as( select cte2.*,(amount1+amount2+amount3) as expected_amount FROM cte2 ) select date_month,amount1, CASE when amount2=0.00 and amount3=0.00 THEN ROUND((expected_amount/1),2) when amount2!=0.00 and amount3=0.00 THEN ROUND((expected_amount/2),2) ELSE ROUND((expected_amount/3),2) END AS moving_average FROM cte3;

[@muthuraja1528]

my Output as WITH monthly_sales AS ( SELECT SUM(sales) AS total_sales, MONTH(date) AS month FROM sales GROUP BY MONTH(date) ORDER BY month ASC ), previous_month AS ( SELECT total_sales, month, LAG(total_sales, 1) OVER (ORDER BY month ASC) AS prev_revenue, LAG(total_sales, 2) OVER (ORDER BY month ASC) AS PP_revenue FROM monthly_sales ) SELECT month, total_sales, (total_sales + COALESCE(prev_revenue, 0) + COALESCE(PP_revenue, 0)) / 3 AS avg_sales_last_3_months FROM previous_month ORDER BY month ASC;

[@user-gq6cg3ls7f]

with cte as( select distinct concat(year(order_date),'-0', month(order_date)) month, SUM(amount) over (partition by month(order_date) order by month(order_date)) monthly_sales from Orders_Mondays ) select month, monthly_sales, avg(monthly_sales) over (order by month rows between 2 preceding and current row) moving_avg from cte

[@theinsightminer08]

WITH monthly_sales_tbl AS (SELECT DATE_FORMAT(Order_Date,'%Y-%m') AS Month, SUM(Amount) AS Monthly_Sales FROM Orders GROUP BY Month) SELECT Month, Monthly_Sales, ROUND(AVG(Monthly_Sales) OVER(ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ),2) AS Moving_Avg_Sales FROM monthly_sales_tbl;