Interview Guides

Segment Orders by Delivery Outcome | Dataford Interview Questions - Dataford - Ace your Interview

Segment Orders by Delivery Outcome

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsCase WhenData Wrangling

Also asked at

Problem

Task

SwiftCart wants a simple delivery performance breakdown from its orders table. Write a PostgreSQL query that uses a CASE statement to segment each order into a delivery outcome bucket, then returns the number of orders in each bucket.

Requirements

Create a derived column called delivery_outcome using these rules:
- Delivered when delivered_at is not null and delivered_at <= promised_date
- Delivered Late when delivered_at is not null and delivered_at > promised_date
- Cancelled when status = 'cancelled'
- In Progress for all other rows
Return delivery_outcome and order_count
Group by the derived outcome and sort by order_count descending, then delivery_outcome ascending

Table Definition

column_name	type	description
order_id	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

order_id	customer_name	status	order_date	promised_date	delivered_at
101	Ava Patel	delivered	2024-03-01	2024-03-05	2024-03-04
102	Liam Chen	delivered	2024-03-01	2024-03-04	2024-03-06
103	Noah Kim	cancelled	2024-03-02	2024-03-06	null
104	Emma Ross	shipped	2024-03-03	2024-03-07	null
105	Olivia Reed	delivered	2024-03-03	2024-03-08	2024-03-08
106	Mia Lopez	cancelled	2024-03-04	2024-03-09	null

Expected Output

delivery_outcome	order_count
Delivered	4
Cancelled	3
In Progress	3
Delivered Late	2

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status such as delivered, shipped, processing, or cancelled
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

Example 1

Input

{"orders":[[108,"Sophia Turner","processing","2024-03-05","2024-03-10",null],[101,"Ava Patel","delivered","2024-03-01","2024-03-05","2024-03-04"],[110,"Isabella Cruz","delivered","2024-03-06","2024-03-09","2024-03-11"],[103,"Noah Kim","cancelled","2024-03-02","2024-03-06",null],[112,"Ethan Brooks",null,"2024-03-07",null,null],[105,"Olivia Reed","delivered","2024-03-03","2024-03-08","2024-03-08"],[107,"Lucas Green","delivered","2024-03-05","2024-03-07","2024-03-06"],[102,"Liam Chen","delivered","

Output[["Delivered","13"],["Delivered Late","8"],["In Progress","5"],["Cancelled","3"]]

Problem

Task

Requirements

Create a derived column called delivery_outcome using these rules:
- Delivered when delivered_at is not null and delivered_at <= promised_date
- Delivered Late when delivered_at is not null and delivered_at > promised_date
- Cancelled when status = 'cancelled'
- In Progress for all other rows
Return delivery_outcome and order_count
Group by the derived outcome and sort by order_count descending, then delivery_outcome ascending

Table Definition

column_name	type	description
order_id	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

order_id	customer_name	status	order_date	promised_date	delivered_at
101	Ava Patel	delivered	2024-03-01	2024-03-05	2024-03-04
102	Liam Chen	delivered	2024-03-01	2024-03-04	2024-03-06
103	Noah Kim	cancelled	2024-03-02	2024-03-06	null
104	Emma Ross	shipped	2024-03-03	2024-03-07	null
105	Olivia Reed	delivered	2024-03-03	2024-03-08	2024-03-08
106	Mia Lopez	cancelled	2024-03-04	2024-03-09	null

Expected Output

delivery_outcome	order_count
Delivered	4
Cancelled	3
In Progress	3
Delivered Late	2

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status such as delivered, shipped, processing, or cancelled
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

Example 1

Input

{"orders":[[108,"Sophia Turner","processing","2024-03-05","2024-03-10",null],[101,"Ava Patel","delivered","2024-03-01","2024-03-05","2024-03-04"],[110,"Isabella Cruz","delivered","2024-03-06","2024-03-09","2024-03-11"],[103,"Noah Kim","cancelled","2024-03-02","2024-03-06",null],[112,"Ethan Brooks",null,"2024-03-07",null,null],[105,"Olivia Reed","delivered","2024-03-03","2024-03-08","2024-03-08"],[107,"Lucas Green","delivered","2024-03-05","2024-03-07","2024-03-06"],[102,"Liam Chen","delivered","

Output[["Delivered","13"],["Delivered Late","8"],["In Progress","5"],["Cancelled","3"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Bucket Users by Order CountEasy IAverage Delivery Time by RegionEasy Monthly Sales Summary for StoreEasy

Next question

Segment Orders by Delivery Outcome

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsCase WhenData Wrangling

Also asked at

Problem

Task

Requirements

Create a derived column called delivery_outcome using these rules:
- Delivered when delivered_at is not null and delivered_at <= promised_date
- Delivered Late when delivered_at is not null and delivered_at > promised_date
- Cancelled when status = 'cancelled'
- In Progress for all other rows
Return delivery_outcome and order_count
Group by the derived outcome and sort by order_count descending, then delivery_outcome ascending

Table Definition

column_name	type	description
order_id	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

order_id	customer_name	status	order_date	promised_date	delivered_at
101	Ava Patel	delivered	2024-03-01	2024-03-05	2024-03-04
102	Liam Chen	delivered	2024-03-01	2024-03-04	2024-03-06
103	Noah Kim	cancelled	2024-03-02	2024-03-06	null
104	Emma Ross	shipped	2024-03-03	2024-03-07	null
105	Olivia Reed	delivered	2024-03-03	2024-03-08	2024-03-08
106	Mia Lopez	cancelled	2024-03-04	2024-03-09	null

Expected Output

delivery_outcome	order_count
Delivered	4
Cancelled	3
In Progress	3
Delivered Late	2

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status such as delivered, shipped, processing, or cancelled
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

Example 1

Input

{"orders":[[108,"Sophia Turner","processing","2024-03-05","2024-03-10",null],[101,"Ava Patel","delivered","2024-03-01","2024-03-05","2024-03-04"],[110,"Isabella Cruz","delivered","2024-03-06","2024-03-09","2024-03-11"],[103,"Noah Kim","cancelled","2024-03-02","2024-03-06",null],[112,"Ethan Brooks",null,"2024-03-07",null,null],[105,"Olivia Reed","delivered","2024-03-03","2024-03-08","2024-03-08"],[107,"Lucas Green","delivered","2024-03-05","2024-03-07","2024-03-06"],[102,"Liam Chen","delivered","

Output[["Delivered","13"],["Delivered Late","8"],["In Progress","5"],["Cancelled","3"]]

Problem

Task

Requirements

Create a derived column called delivery_outcome using these rules:
- Delivered when delivered_at is not null and delivered_at <= promised_date
- Delivered Late when delivered_at is not null and delivered_at > promised_date
- Cancelled when status = 'cancelled'
- In Progress for all other rows
Return delivery_outcome and order_count
Group by the derived outcome and sort by order_count descending, then delivery_outcome ascending

Table Definition

column_name	type	description
order_id	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

order_id	customer_name	status	order_date	promised_date	delivered_at
101	Ava Patel	delivered	2024-03-01	2024-03-05	2024-03-04
102	Liam Chen	delivered	2024-03-01	2024-03-04	2024-03-06
103	Noah Kim	cancelled	2024-03-02	2024-03-06	null
104	Emma Ross	shipped	2024-03-03	2024-03-07	null
105	Olivia Reed	delivered	2024-03-03	2024-03-08	2024-03-08
106	Mia Lopez	cancelled	2024-03-04	2024-03-09	null

Expected Output

delivery_outcome	order_count
Delivered	4
Cancelled	3
In Progress	3
Delivered Late	2

Schema

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Current order status such as delivered, shipped, processing, or cancelled
order_date	DATE	Date the order was placed
promised_date	DATE	Expected delivery date
delivered_at	DATE	Actual delivery date, null if not delivered

Sample Data

Example 1

Input

{"orders":[[108,"Sophia Turner","processing","2024-03-05","2024-03-10",null],[101,"Ava Patel","delivered","2024-03-01","2024-03-05","2024-03-04"],[110,"Isabella Cruz","delivered","2024-03-06","2024-03-09","2024-03-11"],[103,"Noah Kim","cancelled","2024-03-02","2024-03-06",null],[112,"Ethan Brooks",null,"2024-03-07",null,null],[105,"Olivia Reed","delivered","2024-03-03","2024-03-08","2024-03-08"],[107,"Lucas Green","delivered","2024-03-05","2024-03-07","2024-03-06"],[102,"Liam Chen","delivered","

Output[["Delivered","13"],["Delivered Late","8"],["In Progress","5"],["Cancelled","3"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Bucket Users by Order CountEasy IAverage Delivery Time by RegionEasy Monthly Sales Summary for StoreEasy

Next question