วีดีโอ

WITH CTE AS(SELECT *, dense_rank()OVER(ORDER BY P1) AS R1, dense_rank()OVER(ORDER BY P2) AS R2, dense_rank()OVER(ORDER BY P3) AS R3 FROM orders),V1 AS( SELECT C1.P1,C2.P2,C1.P3,C1.R1,C1.R3 FROM CTE AS C1 JOIN CTE AS C2 ON C1.R1 = C2.R2) SELECT VO.P1,VO.P2,VOO.P3 FROM V1 AS VO JOIN V1 AS VOO ON VO.R1= VOO.R3;

Intresting

Awaiting python course❤

SELECT Combo, Cost FROM Combo WHERE SUBSTRING(Combo, 1, 1) < SUBSTRING(Combo, 3, 1) AND SUBSTRING(Combo, 3, 1) < SUBSTRING(Combo, 5, 1) ORDER BY Cost;

WITH CTE AS(SELECT *,substring_index(COMBO,',',1) AS L1, substring_index(substring_index(COMBO,',',2),',',-1) AS L2, substring_index(COMBO,',',-1) AS L3 FROM COMBO),V1 AS( SELECT *, CASE WHEN L1 < L2 AND L2 < L3 THEN 'YES' ELSE 'NO' END AS STAT FROM CTE) SELECT COMBO,COST FROM V1 WHERE STAT = 'YES';

SELECT *, SUBSTRING(Word, 1, 1) AS FirstCharacter from Combinations WHERE Word <> SUBSTRING(Word, 1, 1) ORDER BY word

WITH cte AS ( SELECT * , DENSE_RANK() OVER(PARTITION BY id,DATE(OccurenceTime) ORDER BY OccurenceTime desc ) as drn FROM LOGS) SELECT id,OccurenceTime,Message FROM cte WHERE drn=1 ;

with cte AS (SELECT *,DAY(LoginDate)- DENSE_RANK() OVER (PARTITION BY UserID ORDER BY LoginDate) AS diff FROM Logins) SELECT UserID,MIN(LoginDate) AS sd,MAX(LoginDate) AS ed FROM cte GROUP BY UserID,diff having COUNT(diff) > 1 ;

select Id,OccurenceTime,Message from ( SELECT *,ROW_NUMBER() over(partition by cast(OccurenceTime as date),id order by [OccurenceTime] desc) as rn FROM Logs ) a where rn=1 order by id

Thank you for sharing such valuable content. I have a request: could you please upload more Power BI interview questions.

with cte as( select Logs.*,DATE(OccurenceTime) as date1 FROM Logs ),cte1 as( select cte.*,DENSE_RANK()OVER(PARTITION BY ID,date1 ORDER BY OccurenceTime DESC) as r1 FROM cte ) select ID,OccurenceTime,Message FROM cte1 where r1=1 ORDER BY ID;

with cte as ( SELECT *, last_value(message) over(partition by id,date(OccurenceTime) order by OccurenceTime rows between unbounded preceding and unbounded following) as lv, last_value(OccurenceTime) over(partition by id,date(OccurenceTime) order by OccurenceTime rows between unbounded preceding and unbounded following) as lv1 FROM Logs ) select id, max(OccurenceTime) as dt,max(lv) as message from cte group by id,date(OccurenceTime)

my solution with two cte's and a left join WITH cte AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY id, DATE(occurencetime) ORDER BY occurencetime) AS rn FROM logs ORDER BY 1, 2), cte2 AS( SELECT id, DATE(occurenceTime) AS dated, MAX(rn) AS ranked FROM cte GROUP BY id, DATE(occurencetime)) SELECT i.id, j.occurencetime, j.message FROM cte2 i LEFT JOIN cte j ON i.id = j.id AND i.dated = DATE(j.occurenceTime) AND i.ranked = j.rn

Here is my solution with limit and offset and without using cte: - SELECT m.name as Employee FROM employee e join employee m on e.managerid = m.id group by Employee order by COUNT(e.name) desc limit 1 offset 1

with cte1 as( select word, length(word) as l1 from wipro ), cte2 as( select word from cte1 where l1>1 ), cte3 as( select left(word,1) as Alphabet,word from cte2 ) select * from cte3 order by word;

Using REGEXP: - WITH cte_alphabet as ( select * from Combinations where word REGEXP '^.$' ) ,cte_words as ( select * ,left(word,1) as first from combinations where word NOT REGEXP '^.$' ) select c.word as Alphabet,cw.word as Word from cte_alphabet c join cte_words cw on c.word = cw.first

Good one!

Awesome Solution! Thanks

SELECT * FROM Combinations a left join ( select * from Combinations where len(word)>1 )b on a.Word = left(b.Word,1) where b.Word is not null

with cte as (select * from Combinations where len(word)=1) select * from Combinations a inner join cte b on b.word=SUBSTRING(a.word,1,1) where len(a.word)<>1

SQL SERVER with cte as( select LEFT(REPLACE(combo, ',', ''), 1) as first , right(LEFT(REPLACE(combo, ',', ''), 2), 1) as second , RIGHT(REPLACE(combo, ',', ''), 1) as third , * from Combo) , cte2 as( select case when ASCII(first) < ASCII(second) and ASCII(second) < ASCII(third) then Combo end as combo1 , Cost from cte) select combo1 as combo, cost from cte2 where combo1 is not null

with cte as (select i.id as Invoiceid, i.BillingDate as billingdate, c.name as customername, c.referredBy from invoice i left join customers c on i.customerid=c.id group by 1) select a.invoiceid as invoiceid,a.billingdate as billingdate,a.customername as customername, b.customername as crn from cte a left join cte b on a.referredby = b.invoiceid

with cte as ( select *, ascii(left(combo,1))as t1, ascii(mid(combo,3,1)) as t2, ascii(right(combo,1)) as t3 from combo ) select combo, cost from cte where t1<t2 and t2 < t3

Here i tried solving it using MYSQL with recursive cte as ( select days,length(days) - length(replace(days,",","")) +1 as cnt from combination union select days,cnt-1 from cte where cnt >1), cte2 as ( select *, substring_index(substring_index(days,",",cnt),",",-1) as wrd from cte ) select days, max(if(wrd="F",'TRUE',null)) as friday, max(if(wrd="M",'TRUE',null)) as monday, max(if(wrd="R",'TRUE',null)) as thursday, max(if(wrd="S",'TRUE',null)) as saturday, max(if(wrd="T",'TRUE',null)) as tuesday, max(if(wrd="U",'TRUE',null)) as sunday, max(if(wrd="W",'TRUE',null)) as wednesday from cte2 as c1 join weeks as w on c1.wrd = w.letter group by days

What if there are duplicates? Can I just add a row_number as id and do the joins based that id. Does that solve the problem for duplicates?

How to add this combination table do we need to create sir

Nice bro 😊 way u teaching do some video basic to advance sql sir

bro dax meeda videos start chey bro

Bro.. With out pivot.. Tell me

Superb explanation 👌 👏 👍

with cte as( select *, ROW_NUMBER() over(partition by Origin, Destination order by Origin, Destination) as rn from Shipments) select t1.Origin, t1.Destination, t1.ShipmentCount+t2.ShipmentCount as total_shipment from cte t1 inner join cte t2 on t1.rn=t2.rn where t1.Destination=t2.Origin and t1.Origin=t2.Destination and t1.Origin='India'

SELECT * FROM EventLog WHERE LOWER(Message) = 'in-progress' OR LOWER(Message) = 'block'; can I use this

with cte as ( select Combo,left(Combo,1) as top , right(left(Combo,3),1) as mid ,right(Combo,1) as bot , cost from Combo ) select Combo,cost from cte where top < mid and mid < bot order by cost asc;

with cte as( select Combo.*,SUBSTRING(Combo,1,1) as s1,SUBSTRING(Combo,3,1) as s2,SUBSTRING(Combo,5,1) as s3 FROM Combo ) select Combo,Cost FROM cte where s1<s2 and s2<s3 ORDER BY Cost;

with cte as ( select *, ASCII(value) as rn from Combo cross apply string_split(combo, ',')) , cte2 as (select *, RANK() over (partition by combo order by rn) as rnk from cte) , cte3 as ( select *, ROW_NUMBER() over (partition by value order by rn) as r from cte2) select Combo, cost from cte3 where rnk = 3 and r = 1 order by 2

Thanks you❤

with cte as ( select *, case when CategoryID = 'B' then 'A' when CategoryID = 'A' then 'C' else 'B' end as "new_categoryid" from products_new ) ,cte2 as ( select ProductID, new_categoryid, price, row_number() over(partition by new_categoryid) as "rn" from cte ) select productID, new_categoryid,price, case when rn=1 then last_value(price) over(partition by new_categoryid) else lag(price,1) over(partition by new_categoryid) end as "new_price" from cte2; ---> neglect the columns you dont want from the last select statement

with cte1 as( Select Top 100 percent A.TestCase as x,B.TestCase as y,C.TestCase as z from TestCases A join TestCases B on A.TestCase<>B.TestCase join Testcases C on B.TestCase<>c.TestCase and c.TestCase<>a.TestCase order by x,y,z ) select x+','+y+','+z as permutations from cte1

Hi All please find my solution with abc as( select product,1 as Quantity from Products union all select product,Quantity+1 as Quantity from abc where exists(select p.Product,p.Quantity from products p where p.Product=abc.Product and abc.Quantity<p.Quantity) ) select product,1 as Quantity from abc

WITH T1 AS(select *, ROW_NUMBER() OVER(order by p1) as RN0 , ROW_NUMBER() OVER(order by p2 desc) as RN1, ROW_NUMBER() OVER(order by p3) as RN2 from Orders order by p1) select A.p1, B.p2, C.p3 from T1 A, T1 B, T1 C where A.RN0 = B.RN1 AND A.RN0 = C.RN2

WITH T1 AS (select USER_ID,post_id||day as post, COUNT() OVER(PARTITION BY USER_ID) AS COUNT from transactions ) SELECT A.USER_ID, B.USER_ID FROM T1 A, T1 B WHERE A.POST = B.POST AND A.USER_ID != B.USER_ID AND A.COUNT = B.COUNT AND ((A.USER_ID, B.USER_ID ) NOT IN ( SELECT * FROM friend) AND (B.USER_ID, A.USER_ID ) NOT IN ( SELECT * FROM friend)) GROUP BY A.USER_ID, B.USER_ID HAVING COUNT(*) =A.COUNT

With cte as( Select manager_id, count(manager_id) From employee Where manager_id is not null Group by manager_id Having count(manager_id)=2) Select name as employee_name From cte as c1 join employee as e1 On c1.manager_id=e1.id

Here is my attempt with Microsoft SQL Server Management Studio: with cte as( select *, rank() over (partition by Department order by Salary desc) as max_Sal_Dept, case when rank() over (partition by Department order by Salary desc) = 1 then 'Top Performer' when rank() over (partition by Department order by Salary desc) = 2 then 'Average Performer' else 'Worst Perfomer' end as Perfomer from employees) select * from cte;

with cte as( SELECT SessionID,MAX(TimeStamp) as timedate1 FROM EventLog GROUP BY SessionID ) select cte.SessionID,CONCAT(TimeStamp,'.000') as TimeStamp,Message FROM cte JOIN EventLog ON cte.SessionID=EventLog.SessionID and cte.timedate1=EventLog.TimeStamp ORDER BY SessionID DESC;

I also wrote the same correlated subquery but wasted space by using cte WITH cte AS( SELECT *, DENSE_RANK() OVER(PARTITION BY sessionId ORDER BY tStamp) AS rn FROM eventlog) SELECT sessionid, message FROM cte a WHERE rn = (SELECT MAX(rn) FROM cte b WHERE sessionid = a.sessionid) ORDER BY 1

P1 asc, p2 desc and p3 asc here is my solution With cte as ( SELECT P1, ROW_NUMBER() Over(order by P1) as P1_rN ,P2, ROW_NUMBER() Over(order by P2 desc) as P2_rN ,P3, ROW_NUMBER() Over(order by P3) as P3_rN FROM Orders) Select a.P1, b.P2, c.P3 from cte a inner join cte b on a.P1_rN = b.P2_rN inner join cte c on a.P1_rN = c.P3_rN

with cte as ( select *, REPLACE(FORMula, '+', ',') as plus , REPLACE(FORMula, '-', ',') as minus , REPLACE(FORMula, '*', ',') as multiply , REPLACE(FORMula, '/', ',') as divide from Operation) , CTE2 AS (SELECT ID, Value, Formula, multiply FROM cte WHERE Formula <> multiply UNION ALL SELECT ID, Value, Formula, plus FROM cte WHERE Formula <> plus UNION ALL SELECT ID, Value, Formula, divide FROM cte WHERE Formula <> divide UNION ALL SELECT ID, Value, Formula, minus FROM cte WHERE Formula <> minus) , cte3 as (select *,SUBSTRING(multiply, 0, CHARINDEX(',', multiply)) as First , SUBSTRING(multiply, CHARINDEX(',', multiply)+1, LEN(multiply)) as second , SUBSTRING(formula, 2, 1) as operator from CTE2) select c.ID, c.Formula, c.Value , case when c.operator = '+' then c1.Value + c2.Value when c.operator = '-' then c1.Value - c2.Value when c.operator = '*' then c1.Value * c2.Value when c.operator = '/' then c1.Value*1.0 / c2.Value when c.operator = '0' then c1.Value*1.0 / c2.Value end as new_value from cte3 c join cte3 c1 on c.First = c1.ID join cte3 c2 on c.second = c2.ID order by 1

more SImple Solution with cte as ( SELECT *, ROW_NUMBER() over (order by date) as rn , DATEADD(day, -1*ROW_NUMBER() over (order by date) , Date) as diff FROM Travel) select Name, MIN(origin) as Origin, MAX(destination) as Destination from cte group by Name, diff having MIN(origin) <> MAX(destination)

with cte as( select *, ROW_NUMBER() over (order by p1) R1 from orders ), cte2 as( select *, ROW_NUMBER() over (order by p2 desc) R2 from orders ), cte3 as( select *, ROW_NUMBER() over (order by p3) R3 from orders ) select cte.p1, cte2.p2, cte3.p3 from cte,cte2,cte3 where cte.R1=cte2.R2 and cte.R1=cte3.R3

Hello My solution in Sql Server 2022: with flo as ( select *, dense_rank()over(order by p1)as rnk1, dense_rank()over(order by p2 desc)as rnk2, dense_rank()over(order by p3)as rnk3 from orders ) select p1, (select p2 from flo where f.rnk1=rnk2) as p2, (select p3 from flo where f.rnk1=rnk3 )as p3 from flo f; Hope it helps.