Annual Enterprise Accounts Signed Up in 2024 — SQL Problem
The finance team at a project-management SaaS wants to reconcile this year's enterprise contracts with what was billed up front.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #398
Problem statement
The finance team at a project-management SaaS wants to reconcile this year's enterprise contracts with what was billed up front.
Return every account on the enterprise plan with annual billing whose signed_up_on falls in the calendar year 2024, with the columns account_id and company_name. Return the rows ordered by signed_up_on, then by account_id.
Tables
Table: accounts
| Column | Type |
|---|---|
| account_id | int |
| company_name | varchar |
| plan | enum(free, starter, growth, enterprise) |
| billing_cycle | enum(monthly, annual) |
| signed_up_on | date |
Primary key: account_id.
One row per customer account (a company, not a person).
Examples
Example 1
accounts
| account_id | company_name | plan | billing_cycle | signed_up_on |
|---|---|---|---|---|
| 1 | Zentrix Labs | enterprise | annual | 2024-03-14 |
| 2 | Quillpad | growth | annual | 2024-01-09 |
| 3 | Ledgerly | enterprise | monthly | 2024-05-02 |
| 4 | Orbitpay | enterprise | annual | 2023-12-31 |
| 5 | Vaani AI | enterprise | annual | 2024-01-01 |
| 6 | Mintmark | enterprise | annual | 2024-03-14 |
| 7 | Tiffinly | starter | monthly | 2024-07-20 |
Output
| account_id | company_name |
|---|---|
| 5 | Vaani AI |
| 1 | Zentrix Labs |
| 6 | Mintmark |
How to solve Annual Enterprise Accounts Signed Up in 2024
This is a pure filter: keep the rows whose plan is enterprise, whose billing cycle is annual, and whose signup date lies in 2024, then sort.
The year test is best written as a range — signed_up_on >= '2024-01-01' AND signed_up_on < '2025-01-01'. It includes both 1 January and 31 December, and because the column itself is compared (not a function of it), an index on signed_up_on can serve it. YEAR(signed_up_on) = 2024 gives the same rows and reads more naturally, but wraps the column in a function, which stops a plain index from being used on a large table. BETWEEN '2024-01-01' AND '2024-12-31' is correct for a date column; on a datetime column it would miss everything after midnight on 31 December, which is why the half-open range is the habit worth keeping.
The order is fixed by the statement, and signup dates repeat, so account_id breaks ties. The query reads the table once.
Reference solution (MySQL)
SELECT account_id, company_name
FROM accounts
WHERE plan = 'enterprise'
AND billing_cycle = 'annual'
AND signed_up_on >= '2024-01-01' AND signed_up_on < '2025-01-01'
ORDER BY signed_up_on, account_idAnother way
SELECT account_id, company_name FROM accounts WHERE plan = 'enterprise' AND billing_cycle = 'annual' AND YEAR(signed_up_on) = 2024 ORDER BY signed_up_on, account_idAnother way
SELECT account_id, company_name FROM accounts WHERE plan = 'enterprise' AND billing_cycle = 'annual' AND signed_up_on BETWEEN '2024-01-01' AND '2024-12-31' ORDER BY signed_up_on, account_id← Products Most Often Bought in the Same Basket · Signups Who Never Created a Project →