ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

[@DarnasiChakravarthy]

You are content is too good

[@datasculptor2895]

Thank you

[@ksahiti1227]

Thanks

[@ksahiti1227]

Your content is too good. All the interviews which I attended got these questions sir. I cracked interview by watching your videos☺ Please take this amount as my token of appreciation.

[@user-gq6cg3ls7f]

another approach with cte as( select *, count(*) over (partition by category order by productID desc) RN from Products_Thus ), cte2 as( select *, ROW_NUMBER() over (partition by category order by productID) RN from Products_Thus ) select c2.ProductID, c.Product, c.Category from cte c inner join cte2 c2 on c.RN=c2.RN and c.Category=c2.Category order by c2.ProductID

[@rohitsinghbisht1101]

Your content is good... please if you can guide so i have cmpleted and understood all type of statement so now how to improve? wether i solve situation based queries + Project or just focus on projects only /? i need gudance as i am thinking about switching to data analytics role

[@datasculptor2895]

Hi.. If you have covered all topics theoretically, I suggest you practice a lot of questions. This will improve your skills. Once you are confident on solving a sql question, then work on some projects

[@rohitsinghbisht1101]

@@datasculptor2895 thanks.. But how good do I have to become? I mean becz there will always be hard questions and how to find some ...

[@datasculptor2895]

@@rohitsinghbisht1101 you start with easy questions. Learning is a never ending process. You become strong only with experience

[@pavanakumarpk2525]

Have the habit of keeping the table creation and schema details

[@datasculptor2895]

Sure sir. Any other suggestions sir? By the way it is already in description

[@pavanakumarpk2525]

If u keep the copied details directly in the description, there won't be any redirects to other

[@sandydalhousie]

Another approach here as well: with cte as (select ProductID, Product, Category, ROW_NUMBER() over(partition by Category order by ProductID) as rnk from products1), cte2 as (select ProductID, Product, Category, ROW_NUMBER() over(partition by Category order by ProductID desc) as rnk1 from products1), cte3 as (select cte.ProductID, cte2.Product, cte.Category from cte join cte2 on cte.rnk=cte2.rnk1 and cte.Category=cte2.Category) select * from cte3 order by ProductID;

[@datasculptor2895]

Nice!

[@tanujreddy8597]

for how much experience this question can be asked

[@datasculptor2895]

3-5 yrs

[@tanujreddy8597]

@@datasculptor2895 ok thanks sir

[@Manifestion_kannada]

Order by 1 means I don't know about this Explain me order by 1 or order by 2 means

[@datasculptor2895]

Order by 1 means sorting the first column that is present in the select statement. Order by 2 means sorting the second column that is present in the select statement.

[@Manifestion_kannada]

@@datasculptor2895 i try applying 1 and 2

[@datasculptor2895]

Order by 1,2

[@HARSHRAJ-gp6ve]

with cte as( select Products.*,ROW_NUMBER()OVER(PARTITION BY Category ORDER BY ProductID) as x1 FROM Products ),cte1 as( select ProductID as id1,Product as product1,Category as category1,ROW_NUMBER()OVER (PARTITION BY Category ORDER BY ProductID DESC) as x2 FROM Products ),cte2 as( select * FROM cte JOIN cte1 ON cte.x1=cte1.x2 and Category=category1 ) select cte2.ProductID,product1,cte2.Category FROM cte2 JOIN Products ON cte2.ProductID=Products.ProductID;

[@tanujreddy8597]

SELECT productid, productname, productcategory FROM ( SELECT ROWNUM r, productid FROM ( SELECT productid FROM product ORDER BY product.productcategory, product.productid DESC ) ) a, ( SELECT ROWNUM r, productname, productcategory FROM ( SELECT productname, productcategory FROM product ORDER BY product.productcategory, product.productid ) ) b WHERE a.r = b.r ORDER BY PRODUCTID;

[@harshitsalecha221]

WITH cte1 AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY category ORDER BY category, Productid DESC) as rn, ROW_NUMBER() OVER(PARTITION BY category ORDER BY category, Productid) as rn1 FROM products) SELECT c2.productid,c1.product,c1.category FROM cte1 c1 INNER JOIN cte1 as c2 ON c1.category=c2.category AND c1.rn=c2.rn1 ORDER BY c2.productid;