DSTI evaluation (March 2021)

Using the database WideWorldImporters, write a SQL query which reports the consistency between orders and their attached invoices. The resultset should report for each (CustomerID, CustomerName)

• a. the total number of orders: TotalNBOrders
• b. the number of invoices converted from an order: TotalNBInvoices
• c. the total value of orders: OrdersTotalValue
• d. the total value of invoices: InvoicesTotalValue
• f. the absolute value of the difference between c - d: AbsoluteValueDifference

The resultset must be sorted by highest values of AbsoluteValueDifference, then by smallest to highest values of TotalNBOrders and CustomerName is that order.

Please note that all values in a & b must be identical by definition of the query, as we are observing orders converted into invoices.

We are looking for potential differences between c & d.

BUT, you must find them consistent as the data is clean in WideWorldImporters.