Capgemini SQL Interview Question 2024 | Retrieve the total revenue generated from each product

[@Harini-x7t]

q1 ) select p1.ProductName,p1.productid,sum(p1.productprice * p2.quantity) as total from products p1 inner join orders p2 on p1.productid=p2.productid group by p1.ProductName,p1.productid q2) select * from Products where ProductID not in (select productid from orders)

[@datasciencecorner]

Keep rocking :)

[@theinsightminer08]

1) SELECT p.productname, SUM(o.quantity * p.productprice) AS total_revenue FROM Products p INNER JOIN Orders o ON p.productid = o.productid GROUP BY p.productname; 2) SELECT p.productname FROM Products p LEFT JOIN Orders o ON p.productid = o.productid WHERE o.orderid IS NULL;

[@datasciencecorner]

Nice. Keep practicing :)

[@madhusudhanreddyt2838]

in sql server (ssms) -- Retrieve the total revenue generated from each product. ;with cte_totalrevenue as ( select p.ProductName, p.ProductPrice * o.Quantity as Revenue from products as p inner join orders as o on p.productid = o.productid ) select ProductName, SUM(Revenue) as Total_Revenue from cte_totalrevenue group by ProductName order by ProductName; select p.ProductName, SUM(p.ProductPrice * o.Quantity) as Total_Revenue from Products as p inner join Orders as o on p.ProductID = o.ProductID group by p.ProductName order by p.ProductName; -- Find products that have not been ordered at all. select p.ProductName, o.OrderID from products as p left outer join orders as o on p.productid = o.productid where o.OrderID is null; -- using subquery select p.ProductName from Products as p where p.ProductID not in (select o.ProductID from Orders as o)

[@datasciencecorner]

Nice :)