Interview Guides

Compare Daily Sales to Prior Day | Dataford Interview Questions - Dataford - Ace your Interview

Compare Daily Sales to Prior Day

Medium

SQL & Data Manipulation

Asked at 1 company1Window FunctionsRunning TotalsLag/Lead

Also asked at

Problem

Task

At Northstar Retail, analysts want to compare each store's daily revenue to its previous recorded day. Write a PostgreSQL query that uses LAG or LEAD to compare a day's revenue with the prior row for the same store.

Requirements

Aggregate order revenue by store_id and order_date.
For each store, compare each day's revenue to the previous day present in the data using a window function.
Return store_id, store_name, order_date, daily_revenue, previous_day_revenue, revenue_change, and a change_type column with values Increase, Decrease, or No Prior Day.
Exclude cancelled orders from the revenue calculation.
Order the final output by store_id and order_date.

Table Definitions

`stores`

column	type	description
store_id	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Store region

`orders`

column	type	description
order_id	INT	Unique order identifier
store_id	INT	Store that received the order
order_date	DATE	Order date
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample Data

`stores`

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix	NULL

`orders`

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04	NULL	completed
112	3	2024-04-02	-20.00	completed

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	NULL	NULL	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	NULL	NULL	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	NULL	100.00	NULL	Decrease
3	Seattle	2024-04-01	300.00	NULL	NULL	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Geographic region for the store

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
store_id	INT	Store associated with the order
order_date	DATE	Date the order was placed
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

orders

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04		completed
112	3	2024-04-02	-20.00	completed
102	1	2024-04-04	150.00	completed
105	1	2024-04-02	150.00	completed
107	2	2024-04-01	25.00	cancelled
114	99	2024-04-02	500.00	completed
1	49	2024-03-30	112	completed
2	18	2024-04-01	164	completed
3	24	2024-04-02	7	cancelled
4	18	2024-03-31	153	cancelled
5	74	2024-04-01	-102	completed
6	70	2024-03-31	null	completed
7	48	2024-04-05	47	completed
8	70	2024-04-06	null	completed
9	69	2024-04-04	264	completed
10	42	2024-04-02	459	completed
11	55	2024-04-07	-64	completed
12	19	2024-03-29	128	completed
13	90	2024-03-29	36	completed
14	88	2024-03-29	92	completed

stores

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix
1	Seattle	South
2	Phoenix	null
3	Phoenix	South
4	Austin	East
5	Seattle	null
6	Phoenix	East
7	Seattle	null
8	Phoenix	East
9	Austin	West
10	Austin	South
11	Austin	West
12	Phoenix	null
13	Phoenix	East
14	Manhattan	East
15	Phoenix	null
16	Seattle	East
17	Phoenix	West
18	Seattle	East
19	Austin	West
20	Manhattan	West

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	null	null	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	null	null	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	null	100.00	null	Decrease
3	Seattle	2024-04-01	300.00	null	null	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase
18	Seattle	2024-04-01	164.00	null	null	No Prior Day
19	Austin	2024-03-29	128.00	null	null	No Prior Day

Problem

Task

Requirements

Aggregate order revenue by store_id and order_date.
For each store, compare each day's revenue to the previous day present in the data using a window function.
Return store_id, store_name, order_date, daily_revenue, previous_day_revenue, revenue_change, and a change_type column with values Increase, Decrease, or No Prior Day.
Exclude cancelled orders from the revenue calculation.
Order the final output by store_id and order_date.

Table Definitions

`stores`

column	type	description
store_id	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Store region

`orders`

column	type	description
order_id	INT	Unique order identifier
store_id	INT	Store that received the order
order_date	DATE	Order date
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample Data

`stores`

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix	NULL

`orders`

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04	NULL	completed
112	3	2024-04-02	-20.00	completed

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	NULL	NULL	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	NULL	NULL	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	NULL	100.00	NULL	Decrease
3	Seattle	2024-04-01	300.00	NULL	NULL	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Geographic region for the store

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
store_id	INT	Store associated with the order
order_date	DATE	Date the order was placed
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

orders

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04		completed
112	3	2024-04-02	-20.00	completed
102	1	2024-04-04	150.00	completed
105	1	2024-04-02	150.00	completed
107	2	2024-04-01	25.00	cancelled
114	99	2024-04-02	500.00	completed
1	49	2024-03-30	112	completed
2	18	2024-04-01	164	completed
3	24	2024-04-02	7	cancelled
4	18	2024-03-31	153	cancelled
5	74	2024-04-01	-102	completed
6	70	2024-03-31	null	completed
7	48	2024-04-05	47	completed
8	70	2024-04-06	null	completed
9	69	2024-04-04	264	completed
10	42	2024-04-02	459	completed
11	55	2024-04-07	-64	completed
12	19	2024-03-29	128	completed
13	90	2024-03-29	36	completed
14	88	2024-03-29	92	completed

stores

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix
1	Seattle	South
2	Phoenix	null
3	Phoenix	South
4	Austin	East
5	Seattle	null
6	Phoenix	East
7	Seattle	null
8	Phoenix	East
9	Austin	West
10	Austin	South
11	Austin	West
12	Phoenix	null
13	Phoenix	East
14	Manhattan	East
15	Phoenix	null
16	Seattle	East
17	Phoenix	West
18	Seattle	East
19	Austin	West
20	Manhattan	West

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	null	null	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	null	null	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	null	100.00	null	Decrease
3	Seattle	2024-04-01	300.00	null	null	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase
18	Seattle	2024-04-01	164.00	null	null	No Prior Day
19	Austin	2024-03-29	128.00	null	null	No Prior Day

Practice SQL

PostgreSQL

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

Up next

Compare Periods with LAG and LEADMedium

Compare Sales Before Product LaunchMedium Monthly Revenue Trends by CategoryMedium

Next question

Compare Daily Sales to Prior Day

Medium

SQL & Data Manipulation

Asked at 1 company1Window FunctionsRunning TotalsLag/Lead

Also asked at

Problem

Task

Requirements

Aggregate order revenue by store_id and order_date.
For each store, compare each day's revenue to the previous day present in the data using a window function.
Return store_id, store_name, order_date, daily_revenue, previous_day_revenue, revenue_change, and a change_type column with values Increase, Decrease, or No Prior Day.
Exclude cancelled orders from the revenue calculation.
Order the final output by store_id and order_date.

Table Definitions

`stores`

column	type	description
store_id	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Store region

`orders`

column	type	description
order_id	INT	Unique order identifier
store_id	INT	Store that received the order
order_date	DATE	Order date
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample Data

`stores`

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix	NULL

`orders`

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04	NULL	completed
112	3	2024-04-02	-20.00	completed

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	NULL	NULL	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	NULL	NULL	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	NULL	100.00	NULL	Decrease
3	Seattle	2024-04-01	300.00	NULL	NULL	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Geographic region for the store

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
store_id	INT	Store associated with the order
order_date	DATE	Date the order was placed
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

orders

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04		completed
112	3	2024-04-02	-20.00	completed
102	1	2024-04-04	150.00	completed
105	1	2024-04-02	150.00	completed
107	2	2024-04-01	25.00	cancelled
114	99	2024-04-02	500.00	completed
1	49	2024-03-30	112	completed
2	18	2024-04-01	164	completed
3	24	2024-04-02	7	cancelled
4	18	2024-03-31	153	cancelled
5	74	2024-04-01	-102	completed
6	70	2024-03-31	null	completed
7	48	2024-04-05	47	completed
8	70	2024-04-06	null	completed
9	69	2024-04-04	264	completed
10	42	2024-04-02	459	completed
11	55	2024-04-07	-64	completed
12	19	2024-03-29	128	completed
13	90	2024-03-29	36	completed
14	88	2024-03-29	92	completed

stores

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix
1	Seattle	South
2	Phoenix	null
3	Phoenix	South
4	Austin	East
5	Seattle	null
6	Phoenix	East
7	Seattle	null
8	Phoenix	East
9	Austin	West
10	Austin	South
11	Austin	West
12	Phoenix	null
13	Phoenix	East
14	Manhattan	East
15	Phoenix	null
16	Seattle	East
17	Phoenix	West
18	Seattle	East
19	Austin	West
20	Manhattan	West

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	null	null	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	null	null	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	null	100.00	null	Decrease
3	Seattle	2024-04-01	300.00	null	null	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase
18	Seattle	2024-04-01	164.00	null	null	No Prior Day
19	Austin	2024-03-29	128.00	null	null	No Prior Day

Problem

Task

Requirements

Aggregate order revenue by store_id and order_date.
For each store, compare each day's revenue to the previous day present in the data using a window function.
Return store_id, store_name, order_date, daily_revenue, previous_day_revenue, revenue_change, and a change_type column with values Increase, Decrease, or No Prior Day.
Exclude cancelled orders from the revenue calculation.
Order the final output by store_id and order_date.

Table Definitions

`stores`

column	type	description
store_id	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Store region

`orders`

column	type	description
order_id	INT	Unique order identifier
store_id	INT	Store that received the order
order_date	DATE	Order date
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample Data

`stores`

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix	NULL

`orders`

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04	NULL	completed
112	3	2024-04-02	-20.00	completed

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	NULL	NULL	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	NULL	NULL	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	NULL	100.00	NULL	Decrease
3	Seattle	2024-04-01	300.00	NULL	NULL	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Geographic region for the store

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
store_id	INT	Store associated with the order
order_date	DATE	Date the order was placed
amount	NUMERIC(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

orders

order_id	store_id	order_date	amount	status
108	2	2024-04-03	60.00	completed
101	1	2024-04-01	120.00	completed
111	3	2024-04-01	300.00	completed
104	1	2024-04-02	50.00	completed
106	2	2024-04-01	80.00	completed
113	3	2024-04-03	0.00	completed
109	2	2024-04-03	40.00	completed
103	1	2024-04-02	200.00	cancelled
110	2	2024-04-04		completed
112	3	2024-04-02	-20.00	completed
102	1	2024-04-04	150.00	completed
105	1	2024-04-02	150.00	completed
107	2	2024-04-01	25.00	cancelled
114	99	2024-04-02	500.00	completed
1	49	2024-03-30	112	completed
2	18	2024-04-01	164	completed
3	24	2024-04-02	7	cancelled
4	18	2024-03-31	153	cancelled
5	74	2024-04-01	-102	completed
6	70	2024-03-31	null	completed
7	48	2024-04-05	47	completed
8	70	2024-04-06	null	completed
9	69	2024-04-04	264	completed
10	42	2024-04-02	459	completed
11	55	2024-04-07	-64	completed
12	19	2024-03-29	128	completed
13	90	2024-03-29	36	completed
14	88	2024-03-29	92	completed

stores

store_id	store_name	region
2	Brooklyn	East
1	Manhattan	East
4	Austin	South
3	Seattle	West
5	Phoenix
1	Seattle	South
2	Phoenix	null
3	Phoenix	South
4	Austin	East
5	Seattle	null
6	Phoenix	East
7	Seattle	null
8	Phoenix	East
9	Austin	West
10	Austin	South
11	Austin	West
12	Phoenix	null
13	Phoenix	East
14	Manhattan	East
15	Phoenix	null
16	Seattle	East
17	Phoenix	West
18	Seattle	East
19	Austin	West
20	Manhattan	West

Expected Output

store_id	store_name	order_date	daily_revenue	previous_day_revenue	revenue_change	change_type
1	Manhattan	2024-04-01	120.00	null	null	No Prior Day
1	Manhattan	2024-04-02	200.00	120.00	80.00	Increase
1	Manhattan	2024-04-04	150.00	200.00	-50.00	Decrease
2	Brooklyn	2024-04-01	80.00	null	null	No Prior Day
2	Brooklyn	2024-04-03	100.00	80.00	20.00	Increase
2	Brooklyn	2024-04-04	null	100.00	null	Decrease
3	Seattle	2024-04-01	300.00	null	null	No Prior Day
3	Seattle	2024-04-02	-20.00	300.00	-320.00	Decrease
3	Seattle	2024-04-03	0.00	-20.00	20.00	Increase
18	Seattle	2024-04-01	164.00	null	null	No Prior Day
19	Austin	2024-03-29	128.00	null	null	No Prior Day

Practice SQL

PostgreSQL

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

Up next

Compare Periods with LAG and LEADMedium

Compare Sales Before Product LaunchMedium Monthly Revenue Trends by CategoryMedium

Next question