Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Practice Aggregation

Practice Aggregation

Practice writing queries to strengthen your grasp of aggregation patterns.

We'll cover the following...

Aggregation patterns questions

Let’s practice writing queries to strengthen your grasp of aggregation patterns.

Count the late deliveries

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order.

The table contains information about the orders placed. In the Orders table, each order is optionally assigned to an employee (EmployeeID) responsible for processing or managing the delivery. The table also includes a LateDelivery field, which is a boolean indicating whether the order was delivered later than expected.

Write an SQL query to find out how many late deliveries are associated with each employee. Only include: ...