Interview Guides

Detect Post-Launch Customer Behavior Shifts | Dataford Interview Questions - Dataford - Ace your Interview

Detect Post-Launch Customer Behavior Shifts

Medium

SQL & Data Manipulation

Asked at 2 companies2Window FunctionsCTEsDate Functions

Also asked at

Problem

Task

NovaCart launched a new product on 2024-04-01 and wants to identify customers whose purchasing behavior changed afterward. Write a SQL query to compare each customer's activity in the 30 days before launch vs. the 30 days after launch.

Requirements

For each customer, calculate pre_orders, post_orders, pre_spend, and post_spend using orders in the windows 2024-03-02 to 2024-03-31 and 2024-04-01 to 2024-04-30.
Return only customers who had at least 1 order in both periods.
Flag a customer as changed if either order count changed by at least 2 or total spend changed by at least 100.
Include the customer's segment and sort by absolute spend change descending, then customer_id.

Table Definitions

`customers`

column	type	description
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Order date
order_amount	DECIMAL(10,2)	Order value
channel	VARCHAR(30)	Purchase channel

`product_launches`

column	type	description
launch_id	INT	Launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Product launch date

Sample Data

Representative rows are provided below.

Expected Output

customer_id	customer_name	segment	pre_orders	post_orders	pre_spend	post_spend	order_change	spend_change
2	Bob Smith	Growth	1	3	80.00	285.00	2	205.00
1	Alice Chen	Enterprise	2	1	250.00	40.00	-1	-210.00
7	Grace Hall	SMB	1	2	90.00	220.00	1	130.00

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Total order amount
channel	VARCHAR(30)	Purchase channel

product_launches

Column	Type	Description
launch_idPK	INT	Unique launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Launch date for the product

Sample Data

Example 1

Input

{"orders":[["101","1","2024-03-05","100.00","web"],["102","1","2024-03-20","150.00","mobile"],["103","1","2024-04-10","40.00","web"],["104","2","2024-03-25","80.00","web"],["105","2","2024-04-02","90.00","mobile"],["106","2","2024-04-15","95.00","web"],["107","2","2024-04-28","100.00","store"],["108","3","2024-03-10","60.00","web"],["109","3","2024-04-12","70.00","mobile"],["110","4","2024-03-08","200.00","store"],["111","4","2024-03-30","50.00","web"],["112","5","2024-04-05","120.00","web"],["1

Output

[["1","Alice Chen","Enterprise","5","1","565.00","40.00","-4","-525.00"],["6","Farah Khan","Growth","4","2","481.00","144.00","-2","-337.00"],["9","Ivy Turner","Growth","5","2","100.00","0.00","-3","-100.00"]]

Problem

Task

Requirements

For each customer, calculate pre_orders, post_orders, pre_spend, and post_spend using orders in the windows 2024-03-02 to 2024-03-31 and 2024-04-01 to 2024-04-30.
Return only customers who had at least 1 order in both periods.
Flag a customer as changed if either order count changed by at least 2 or total spend changed by at least 100.
Include the customer's segment and sort by absolute spend change descending, then customer_id.

Table Definitions

`customers`

column	type	description
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Order date
order_amount	DECIMAL(10,2)	Order value
channel	VARCHAR(30)	Purchase channel

`product_launches`

column	type	description
launch_id	INT	Launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Product launch date

Sample Data

Representative rows are provided below.

Expected Output

customer_id	customer_name	segment	pre_orders	post_orders	pre_spend	post_spend	order_change	spend_change
2	Bob Smith	Growth	1	3	80.00	285.00	2	205.00
1	Alice Chen	Enterprise	2	1	250.00	40.00	-1	-210.00
7	Grace Hall	SMB	1	2	90.00	220.00	1	130.00

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Total order amount
channel	VARCHAR(30)	Purchase channel

product_launches

Column	Type	Description
launch_idPK	INT	Unique launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Launch date for the product

Sample Data

Example 1

Input

{"orders":[["101","1","2024-03-05","100.00","web"],["102","1","2024-03-20","150.00","mobile"],["103","1","2024-04-10","40.00","web"],["104","2","2024-03-25","80.00","web"],["105","2","2024-04-02","90.00","mobile"],["106","2","2024-04-15","95.00","web"],["107","2","2024-04-28","100.00","store"],["108","3","2024-03-10","60.00","web"],["109","3","2024-04-12","70.00","mobile"],["110","4","2024-03-08","200.00","store"],["111","4","2024-03-30","50.00","web"],["112","5","2024-04-05","120.00","web"],["1

Output

[["1","Alice Chen","Enterprise","5","1","565.00","40.00","-4","-525.00"],["6","Farah Khan","Growth","4","2","481.00","144.00","-2","-337.00"],["9","Ivy Turner","Growth","5","2","100.00","0.00","-3","-100.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Sales Before Product LaunchMedium

Measure Retention After Product LaunchMedium

Monthly Customer Spend and RankMedium

Next question

Detect Post-Launch Customer Behavior Shifts

Medium

SQL & Data Manipulation

Asked at 2 companies2Window FunctionsCTEsDate Functions

Also asked at

Problem

Task

Requirements

For each customer, calculate pre_orders, post_orders, pre_spend, and post_spend using orders in the windows 2024-03-02 to 2024-03-31 and 2024-04-01 to 2024-04-30.
Return only customers who had at least 1 order in both periods.
Flag a customer as changed if either order count changed by at least 2 or total spend changed by at least 100.
Include the customer's segment and sort by absolute spend change descending, then customer_id.

Table Definitions

`customers`

column	type	description
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Order date
order_amount	DECIMAL(10,2)	Order value
channel	VARCHAR(30)	Purchase channel

`product_launches`

column	type	description
launch_id	INT	Launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Product launch date

Sample Data

Representative rows are provided below.

Expected Output

customer_id	customer_name	segment	pre_orders	post_orders	pre_spend	post_spend	order_change	spend_change
2	Bob Smith	Growth	1	3	80.00	285.00	2	205.00
1	Alice Chen	Enterprise	2	1	250.00	40.00	-1	-210.00
7	Grace Hall	SMB	1	2	90.00	220.00	1	130.00

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Total order amount
channel	VARCHAR(30)	Purchase channel

product_launches

Column	Type	Description
launch_idPK	INT	Unique launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Launch date for the product

Sample Data

Example 1

Input

{"orders":[["101","1","2024-03-05","100.00","web"],["102","1","2024-03-20","150.00","mobile"],["103","1","2024-04-10","40.00","web"],["104","2","2024-03-25","80.00","web"],["105","2","2024-04-02","90.00","mobile"],["106","2","2024-04-15","95.00","web"],["107","2","2024-04-28","100.00","store"],["108","3","2024-03-10","60.00","web"],["109","3","2024-04-12","70.00","mobile"],["110","4","2024-03-08","200.00","store"],["111","4","2024-03-30","50.00","web"],["112","5","2024-04-05","120.00","web"],["1

Output

[["1","Alice Chen","Enterprise","5","1","565.00","40.00","-4","-525.00"],["6","Farah Khan","Growth","4","2","481.00","144.00","-2","-337.00"],["9","Ivy Turner","Growth","5","2","100.00","0.00","-3","-100.00"]]

Problem

Task

Requirements

For each customer, calculate pre_orders, post_orders, pre_spend, and post_spend using orders in the windows 2024-03-02 to 2024-03-31 and 2024-04-01 to 2024-04-30.
Return only customers who had at least 1 order in both periods.
Flag a customer as changed if either order count changed by at least 2 or total spend changed by at least 100.
Include the customer's segment and sort by absolute spend change descending, then customer_id.

Table Definitions

`customers`

column	type	description
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Account creation date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Order date
order_amount	DECIMAL(10,2)	Order value
channel	VARCHAR(30)	Purchase channel

`product_launches`

column	type	description
launch_id	INT	Launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Product launch date

Sample Data

Representative rows are provided below.

Expected Output

customer_id	customer_name	segment	pre_orders	post_orders	pre_spend	post_spend	order_change	spend_change
2	Bob Smith	Growth	1	3	80.00	285.00	2	205.00
1	Alice Chen	Enterprise	2	1	250.00	40.00	-1	-210.00
7	Grace Hall	SMB	1	2	90.00	220.00	1	130.00

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Total order amount
channel	VARCHAR(30)	Purchase channel

product_launches

Column	Type	Description
launch_idPK	INT	Unique launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Launch date for the product

Sample Data

Example 1

Input

{"orders":[["101","1","2024-03-05","100.00","web"],["102","1","2024-03-20","150.00","mobile"],["103","1","2024-04-10","40.00","web"],["104","2","2024-03-25","80.00","web"],["105","2","2024-04-02","90.00","mobile"],["106","2","2024-04-15","95.00","web"],["107","2","2024-04-28","100.00","store"],["108","3","2024-03-10","60.00","web"],["109","3","2024-04-12","70.00","mobile"],["110","4","2024-03-08","200.00","store"],["111","4","2024-03-30","50.00","web"],["112","5","2024-04-05","120.00","web"],["1

Output

[["1","Alice Chen","Enterprise","5","1","565.00","40.00","-4","-525.00"],["6","Farah Khan","Growth","4","2","481.00","144.00","-2","-337.00"],["9","Ivy Turner","Growth","5","2","100.00","0.00","-3","-100.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Sales Before Product LaunchMedium

Measure Retention After Product LaunchMedium

Monthly Customer Spend and RankMedium

Next question