Conditional Count

Another SQL job interview question I came across recently. This question tests your understanding of SQL Group Functions. Given the following tables:

Orders table

This table represents the orders of a package delivery company. As you can see, each row represents one order, and has four columns:

• OrderDate – holds the order date.
• CustomerTypeID – holds the customer type such that 1 represents a private customer, and 2 represents a business customer.
• ShipTypeID – holds the shipping type such that L represents a domestic shipping, and O represents an international shipping.
• Quantity – holds the number of units which will be delivered.

Customers table

This table represents the type of customers this company has. As you can see, each row represents different type of customers, and has two columns:

• CustomerTypeID – holds the customer type number.
• CustomerTypeDescription – holds the customer type description.

Your task:

Create a query to display the total number of units sent by international and domestic shipping, for each day, and for each customer type.

CREATE TABLE orders
(OrderDate date,
 CustomerTypeID int,
 ShipTypeId varchar(1),
 Quantity int)
 
 CREATE TABLE Customers
 (CustomerTypeID int,
  CustomerTypeDescription varchar(25)) 
 
  INSERT INTO orders VALUES(getdate(), 1, 'L' , 20)
  INSERT INTO orders VALUES(getdate(), 1, 'L' , 15)
  INSERT INTO orders VALUES(getdate(), 1, 'O' , 22)
  INSERT INTO orders VALUES(getdate(), 1, 'O' , 38)
 
  INSERT INTO orders VALUES(getdate(), 1, 'O' , 72)
  INSERT INTO orders VALUES(getdate(), 2, 'L' , 54)
  INSERT INTO orders VALUES(getdate(), 2, 'O' , 12)
  INSERT INTO orders VALUES(getdate(), 2, 'L' , 80)

SELECT S.OrderDate, CT.CustomerTypeID, CT.CustomerTypeDescription , 
       SUM(CASE WHEN S.ShipTypeID = 'O' THEN S.Quantity END) AS 'Internationl Shipping',
       SUM(CASE WHEN S.ShipTypeID = 'L' THEN S.Quantity END) AS 'Local Shipping'       
FROM Sales S, CustomerType CT
WHERE S.CustomerTypeID = CT.CustomerTypeID
GROUP BY S.OrderDate, CT.CustomerTypeID, CT.CustomerTypeDescription