ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

[@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.

[@DarnasiChakravarthy]

You are content is too good

[@datasculptor2895]

Thank you

[@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

[@mychoice7126]

Create table bdrproducts (
Product_Id int primary key,
Product_Name varchar(15),
Product_Type varchar(15)
);
Insert into bdrproducts values(1,'Laptop','Electranics')
Insert into bdrproducts values(2,'SmartPhone','Electranics')
Insert into bdrproducts values(3,'Tablet','Electranics')
Insert into bdrproducts values(4,'Headphone','Accessories')
Insert into bdrproducts values(5,'Smartwatch','Accessories')
Insert into bdrproducts values(6,'Keyboard','Accessories')
Insert into bdrproducts values(7,'Mouse','Accessories')
Insert into bdrproducts values(8,'Monitor','Accessories')
Insert into bdrproducts values(9,'Printer','Electranics');

[@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

[@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

[@tanujreddy8597]

for how much experience this question can be asked

[@datasculptor2895]

3-5 yrs

[@tanujreddy8597]

@@datasculptor2895 ok thanks sir

[@Iamthebestthing]

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.

[@Iamthebestthing]

@@datasculptor2895 i try applying 1 and 2

[@datasculptor2895]

Order by 1,2

[@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!

[@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;