Count active subscriptions when rows are soft-deleted

Nothing in this billing database is ever physically deleted. A row is removed by setting its deleted_at timestamp; deleted_at is NULL while the row is live. That applies to both tables:

accounts (account_id, plan, deleted_at)
subscriptions (subscription_id, account_id, status, deleted_at)

Count the active subscriptions per plan. A subscription counts when its status is 'active', it isn't deleted, and its account isn't deleted. Every plan that appears in accounts must be listed, with 0 when it has no active subscriptions.

Columns: plan, active_subs. Sort by plan.

Tables & sample data

accounts

6 rows total · first 5 shown

account_idvarchar	planvarchar	deleted_attimestamp
A-101	pro	NULL
A-102	pro	2024-02-01 10:00:00
A-103	team	NULL
A-104	free	NULL
A-105	enterprise	2024-01-15 00:00:00

subscriptions

9 rows total · first 5 shown

subscription_idvarchar	account_idvarchar	statusvarchar	deleted_attimestamp
S-1	A-101	active	NULL
S-2	A-101	cancelled	NULL
S-3	A-102	active	NULL
S-4	A-103	active	2024-02-20 09:00:00
S-5	A-103	active	NULL

query.sql

Loading editor…