Aggregate Customer Order Data

March 24, 2023

Consider the following database tables and contents:

CustomerType     
CustomerTypeId  CustomerTypeDisplayName
1   Individual
2   Organisation

Customer         
CustomerId  CustomerName    CustomerTypeId
1   John Smith  1
2   Peterson Ltd    2
3   Paul Taylor 1
4   Janet Peters    1

Order        
OrderId CustomerId  OrderDate
1   1   2010-01-01
2   4   2010-01-02
3   2   2010-01-02
4   2   2010-02-10
5   2   2010-03-05
6   1   2011-10-01
7   4   2011-11-01
8   2   2012-05-01

Given the database above please write a SQL query that would return the data in the following format:

CustomerName    CustomerTypeDisplayName NumberOfOrders  EarliestOrderDate
John Smith  Individual  2   2010-01-01

… … … …

>Solution :

The following query shall written you the data in required format as in this DBFIDDLE.

SELECT 
  c.CustomerName, 
  ct.CustomerTypeDisplayName, 
  COUNT(o.OrderId) AS NumberOfOrders, 
  MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
JOIN CustomerType ct ON c.CustomerTypeId = ct.CustomerTypeId
LEFT JOIN [Order] o ON c.CustomerId = o.CustomerId
GROUP BY 
  c.CustomerName, 
  ct.CustomerTypeDisplayName;

Please make sure to surround order table_name with square brackets as [order] as order is a reserved keyword in sql server.

The above query shall give you result as :

CustomerName    CustomerTypeDisplayName     NumberOfOrders      EarliestOrderDate
Janet Peters    Individual                  2                   2010-01-02
John Smith      Individual                  2                   2010-01-01
Paul Taylor     Individual                  0                   null
Peterson Ltd    Organisation                4                   2010-01-02