To count active subscriptions at a reporting date, include records whose start is on or before that date and whose exclusive end is after it, or is explicitly open-ended. Then decide whether the metric counts subscription records or distinct customers. A customer can have multiple active subscriptions, so these totals can differ.
Define the end date before writing SQL
This fictional US, UK, and European subscription example treats starts as inclusive and ends as exclusive. A record ending November 12 is no longer active on November 12. A replacement record starting November 12 is active that day. NULL in ends means no scheduled end, not missing or unverified information.
The snapshot date is November 12, 2026. US-A has an old subscription ending that day and a replacement starting that day. UK-B has two overlapping subscriptions. DE-C ends that day. FR-D starts November 13 and must not appear yet. These are synthetic identifiers and contract dates for interview practice.
Run the snapshot query
WITH subscriptions(id, customer, starts, ends) AS (
VALUES (1,'US-A','2026-11-01','2026-11-12'),
(2,'US-A','2026-11-12',NULL),
(3,'UK-B','2026-11-12','2026-12-01'),
(4,'DE-C','2026-11-01','2026-11-12'),
(5,'FR-D','2026-11-13',NULL),
(6,'UK-B','2026-11-10','2026-11-20')
)
SELECT COUNT(*) AS active_subscriptions,
COUNT(DISTINCT customer) AS active_customers
FROM subscriptions
WHERE starts <= '2026-11-12'
AND (ends > '2026-11-12' OR ends IS NULL);
Why are there three subscriptions but only two customers?
The tested result is 3 active subscription records and 2 active customers. Active IDs are 2, 3, and 6. US-A contributes the replacement record; UK-B contributes two records but one distinct customer. DE-C has ended, and FR-D has not started.
The overlapping UK records might represent two legitimate products or a data-quality problem. The snapshot query cannot decide that. Audit the product and contract keys before deduplicating. Removing one record simply to make the two totals match would change the definition of the metric.
Why not use BETWEEN?
BETWEEN includes both endpoints. With an inclusive end test, the old US-A record and DE-C’s ended record would still count on November 12. The result would become 5 subscriptions and 3 customers for this fixture. That contradicts the exclusive-end contract definition.
SQLite’s expression reference documents BETWEEN, comparison operators, and NULL tests. The parentheses keep the open-ended condition inside the start-date requirement. Without them, an OR ends IS NULL branch can admit future subscriptions such as FR-D.
Does an active subscription mean an active user?
No. This snapshot measures entitlement or contract activity according to the supplied interval. It does not show that a customer logged in, used the product, paid an invoice, or generated recognized revenue on the reporting date. A daily active-user metric requires qualifying usage events and a documented user identity.
A customer with two subscriptions should count twice only when the metric’s unit is subscriptions. For customer coverage, deduplicate the customer key after selecting active intervals. Before production reporting, audit missing customer IDs because COUNT(DISTINCT customer) omits NULL identifiers. The missing-customer-ID audit explains why event or record counts cannot substitute for identified people.
What changes with timestamps or historical reporting?
This query uses valid, uniformly formatted YYYY-MM-DD date strings. For timestamp-based entitlements, compare timestamps in a consistent time basis and define the exact snapshot instant. A London midnight snapshot and a New York midnight snapshot are different instants. Do not strip times before deciding which business definition is intended.
If your source stores the last active date inclusively, translate that documented convention or use an inclusive end predicate. Do not change the operator without changing the definition and boundary tests. Use the effective access-end date, not automatically the cancellation-request date: a customer who requests cancellation on November 12 but retains access through November 30 remains active in the November 12 snapshot, with an exclusive end of December 1. If cancellation ends access immediately, use that effective timestamp instead. Confirm which source field represents this boundary. Reject reversed intervals, investigate missing starts, and define whether trials, paused plans, or suspended accounts count.
A present-day subscription table may not reproduce what the business knew at a past reporting cutoff. Backdated cancellations and corrected start dates can revise earlier snapshots. If the requirement is an as-reported historical number, preserve source versions or snapshots rather than assuming today’s records are unchanged history.
How should you validate the report?
Check a record starting exactly at the cutoff, one ending exactly at the cutoff, a future open-ended record, and a customer with overlapping records. This fixture contains all four. List the qualifying IDs during review so an apparently reasonable aggregate does not conceal the wrong membership.
For a full daily series, evaluate the same rule against a calendar of reporting dates and retain dates with zero active records. Do not sum daily customer counts to estimate unique customers over the whole period. Continue with daily versus weekly distinct users and date boundaries in SQL for the related reporting distinctions.