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 :)

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

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