Відео

but you not explain how you sort product like A,A,A,B,B,B,null,null,null my solution :- WITH cte AS ( SELECT product, region, SUM(sales) OVER(PARTITION BY product ORDER BY sales) AS r, ROW_NUMBER() OVER(PARTITION BY product ORDER BY sales DESC) AS rn FROM sales_rollup ) SELECT product, NULL AS region, r AS total_sales FROM cte WHERE rn = 1 UNION ALL SELECT NULL AS product, region, SUM(sales) AS total_sales FROM sales_rollup GROUP BY region UNION ALL SELECT NULL AS product, NULL AS region, SUM(sum_r) AS total_sales FROM ( SELECT region, SUM(sales) AS sum_r FROM sales_rollup GROUP BY region ) a UNION ALL SELECT product, region, SUM(sales) OVER(PARTITION BY product ORDER BY sales) AS total_sales FROM sales_rollup (i'm thinking to create temporary table then put all things in it after that sort )

Awesome! Content It requires a lot of hardwork for producing the content. Thankyou so much

Interesting

Are you working as a data engineer in Intel? Are you from NIT/IIT ? If no then share your journey how you reached there. If yes then ignore.

Cant we do this by two inner join's it will give common between all three tables

Hi, Please check the below scenario, as i'm getting different answer from my approach. For Ride = 10, it was requested_at 2020-03-04 and driver = 7 joined at 2020-03-08. Meaning only 3 drivers were available for ride_id 10 namely 10, 8, 5. Below is my approach. with RideCNT as ( Select CONVERT(varchar(6), R.requested_at, 112) as [YearMonth] , COUNT(R.ride_id) as RideReqCNT , COUNT(AR.ride_id) as RideAcceptCNT from Rides R LEFT JOIN AcceptedRides AR ON R.ride_id = AR.ride_id GROUP BY CONVERT(varchar(6), R.requested_at, 112) -- ORDER BY 1 ) , DriverCNT as ( Select CONVERT(nvarchar(6), R.requested_at, 112) As YearMonth , COUNT(DISTINCT R.ride_id) as RideReqCNT , COUNT(distinct driver_id) as DriverAvailCNT from Rides R LEFT JOIN Drivers D ON R.requested_at >= D.join_date GROUP BY CONVERT(nvarchar(6), requested_at, 112) ) Select A.YearMonth, A.RideReqCNT, B.DriverAvailCNT, A.RideAcceptCNT , CASE WHEN DriverAvailCNT = 0 THEN 0 ELSE RideAcceptCNT * 100.0 / DriverAvailCNT END AS working_percentage from RideCNT A INNER JOIN DriverCNT B ON A.YearMonth = B.YearMonth;

SELECT COALESCE( a.Id , b.Id) AS ID , CASE WHEN a.Name != b.Name THEN 'Source Mismatch' WHEN a.Name IS NULL THEN 'New In Target' WHEN b.Name IS NULL THEN 'New In Source' END as Comment FROM Source a FULL OUTER JOIN Target b ON a.Id = b.Id WHERE CASE WHEN a.Name != b.Name THEN 'Source Mismatch' WHEN a.Name IS NULL THEN 'New In Target' WHEN b.Name IS NULL THEN 'New In Source' END IS NOT NULL

Solve more questions on join and analytical functions

Hi, while using UNION ALL method to get the result in required format. I use below method. with cte as ( Select Product, Region, Sales AS TotalSales from Sales UNION ALL Select Product, NULL as Region , SUM(Sales) AS TotalSales From Sales GROUP BY Product UNION ALL Select Null as Product, NULL as Region , SUM(Sales) AS TotalSales From Sales ) Select * from cte Order by (CASE WHEN Product IS NULL THEN 'zz' ELSE Product END), (CASE WHEN Region IS NULL THEN 'zz' ELSE Region END);

Please bring more real-time power BI interview questions, like you do for SQL

Thanks for the new approach...

Hi, Below is my Solution. Please check your query result, as when i ran with 14 matches, there is some descrepency at winning_% and sequencing of teams For me, KKR > GT is coming. Declare @MN INT = 14; with teams as ( Select TeamA As[Team] from IPLResults UNION Select TeamB As[Team] from IPLResults ) , cte as ( Select TeamA as [Team], WinningTeam from IPLResults WHERE MatchN <= @MN UNION ALL Select TeamB, WinningTeam from IPLResults WHERE MatchN <= @MN ) , cte_final as ( Select T.Team , Count(CT.Team) as Played , SUM(CASE WHEN T.Team = CT.WinningTeam THEN 1 ELSE 0 END) AS Win , SUM(CASE WHEN T.Team <> CT.WinningTeam AND CT.WinningTeam <> 'NA' THEN 1 ELSE 0 END) AS Loss , SUM(CASE WHEN CT.WinningTeam = 'NA' THEN 1 ELSE 0 END) AS Draw from teams T LEFT JOIN cte CT ON T.Team = CT.Team Group by T.Team ) Select * , Win * 2 + Draw*1 as Points , Win *100.0 / Played as [Winning %] from cte_final Order by Points desc, [Winning %] DESC;

Really a brain storming question in powerBI . Thanks for bringing up such questions. Keep it up. 👍👍. It would be better if you provide those excel datasets gdrive link for our practice purpose .

simple approach----- with cte as (select a.id as id,a.valuea as vals from tablea2 as a join tableb as b on a.id=b.id and a.valuea=b.valueb), cte2 as(select b.id as id,b.valueb as vals from tableb as b join tablec as c on b.id=c.id and b.valueb=c.valuec) select c1.* from cte as c1 left join cte2 as c2 on c1.id=c2.id

Glad to know

Hello bud, are we under the strict instructions to only 'JOIN' the tables or would it be ok to use 'Union'? Also, may I know where was this interview question asked? Thanks for the continuous content uploads, it helps a ton of folks!

Nice one brother keep up the great work 😊

Hi, Below is my approach. with cte as ( Select A.AttemptID, A.PlayerID, A.Score , MAX(CASE When B.AttemptID = 1 THEN A.Score - B.Score END) AS [Different First] , MAX(CASE When B.AttemptID = 3 THEN A.Score - B.Score END) AS [Different Last] , MAX(CASE WHEN A.AttemptID = 1 THEN 1 WHEN A.AttemptID = B.AttemptID + 1 AND A.Score >= B.Score THEN 1 ELSE 0 END ) AS [Is Previous Score Lower] from PlayerScores A INNER JOIN PlayerScores B ON A.PlayerID= B.PlayerID GROUP BY A.AttemptID, A.PlayerID, A.Score -- Order by A.PlayerID, A.AttemptID ) , cte_IMprv as ( Select PlayerID, CASE WHEN COUNT(AttemptID) = SUM([Is Previous Score Lower]) Then 1 ELSE 0 END AS [Is OverAll Improved] from cte group by PlayerID ) Select M.*, IM.[Is OverAll Improved] from cte M LEFT JOIN cte_IMprv IM ON M.PlayerID = IM.PlayerID Order by PlayerID, AttemptID

I use other window function SELECT *,DENSE_RANK()over(partition by department_id order by name) as rn FROM employees order by employee_id

select i.a, i.b, sum(case when e.a = i.a and e.b = i.a then e.shipments + i.shipments end) as total_shipments from exp_imp e join exp_imp i on e.a = i.b and e.b = i.a and e.shipments < i.shipments group by i.a, i.b

is this a right approach using join? with cte as( select a.ID as id1,a.ValueA,b.ID as id2,b.ValueB,c.ID as id3,c.ValueC,case when a.ID=b.ID and a.ValueA=b.ValueB then 0 when b.ID=c.ID and b.ValueB=c.ValueC then 1 when c.ID=a.ID and c.ValueC=a.ValueA then 2 end flag, coalesce (case when a.ID=b.ID and b.ID=c.ID then 5 end,4) flag2 from TableA a FULL join TableB b on a.ID=b.ID AND A.ValueA=B.ValueB full join TableC c on b.ID=c.ID and b.ValueB=c.ValueC full join TableA aa on c.ID=aa.ID and c.ValueC=aa.ValueA )select id2,valueb from cte where flag2<>5 and flag in (0,1)

Select salesrepid from (Select *,count(Salesrepid) as CNTfrom (Select * from (Select SalesrepId,row_Number() over (Partition by Salesrepid,SalesType) as RN from Orders)N where RN = 1) group by Salesrepid)N where CNT=1

Select *,Case When Amount=Min(Amount) over (Partition by Sales_person Order by Amount asc) Then "Lowest" When Amount=Max(Amount) over (Partition by Sales_person Order by Amount Desc) Then "Highest" When Amount=Avg(Amount) over (Partition by Sales_person Range between unbounded preceding and unbounded following) Then "Average" When Amount>Avg(Amount) over (Partition by Sales_person Range between unbounded preceding and unbounded following) Then "Above Average" When Amount<Avg(Amount) over (Partition by Sales_person Range between unbounded preceding and unbounded following) Then "Below Average" end as Category from sale order by Sales_person,amount;

is this correct ? with cte as ( SELECT a.id , a.ValueA FROM TABLEA A JOIN TableB B ON A.ID=B.ID AND A.VALUEA=B.VALUEB union select b.ID , b.ValueB from tableb b join tablec c on b.id=c.ID and b.ValueB=c.ValueC union select a.ID, a.ValueA from TableA a join TableC c on a.ID=c.id and a.ValueA=c.ValueC) select * From cte;

MYSQL Solution Select Name,Marks,Years,Pre_Year from (Select *, Case When Marks>=Pre_Year then 1 else 0 end as Flag from (Select *,Lag(Marks) over (Partition by Name Order by Years) as Pre_Year from Marks)N)N Where Flag=1;

MySQL: WITH RankedData AS ( SELECT Region, revenue, ROW_NUMBER() OVER (PARTITION BY Region ORDER BY revenue) AS rn, COUNT(*) OVER (PARTITION BY Region) AS cnt FROM sales2 ), PercentilePositions AS ( SELECT Region, revenue, rn, cnt, 0.9 * (cnt - 1) + 1 AS percentile_pos FROM RankedData ), InterpolatedData AS ( SELECT Region, revenue AS lower_revenue, LEAD(revenue) OVER (PARTITION BY Region ORDER BY revenue) AS upper_revenue, rn, FLOOR(percentile_pos) AS lower_rn, CEIL(percentile_pos) AS upper_rn, percentile_pos FROM PercentilePositions ) SELECT *, CASE WHEN lower_rn = upper_rn THEN lower_revenue ELSE lower_revenue + (upper_revenue - lower_revenue) * (percentile_pos - lower_rn) END AS percentile_90 FROM InterpolatedData WHERE rn = lower_rn -- OR rn = upper_rn ORDER BY Region;

Thanks for new function, I have learn new function today

sql intemediate advanced topics videos/resources cheyyandi bro

SELECT *, CASE WHEN SALARY >=2000 AND SALARY <=3000 THEN 1 WHEN SALARY >=3500 AND SALARY <=4000 THEN 2 WHEN SALARY >=4500 AND SALARY <=5000 THEN 3 WHEN SALARY >=6000 AND SALARY <=7000 THEN 4 END SALARY_BUCKET FROM EMPLOYEES ORDER BY SALARY

WITH CTE AS (SELECT * ,SUM(WEIGHT)OVER(ORDER BY LINEORDER ) AS CUMMSUM FROM ELEVATORORDER) SELECT NAME FROM CTE WHERE CUMMSUM<2000 ORDER BY LINEORDER DESC LIMIT 1

with cte as( select*, percent_rank() over(order by salary asc) as rn from employees ) select employee_id, salary, case when rn = 0 then '1' when rn = 0.125 then '1' when rn = 0.25 then '1' when rn = 0.375 then '2' when rn = 0.5 then '2' when rn = 0.625 then '3' when rn = 0.75 then '3' when rn = 0.875 then '4' else '4' end as Bucket from cte

Good question. Please keep posting such questions on DAX.

SELECT Region, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Amount) OVER (PARTITION BY Region) AS median_amount FROM Sales;

SELECT Region, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Amount) OVER (PARTITION BY Region) AS percentile_90 FROM table_name;

Well explained without predefined functions

Informative 🙂🙂👌👌👌make more such query based questions related to SQL (most important)

EE is associated with EE and shld not be return, only GG shld b return going by your own logic. Kindly review

Here is my approach but i want to know if i don't use cte will there be any performance issue...? select top 2 DATENAME(dw,timestamp)dw,case when format(timestamp,'HH')<12 then 'morning' when format(timestamp,'HH')<17 then 'afternoon' else 'evening' end Time,count(orderid)order_cnt from orders group by DATENAME(dw,timestamp), case when format(timestamp,'HH')<12 then 'morning' when format(timestamp,'HH')<17 then 'afternoon' else 'evening' end order by count(orderid) desc

Brother please mention the question in description section or in blog site.