WITH cte AS (SELECT DATE_FORMAT(created_at, "%b-%y") AS ym, DATE_FORMAT(created_at, "%m-%y") AS y_m, SUM(amount) AS total FROM MoM GROUP BY ym,y_m ORDER BY y_m) SELECT ym, total, COALESCE(ROUND((total - LAG(total) OVER(ORDER BY y_m)) / LAG(total) OVER(ORDER BY y_m) * 100,2),0) AS 'Mom_diff%' FROM cte;

select distinct Year(Shipment_Dt) Years, DATENAME(MONTH,Shipment_Dt) Months, count(*) over (partition by Year(Shipment_Dt), DATENAME(MONTH,Shipment_Dt) ) cnt from Shipment_Month order by Years,Months

SELECT YEAR(Shipment_Dt) AS years, MONTHNAME(Shipment_Dt) AS months, COUNT(Subject_ID) AS Total_Orders FROM Shipment_Month GROUP BY years,months;

Just a Correction it will be Last two Transactions WITH cte AS (SELECT b.Name, t.transaction_date, t.Amount, ROW_NUMBER() OVER(PARTITION BY b.Name ORDER BY t.transaction_date DESC) AS rn FROM transactions t INNER JOIN bank b ON b.Bank_id = t.Bank_id) SELECT Name, SUM(Amount) AS total FROM cte WHERE rn <= 2 GROUP BY Name;

Amazing

Nice explanation bro 👍 👌 👏

Can you put this as an update to fill in the nulls

thanks for your explanation

Nice explanation. Thank you sir!

with a as ( SELECT *, row_number() over(partition by user_id order by orderdate) rn FROM users_visit ) select a.orderdate ,count(case when b.user_id is not null then 1 end) ,count(case when b.user_id is null then 1 end) from a left join a b on a.user_id = b.user_id and a.name = b.name and a.orderdate = b.orderdate and b.rn = 1 group by a.orderdate

;with cte_output as ( select User_Id, Name, Orderdate, ROW_NUMBER() OVER (PARTITION BY User_Id ORDER BY OrderDate) as RNo from users_visit ) select Orderdate, SUM(CASE WHEN RNo = 1 THEN 1 ELSE 0 END) as New_User, SUM(CASE WHEN RNo > 1 THEN 1 ELSE 0 END) as Repeat_User from cte_output group by Orderdate;

Nice explanation bro 👍 👌 👏

with cte as( select *, AVG(sales) over (partition by city ) avg_city_sales, AVG(sales) over () avg_country_sales from city_data ) select distinct city, avg_city_sales from cte where avg_city_sales>avg_country_sales

With cte as ( Select *, Row_Number() over ( Partition by user_id order by orderdate asc) as rn From users_visit) Select order_date, Sum( Case when rn= 1 then 1 else 0 end ) as new_users, Sum( Case when rn > 1 then 1 else 0 end) as returning_users From cte Group by 1 Order by 1

Comprehensively explained!

with cte as( select *, sum(run_scored) over(partition by player_name order by innings_date) as run_sum from runs ),cte1 as( select *,first_value(innings_date) over(partition by player_name order by innings_date ) as Target_Reached from cte where run_sum >100 ) select distinct player_name , target_reached from cte1 order by 2

with cte as( select ename, edept,salary, dense_rank() over(partition by edept order by salary) as asc_, dense_rank() over(partition by edept order by salary desc) as desc_ from emp) select edept, max(case when asc_=1 then ename end) as min_ename, max(case when desc_=1 then ename end) as max_ename from cte group by 1

you said city sales greater than avg sales??? select City, SUM(Sales) as City_Sales from city_data group by City having SUM(Sales) > ( select AVG(Sales) as Avg_Sales from city_data)

proportion % select Name, CAST(CAST(CAST(SUM(Prize) as decimal)/CAST((select SUM(Prize) from ProportionData) as decimal) * 100 as decimal(10, 2)) as varchar) +'%' as Proportion_Percent from ProportionData group by Name

-- find gender percentage select Gender, CAST(ROUND(CAST(COUNT(*) as float)/CAST((select COUNT(*) from GenderData) as float) * 100, 2) as varchar)+'%' as GenderPercent from GenderData group by Gender -- select Gender, SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) as MaleCount, SUM(CASE WHEN Gender = 'Female'THEN 1 ELSE 0 END) as FemaleCount from GenderData group by Gender;

select Name, CAST(CAST(CAST(SUM(Prize) as decimal)/CAST((select SUM(Prize) from ProportionData) as decimal) * 100 as decimal(10, 2)) as varchar) +'%' as Proportion_Percent from ProportionData group by Name -- ;with cte_temp as ( select Name, Prize, SUM(Prize) OVER (ORDER BY Name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as total from ProportionData ) select Name, CAST(ROUND(cast(Prize as float)/cast(total as float) * 100, 2) as varchar)+'%' as proportion from cte_temp

;with cte_1 as ( select EName, EDept, Salary, ROW_NUMBER() OVER (PARTITION BY EDept ORDER BY Salary DESC) as Rdesc from SampleData ), cte_2 as ( select EName, EDept, Salary, ROW_NUMBER() OVER (PARTITION BY EDept ORDER BY Salary ASC) as Rasc from SampleData ) select c1.EDept as EDept, c1.EName as EName_Min, c2.EName as EName_Max from cte_2 as c1 inner join cte_1 as c2 on c1.EDept = c2.EDept where Rasc = 1 and Rdesc = 1;

;with cte_MoM as ( select FORMAT(created_at, 'MMM-yy') as Month_Year, FORMAT(created_at, 'MMyyyy') as MonthYear_No, SUM(amount) as Total_Amount from MoM group by FORMAT(created_at, 'MMM-yy'), FORMAT(created_at, 'MMyyyy') ) select Month_Year, Total_Amount, LAG(Total_Amount) OVER (ORDER BY MonthYear_No) as PreviousMonthAmount, Total_Amount - LAG(Total_Amount) OVER (ORDER BY MonthYear_No) as MoM_Varience, ISNULL(CAST(ROUND(CAST((Total_Amount - LAG(Total_Amount) OVER (ORDER BY MonthYear_No)) as float)/CAST(LAG(Total_Amount) OVER (ORDER BY MonthYear_No) as float) * 100, 2) as varchar)+'%', 0) as MoM_Growth from cte_MoM order by MonthYear_No; Thank you

-- using the common table expressions ;with cte_employee as ( select e.empid, e.salary,e.deptid, d.deptname, ROW_NUMBER() OVER (PARTITION BY e.deptid ORDER BY e.salary DESC) as RNo from employee as e inner join department as d on e.deptid = d.deptid ) select empid, salary, deptid, deptname from cte_employee where cte_employee.RNo = 2; -- using the derived table concept select empid, deptid, deptname, salary from ( select emp.empid, emp.salary, emp.deptid, dept.deptname, ROW_NUMBER() OVER (PARTITION BY dept.deptname ORDER BY emp.salary DESC) as RNo from employee as emp inner join department as dept on emp.deptid = dept.deptid ) as temp where temp.RNo = 2; Thank you

-- using derived table select [Shipping Year], [Shipping Month], COUNT(UQ_Key) as [Total Orders] from ( select CONCAT(Shipping_ID, Subject_ID) as UQ_Key, DATENAME(YEAR, Shipment_Dt) as [Shipping Year], DATENAME(MONTH, Shipment_Dt) as [Shipping Month] from Shipment_Month ) as temp group by [Shipping Year], [Shipping Month] order by [Shipping Year]; -- using cte ;with cte_shipping as ( select CONCAT(Shipping_ID, Subject_ID) as UQ_Key, DATENAME(YEAR, Shipment_Dt) as [Shipping Year], DATENAME(MONTH, Shipment_Dt) as [Shipping Month] from Shipment_Month ) select [Shipping Year], [Shipping Month], COUNT(UQ_Key) as [Total Orders] from cte_shipping group by [Shipping Year], [Shipping Month] order by [Shipping Year];

-- using pivot select S_ID, [English], [Math], [Science], [Sports] from ( select S_ID, Subject, Marks from sub_sum ) as Source_Data PIVOT ( SUM(Marks) FOR Subject IN ([English], [Math], [Science], [Sports]) ) as Pivot_Data; -- without pivot select S_ID, SUM(CASE WHEN Subject = 'English' THEN Marks ELSE 0 END) as [English], SUM(CASE WHEN Subject = 'Math' THEN Marks ELSE 0 END) as [Math], SUM(CASE WHEN Subject = 'Science' THEN Marks ELSE 0 END) as [Science], SUM(CASE WHEN Subject = 'Sports' THEN Marks ELSE 0 END) as [Sports] from sub_sum group by S_ID order by S_ID; -- select Customer_Name, SUM(CASE WHEN YEAR(Order_Date) = 2020 THEN Amount ELSE '' END) as [Sum 2020], SUM(CASE WHEN YEAR(Order_Date) = 2021 THEN Amount ELSE '' END) as [Sum 2021], SUM(CASE WHEN YEAR(Order_Date) = 2022 THEN Amount ELSE '' END) as [Sum 2022] from years_sum group by Customer_Name order by Customer_Name; Thank you for refreshing my sql query analysis

to create a table in power bi environment, we can use the ENTER DATA, which provides a GUI to create the table easily using DAX, we can use the DATATABLE function or else we can use the Table Constructors.. To create a duplicate copy of the table using DAX, easy way is to use the ALL function

Kindly write the complete question on the top of tables.

Kindly write the complete question on top of tables .

Nice job

Thnx 👍🏻

Keep uploading these questions. Very helpful🙏

Nice 👌

Nice 👌

select year(Shipment_Dt) as year, monthname(Shipment_Dt) as month, count(*) as total_orders from Shipment_Month group by year, month;

pls share dataset too

The way u handle question is anazing

Your use of c*** is not an appropriate alias dying pmsl

Hmm, a republish might be in order 🫢

with cte as( select table1.*,ROW_NUMBER()OVER() as x1 FROM table1 ),cte1 as( select category,x1 FROM cte where category!='Null1' ),cte2 as( select category,x1,COALESCE(LEAD(x1)OVER()-1,(select COUNT(*) FROM table1)) as x2 FROM cte1 ) select cte2.category,cte.Brand FROM cte2 JOIN cte ON cte.x1 BETWEEN cte2.x1 and cte2.x2; Mohit sir,if you find my query correct then react on my query,i just update null tonull1 to reduce chance of error

please share the DDL and DML scripts also

Wow very nice 👌

Nice,👌

"very poor choice of aliases" 😂 Jk thanks great question and answer

Bro atleast provide create & insert statement in the description box. So that We can practice from our side. Take this as a suggestion. Keep it up this sql series.👍👍👍👍👍👍

Nice

this is good stuff! thank you!!

It worked thank you 🙏 Why we need to keep last measure name in between inverted commas any reason for that?

Wonderful interview you covered all the main questions and the answers were crisp to the point.

You may need to add 1 to your final formula. Some date difference are negative in your calculation