Interview Guides

Active Subscription Revenue by Customer | Dataford Interview Questions - Dataford - Ace your Interview

Active Subscription Revenue by Customer

Hard

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsData Wrangling

Also asked at

Problem

Task

PetBox wants a report of currently active subscriptions and the revenue tied to each subscribed product. Write a SQL query to return one row per active subscription.

Requirements

Return the customer name, product name, next shipment date, and product price as revenue
Include only subscriptions where status = 'active'
Join subscriptions to customers and products using their keys
Order the final result by customer name, then product name

Tables

`customers`

column	type	description
customer_id	INT	Unique customer identifier
name	VARCHAR(255)	Customer name
email	VARCHAR(255)	Customer email
created_at	TIMESTAMP	Account creation timestamp

`products`

column	type	description
product_id	INT	Unique product identifier
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

`subscriptions`

column	type	description
subscription_id	INT	Unique subscription identifier
customer_id	INT	Customer reference
product_id	INT	Product reference
start_date	DATE	Subscription start date
next_shipment_date	DATE	Next scheduled shipment
status	VARCHAR(255)	Subscription status

Sample Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique identifier for each customer
name	VARCHAR(255)	Customer's full name
email	VARCHAR(255)	Customer email address
created_at	TIMESTAMP	Timestamp when the customer account was created

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique identifier for each subscription
customer_id	INT	Reference to the customer
product_id	INT	Reference to the product
start_date	DATE	Date the subscription started
next_shipment_date	DATE	Date of the next scheduled shipment
status	VARCHAR(255)	Current subscription status

products

product_id	name	category	price
8	Catnip	Toys	8.75
3	Dog Toys	Toys	15.49
11	Dog Bed	Supplies	45.00
1	Dog Food	Food	29.99
6	Fish Flakes	Food	7.25
12	Cat Bowl	Supplies	6.50
4	Cat Treats	Food	9.99
9	Hamster Wheel	Toys	11.00
2	Cat Litter	Supplies	19.99
10	Rabbit Hay	Food	13.25
5	Bird Seed	Food	12.50
7	Dog Leash	Supplies	14.00
1	John	Reptile	3.05
2	Henry	Outdoor	27.87
3	Frank	Reptile	35.92
4	Paula	Toys	26.45
5	Mona	Furniture	16.46
6	Tina	Supplies	3.88
7	Henry	Travel	5.38
8	Bob	Dental	8.25
9	Dog Toys	Accessories	9.36
10	Xander	Feeding	12.76
11	Alice	Safety	5.96
12	Eve	Safety	19.63
13	Grace	Habitat	41.33
14	Xander	Food	24.54
15	Rita	Cleaning	38.95
16	Grace	Cleaning	50.87
17	Henry	Grooming	40.61

customers

customer_id	name	email	created_at
8	Henry	henry@example.com	2023-06-01 13:10:00
3	Charlie	charlie@example.com	2023-03-01 12:00:00
11	Kelly	kelly@example.com	2023-06-20 21:00:00
1	Alice	alice@example.com	2023-01-01 10:00:00
6	Frank	frank@example.com	2023-05-01 08:45:00
12	Liam	liam@example.com	2023-06-25 22:00:00
4	Diana	diana@example.com	2023-04-01 13:00:00
9	Ingrid	ingrid@example.com	2023-06-10 18:15:00
2	Bob	bob@example.com	2023-02-01 11:00:00
10	John	john@example.com	2023-06-15 19:00:00
5	Eve	eve@example.com	2023-04-15 09:30:00
7	Grace	grace@example.com	2023-05-18 17:20:00
1	Grace	paula@example.com	2023-01-15 22:29:45
2	Liam	tina@example.com	2023-05-30 13:33:44
3	Nina	wendy@example.com	2023-02-12 09:38:50
4	Rita	bob@example.com	2023-05-12 17:21:37
5	Mona	kelly@example.com	2023-05-10 19:30:23
6	Kelly	henry@example.com	2023-05-25 21:04:41
7	Liam	zane@example.com	2023-06-22 00:20:02
8	Henry	kelly@example.com	2023-05-19 04:39:03
9	Xander	john@example.com	2023-01-24 15:14:01
10	Eve	mona@example.com	2023-06-12 23:24:48
11	Mona	eve@example.com	2023-04-02 01:15:01
12	Dog Food	bob@example.com	2023-01-08 09:27:28
13	Yara	victor@example.com	2023-03-22 13:15:11
14	Eve	rita@example.com	2023-06-15 23:31:54
15	Eve	rita@example.com	2023-06-01 08:05:05
16	Nina	nina@example.com	2023-04-14 11:56:51

subscriptions

subscription_id	customer_id	product_id	start_date	next_shipment_date	status
8	8	8	2023-06-01	2024-01-25	active
3	3	3	2023-03-01	2024-01-25	inactive
11	11	11	2023-06-20	2024-01-31	active
1	1	1	2023-01-01	2024-01-15	active
6	6	6	2023-05-01	2024-01-22	inactive
12	12	12	2023-06-25	2024-01-28	active
4	4	4	2023-04-01	2024-01-30	active
9	9	9	2023-06-10	2024-01-29	inactive
2	2	2	2023-02-01	2024-01-20	active
10	10	10	2023-06-15	2024-01-30	active
5	5	5	2023-04-15	2024-01-18	active
7	7	7	2023-05-18	2024-01-28	active
1	18	16	2023-01-26	2024-01-24	paused
2	16	52	2023-01-11	2024-01-19	rejected
3	94	57	2023-04-29	2024-01-25	active
4	18	38	2023-02-05	2024-01-13	archived
5	6	15	2023-02-21	2024-01-15	active
6	81	47	2023-03-19	2024-01-30	processing
7	46	79	2023-01-31	2024-01-17	suspended
8	88	12	2023-05-24	2024-01-20	rejected
9	7	20	2023-04-21	2024-01-28	paused
10	39	16	2023-03-13	2024-01-16	pending
11	87	3	2023-04-13	2024-01-25	pending
12	95	52	2023-04-19	2024-01-16	pending
13	75	22	2023-04-21	2024-02-01	queued
14	40	74	2023-05-25	2024-01-17	draft
15	8	5	2023-05-18	2024-01-27	rejected

Expected Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99
Eve	Bird Seed	2024-01-18	12.50
Grace	Dog Leash	2024-01-28	14.00
Henry	Catnip	2024-01-25	8.75
John	Rabbit Hay	2024-01-30	13.25
Kelly	Dog Bed	2024-01-31	45.00
Liam	Cat Bowl	2024-01-28	6.50

Problem

Task

PetBox wants a report of currently active subscriptions and the revenue tied to each subscribed product. Write a SQL query to return one row per active subscription.

Requirements

Return the customer name, product name, next shipment date, and product price as revenue
Include only subscriptions where status = 'active'
Join subscriptions to customers and products using their keys
Order the final result by customer name, then product name

Tables

`customers`

column	type	description
customer_id	INT	Unique customer identifier
name	VARCHAR(255)	Customer name
email	VARCHAR(255)	Customer email
created_at	TIMESTAMP	Account creation timestamp

`products`

column	type	description
product_id	INT	Unique product identifier
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

`subscriptions`

column	type	description
subscription_id	INT	Unique subscription identifier
customer_id	INT	Customer reference
product_id	INT	Product reference
start_date	DATE	Subscription start date
next_shipment_date	DATE	Next scheduled shipment
status	VARCHAR(255)	Subscription status

Sample Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique identifier for each customer
name	VARCHAR(255)	Customer's full name
email	VARCHAR(255)	Customer email address
created_at	TIMESTAMP	Timestamp when the customer account was created

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique identifier for each subscription
customer_id	INT	Reference to the customer
product_id	INT	Reference to the product
start_date	DATE	Date the subscription started
next_shipment_date	DATE	Date of the next scheduled shipment
status	VARCHAR(255)	Current subscription status

products

product_id	name	category	price
8	Catnip	Toys	8.75
3	Dog Toys	Toys	15.49
11	Dog Bed	Supplies	45.00
1	Dog Food	Food	29.99
6	Fish Flakes	Food	7.25
12	Cat Bowl	Supplies	6.50
4	Cat Treats	Food	9.99
9	Hamster Wheel	Toys	11.00
2	Cat Litter	Supplies	19.99
10	Rabbit Hay	Food	13.25
5	Bird Seed	Food	12.50
7	Dog Leash	Supplies	14.00
1	John	Reptile	3.05
2	Henry	Outdoor	27.87
3	Frank	Reptile	35.92
4	Paula	Toys	26.45
5	Mona	Furniture	16.46
6	Tina	Supplies	3.88
7	Henry	Travel	5.38
8	Bob	Dental	8.25
9	Dog Toys	Accessories	9.36
10	Xander	Feeding	12.76
11	Alice	Safety	5.96
12	Eve	Safety	19.63
13	Grace	Habitat	41.33
14	Xander	Food	24.54
15	Rita	Cleaning	38.95
16	Grace	Cleaning	50.87
17	Henry	Grooming	40.61

customers

customer_id	name	email	created_at
8	Henry	henry@example.com	2023-06-01 13:10:00
3	Charlie	charlie@example.com	2023-03-01 12:00:00
11	Kelly	kelly@example.com	2023-06-20 21:00:00
1	Alice	alice@example.com	2023-01-01 10:00:00
6	Frank	frank@example.com	2023-05-01 08:45:00
12	Liam	liam@example.com	2023-06-25 22:00:00
4	Diana	diana@example.com	2023-04-01 13:00:00
9	Ingrid	ingrid@example.com	2023-06-10 18:15:00
2	Bob	bob@example.com	2023-02-01 11:00:00
10	John	john@example.com	2023-06-15 19:00:00
5	Eve	eve@example.com	2023-04-15 09:30:00
7	Grace	grace@example.com	2023-05-18 17:20:00
1	Grace	paula@example.com	2023-01-15 22:29:45
2	Liam	tina@example.com	2023-05-30 13:33:44
3	Nina	wendy@example.com	2023-02-12 09:38:50
4	Rita	bob@example.com	2023-05-12 17:21:37
5	Mona	kelly@example.com	2023-05-10 19:30:23
6	Kelly	henry@example.com	2023-05-25 21:04:41
7	Liam	zane@example.com	2023-06-22 00:20:02
8	Henry	kelly@example.com	2023-05-19 04:39:03
9	Xander	john@example.com	2023-01-24 15:14:01
10	Eve	mona@example.com	2023-06-12 23:24:48
11	Mona	eve@example.com	2023-04-02 01:15:01
12	Dog Food	bob@example.com	2023-01-08 09:27:28
13	Yara	victor@example.com	2023-03-22 13:15:11
14	Eve	rita@example.com	2023-06-15 23:31:54
15	Eve	rita@example.com	2023-06-01 08:05:05
16	Nina	nina@example.com	2023-04-14 11:56:51

subscriptions

subscription_id	customer_id	product_id	start_date	next_shipment_date	status
8	8	8	2023-06-01	2024-01-25	active
3	3	3	2023-03-01	2024-01-25	inactive
11	11	11	2023-06-20	2024-01-31	active
1	1	1	2023-01-01	2024-01-15	active
6	6	6	2023-05-01	2024-01-22	inactive
12	12	12	2023-06-25	2024-01-28	active
4	4	4	2023-04-01	2024-01-30	active
9	9	9	2023-06-10	2024-01-29	inactive
2	2	2	2023-02-01	2024-01-20	active
10	10	10	2023-06-15	2024-01-30	active
5	5	5	2023-04-15	2024-01-18	active
7	7	7	2023-05-18	2024-01-28	active
1	18	16	2023-01-26	2024-01-24	paused
2	16	52	2023-01-11	2024-01-19	rejected
3	94	57	2023-04-29	2024-01-25	active
4	18	38	2023-02-05	2024-01-13	archived
5	6	15	2023-02-21	2024-01-15	active
6	81	47	2023-03-19	2024-01-30	processing
7	46	79	2023-01-31	2024-01-17	suspended
8	88	12	2023-05-24	2024-01-20	rejected
9	7	20	2023-04-21	2024-01-28	paused
10	39	16	2023-03-13	2024-01-16	pending
11	87	3	2023-04-13	2024-01-25	pending
12	95	52	2023-04-19	2024-01-16	pending
13	75	22	2023-04-21	2024-02-01	queued
14	40	74	2023-05-25	2024-01-17	draft
15	8	5	2023-05-18	2024-01-27	rejected

Expected Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99
Eve	Bird Seed	2024-01-18	12.50
Grace	Dog Leash	2024-01-28	14.00
Henry	Catnip	2024-01-25	8.75
John	Rabbit Hay	2024-01-30	13.25
Kelly	Dog Bed	2024-01-31	45.00
Liam	Cat Bowl	2024-01-28	6.50

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Aggregation by Product CategoryMedium

Analyze Monthly Revenue Trends by Product CategoryMedium

Active Subscription Revenue by Customer

Hard

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsData Wrangling

Also asked at

Problem

Task

PetBox wants a report of currently active subscriptions and the revenue tied to each subscribed product. Write a SQL query to return one row per active subscription.

Requirements

Return the customer name, product name, next shipment date, and product price as revenue
Include only subscriptions where status = 'active'
Join subscriptions to customers and products using their keys
Order the final result by customer name, then product name

Tables

`customers`

column	type	description
customer_id	INT	Unique customer identifier
name	VARCHAR(255)	Customer name
email	VARCHAR(255)	Customer email
created_at	TIMESTAMP	Account creation timestamp

`products`

column	type	description
product_id	INT	Unique product identifier
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

`subscriptions`

column	type	description
subscription_id	INT	Unique subscription identifier
customer_id	INT	Customer reference
product_id	INT	Product reference
start_date	DATE	Subscription start date
next_shipment_date	DATE	Next scheduled shipment
status	VARCHAR(255)	Subscription status

Sample Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique identifier for each customer
name	VARCHAR(255)	Customer's full name
email	VARCHAR(255)	Customer email address
created_at	TIMESTAMP	Timestamp when the customer account was created

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique identifier for each subscription
customer_id	INT	Reference to the customer
product_id	INT	Reference to the product
start_date	DATE	Date the subscription started
next_shipment_date	DATE	Date of the next scheduled shipment
status	VARCHAR(255)	Current subscription status

products

product_id	name	category	price
8	Catnip	Toys	8.75
3	Dog Toys	Toys	15.49
11	Dog Bed	Supplies	45.00
1	Dog Food	Food	29.99
6	Fish Flakes	Food	7.25
12	Cat Bowl	Supplies	6.50
4	Cat Treats	Food	9.99
9	Hamster Wheel	Toys	11.00
2	Cat Litter	Supplies	19.99
10	Rabbit Hay	Food	13.25
5	Bird Seed	Food	12.50
7	Dog Leash	Supplies	14.00
1	John	Reptile	3.05
2	Henry	Outdoor	27.87
3	Frank	Reptile	35.92
4	Paula	Toys	26.45
5	Mona	Furniture	16.46
6	Tina	Supplies	3.88
7	Henry	Travel	5.38
8	Bob	Dental	8.25
9	Dog Toys	Accessories	9.36
10	Xander	Feeding	12.76
11	Alice	Safety	5.96
12	Eve	Safety	19.63
13	Grace	Habitat	41.33
14	Xander	Food	24.54
15	Rita	Cleaning	38.95
16	Grace	Cleaning	50.87
17	Henry	Grooming	40.61

customers

customer_id	name	email	created_at
8	Henry	henry@example.com	2023-06-01 13:10:00
3	Charlie	charlie@example.com	2023-03-01 12:00:00
11	Kelly	kelly@example.com	2023-06-20 21:00:00
1	Alice	alice@example.com	2023-01-01 10:00:00
6	Frank	frank@example.com	2023-05-01 08:45:00
12	Liam	liam@example.com	2023-06-25 22:00:00
4	Diana	diana@example.com	2023-04-01 13:00:00
9	Ingrid	ingrid@example.com	2023-06-10 18:15:00
2	Bob	bob@example.com	2023-02-01 11:00:00
10	John	john@example.com	2023-06-15 19:00:00
5	Eve	eve@example.com	2023-04-15 09:30:00
7	Grace	grace@example.com	2023-05-18 17:20:00
1	Grace	paula@example.com	2023-01-15 22:29:45
2	Liam	tina@example.com	2023-05-30 13:33:44
3	Nina	wendy@example.com	2023-02-12 09:38:50
4	Rita	bob@example.com	2023-05-12 17:21:37
5	Mona	kelly@example.com	2023-05-10 19:30:23
6	Kelly	henry@example.com	2023-05-25 21:04:41
7	Liam	zane@example.com	2023-06-22 00:20:02
8	Henry	kelly@example.com	2023-05-19 04:39:03
9	Xander	john@example.com	2023-01-24 15:14:01
10	Eve	mona@example.com	2023-06-12 23:24:48
11	Mona	eve@example.com	2023-04-02 01:15:01
12	Dog Food	bob@example.com	2023-01-08 09:27:28
13	Yara	victor@example.com	2023-03-22 13:15:11
14	Eve	rita@example.com	2023-06-15 23:31:54
15	Eve	rita@example.com	2023-06-01 08:05:05
16	Nina	nina@example.com	2023-04-14 11:56:51

subscriptions

subscription_id	customer_id	product_id	start_date	next_shipment_date	status
8	8	8	2023-06-01	2024-01-25	active
3	3	3	2023-03-01	2024-01-25	inactive
11	11	11	2023-06-20	2024-01-31	active
1	1	1	2023-01-01	2024-01-15	active
6	6	6	2023-05-01	2024-01-22	inactive
12	12	12	2023-06-25	2024-01-28	active
4	4	4	2023-04-01	2024-01-30	active
9	9	9	2023-06-10	2024-01-29	inactive
2	2	2	2023-02-01	2024-01-20	active
10	10	10	2023-06-15	2024-01-30	active
5	5	5	2023-04-15	2024-01-18	active
7	7	7	2023-05-18	2024-01-28	active
1	18	16	2023-01-26	2024-01-24	paused
2	16	52	2023-01-11	2024-01-19	rejected
3	94	57	2023-04-29	2024-01-25	active
4	18	38	2023-02-05	2024-01-13	archived
5	6	15	2023-02-21	2024-01-15	active
6	81	47	2023-03-19	2024-01-30	processing
7	46	79	2023-01-31	2024-01-17	suspended
8	88	12	2023-05-24	2024-01-20	rejected
9	7	20	2023-04-21	2024-01-28	paused
10	39	16	2023-03-13	2024-01-16	pending
11	87	3	2023-04-13	2024-01-25	pending
12	95	52	2023-04-19	2024-01-16	pending
13	75	22	2023-04-21	2024-02-01	queued
14	40	74	2023-05-25	2024-01-17	draft
15	8	5	2023-05-18	2024-01-27	rejected

Expected Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99
Eve	Bird Seed	2024-01-18	12.50
Grace	Dog Leash	2024-01-28	14.00
Henry	Catnip	2024-01-25	8.75
John	Rabbit Hay	2024-01-30	13.25
Kelly	Dog Bed	2024-01-31	45.00
Liam	Cat Bowl	2024-01-28	6.50

Problem

Task

PetBox wants a report of currently active subscriptions and the revenue tied to each subscribed product. Write a SQL query to return one row per active subscription.

Requirements

Return the customer name, product name, next shipment date, and product price as revenue
Include only subscriptions where status = 'active'
Join subscriptions to customers and products using their keys
Order the final result by customer name, then product name

Tables

`customers`

column	type	description
customer_id	INT	Unique customer identifier
name	VARCHAR(255)	Customer name
email	VARCHAR(255)	Customer email
created_at	TIMESTAMP	Account creation timestamp

`products`

column	type	description
product_id	INT	Unique product identifier
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

`subscriptions`

column	type	description
subscription_id	INT	Unique subscription identifier
customer_id	INT	Customer reference
product_id	INT	Product reference
start_date	DATE	Subscription start date
next_shipment_date	DATE	Next scheduled shipment
status	VARCHAR(255)	Subscription status

Sample Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique identifier for each customer
name	VARCHAR(255)	Customer's full name
email	VARCHAR(255)	Customer email address
created_at	TIMESTAMP	Timestamp when the customer account was created

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique identifier for each subscription
customer_id	INT	Reference to the customer
product_id	INT	Reference to the product
start_date	DATE	Date the subscription started
next_shipment_date	DATE	Date of the next scheduled shipment
status	VARCHAR(255)	Current subscription status

products

product_id	name	category	price
8	Catnip	Toys	8.75
3	Dog Toys	Toys	15.49
11	Dog Bed	Supplies	45.00
1	Dog Food	Food	29.99
6	Fish Flakes	Food	7.25
12	Cat Bowl	Supplies	6.50
4	Cat Treats	Food	9.99
9	Hamster Wheel	Toys	11.00
2	Cat Litter	Supplies	19.99
10	Rabbit Hay	Food	13.25
5	Bird Seed	Food	12.50
7	Dog Leash	Supplies	14.00
1	John	Reptile	3.05
2	Henry	Outdoor	27.87
3	Frank	Reptile	35.92
4	Paula	Toys	26.45
5	Mona	Furniture	16.46
6	Tina	Supplies	3.88
7	Henry	Travel	5.38
8	Bob	Dental	8.25
9	Dog Toys	Accessories	9.36
10	Xander	Feeding	12.76
11	Alice	Safety	5.96
12	Eve	Safety	19.63
13	Grace	Habitat	41.33
14	Xander	Food	24.54
15	Rita	Cleaning	38.95
16	Grace	Cleaning	50.87
17	Henry	Grooming	40.61

customers

customer_id	name	email	created_at
8	Henry	henry@example.com	2023-06-01 13:10:00
3	Charlie	charlie@example.com	2023-03-01 12:00:00
11	Kelly	kelly@example.com	2023-06-20 21:00:00
1	Alice	alice@example.com	2023-01-01 10:00:00
6	Frank	frank@example.com	2023-05-01 08:45:00
12	Liam	liam@example.com	2023-06-25 22:00:00
4	Diana	diana@example.com	2023-04-01 13:00:00
9	Ingrid	ingrid@example.com	2023-06-10 18:15:00
2	Bob	bob@example.com	2023-02-01 11:00:00
10	John	john@example.com	2023-06-15 19:00:00
5	Eve	eve@example.com	2023-04-15 09:30:00
7	Grace	grace@example.com	2023-05-18 17:20:00
1	Grace	paula@example.com	2023-01-15 22:29:45
2	Liam	tina@example.com	2023-05-30 13:33:44
3	Nina	wendy@example.com	2023-02-12 09:38:50
4	Rita	bob@example.com	2023-05-12 17:21:37
5	Mona	kelly@example.com	2023-05-10 19:30:23
6	Kelly	henry@example.com	2023-05-25 21:04:41
7	Liam	zane@example.com	2023-06-22 00:20:02
8	Henry	kelly@example.com	2023-05-19 04:39:03
9	Xander	john@example.com	2023-01-24 15:14:01
10	Eve	mona@example.com	2023-06-12 23:24:48
11	Mona	eve@example.com	2023-04-02 01:15:01
12	Dog Food	bob@example.com	2023-01-08 09:27:28
13	Yara	victor@example.com	2023-03-22 13:15:11
14	Eve	rita@example.com	2023-06-15 23:31:54
15	Eve	rita@example.com	2023-06-01 08:05:05
16	Nina	nina@example.com	2023-04-14 11:56:51

subscriptions

subscription_id	customer_id	product_id	start_date	next_shipment_date	status
8	8	8	2023-06-01	2024-01-25	active
3	3	3	2023-03-01	2024-01-25	inactive
11	11	11	2023-06-20	2024-01-31	active
1	1	1	2023-01-01	2024-01-15	active
6	6	6	2023-05-01	2024-01-22	inactive
12	12	12	2023-06-25	2024-01-28	active
4	4	4	2023-04-01	2024-01-30	active
9	9	9	2023-06-10	2024-01-29	inactive
2	2	2	2023-02-01	2024-01-20	active
10	10	10	2023-06-15	2024-01-30	active
5	5	5	2023-04-15	2024-01-18	active
7	7	7	2023-05-18	2024-01-28	active
1	18	16	2023-01-26	2024-01-24	paused
2	16	52	2023-01-11	2024-01-19	rejected
3	94	57	2023-04-29	2024-01-25	active
4	18	38	2023-02-05	2024-01-13	archived
5	6	15	2023-02-21	2024-01-15	active
6	81	47	2023-03-19	2024-01-30	processing
7	46	79	2023-01-31	2024-01-17	suspended
8	88	12	2023-05-24	2024-01-20	rejected
9	7	20	2023-04-21	2024-01-28	paused
10	39	16	2023-03-13	2024-01-16	pending
11	87	3	2023-04-13	2024-01-25	pending
12	95	52	2023-04-19	2024-01-16	pending
13	75	22	2023-04-21	2024-02-01	queued
14	40	74	2023-05-25	2024-01-17	draft
15	8	5	2023-05-18	2024-01-27	rejected

Expected Output

customer_name	product_name	next_shipment_date	revenue
Alice	Dog Food	2024-01-15	29.99
Bob	Cat Litter	2024-01-20	19.99
Diana	Cat Treats	2024-01-30	9.99
Eve	Bird Seed	2024-01-18	12.50
Grace	Dog Leash	2024-01-28	14.00
Henry	Catnip	2024-01-25	8.75
John	Rabbit Hay	2024-01-30	13.25
Kelly	Dog Bed	2024-01-31	45.00
Liam	Cat Bowl	2024-01-28	6.50

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Aggregation by Product CategoryMedium

Analyze Monthly Revenue Trends by Product CategoryMedium