Microsoft Interview Question
Software Engineer / DevelopersCountry: United States
Interview Type: Phone Interview
CREATE TABLE Customers(Cust_Id INT, Cust_Name VARCHAR(50), Age INT)
CREATE TABLE Orders(Order_Id INT, Cust_Id INT, Order_Date DATETIME)
INSERT INTO Customers VALUES (1,'ABC',30), (2,'ABC2',35), (3,'ABC3',33), (4,'ABC4',34), (10,'ABC10',30)
INSERT INTO Orders VALUES(100,1, GETDATE()), (101,2, GETDATE()), (103,3, GETDATE()), (110,1, GETDATE()), (120,1, GETDATE()), (104,4, GETDATE())
SELECT DISTINCT c.Cust_Id, c.Cust_Name, COUNT(1) AS [TotalOrders] FROM Customers c
INNER JOIN Orders o ON c.Cust_Id = o.Cust_Id
GROUP BY c.Cust_Id, c.Cust_Name
Is it total number of orders or total amount of orders. Table structure is not complete either..
select o.cust_id,c.cust_name,sum(o.amount) as total_amt from orders o
left outer join customers c on
o.cust_id=c.cust_id
group by o.cust_id,c.cust_name
having count(o.order_id) >0
OR
select o.cust_id,c.cust_name,count(o.order_id) as total_orders from orders o
left outer join customers c on
o.cust_id=c.cust_id
group by o.cust_id,c.cust_name
having count(o.order_id) >0
SELECT c.cust_id,
- Anonymous March 15, 2016COUNT(o.order_id) ct
FROM customer c,
order1 o
WHERE c.cust_id = o.cust_id
GROUP BY c.cust_id
HAVING COUNT(o.order_id) >= 1
ORDER BY ct DESC