Annual Enterprise Accounts Signed Up in 2024

Easy
398.

Annual Enterprise Accounts Signed Up in 2024

EasyBasicsDates

MySQL

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

ColumnTypeValues
account_idint
company_namevarchar
planenumfree, starter, growth, enterprise
billing_cycleenummonthly, annual
signed_up_ondate

Primary key: account_id.

One row per customer account (a company, not a person).

Example

The rows must come back in the order shown.

accounts

account_idcompany_nameplanbilling_cyclesigned_up_on
1Zentrix Labsenterpriseannual2024-03-14
2Quillpadgrowthannual2024-01-09
3Ledgerlyenterprisemonthly2024-05-02
4Orbitpayenterpriseannual2023-12-31
5Vaani AIenterpriseannual2024-01-01
6Mintmarkenterpriseannual2024-03-14
7Tiffinlystartermonthly2024-07-20

Output

account_idcompany_name
5Vaani AI
1Zentrix Labs
6Mintmark
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
company_namevarchar
planenum(free, starter, growth, enterprise)
billing_cycleenum(monthly, annual)
signed_up_ondate

Primary key: account_id.

One row per customer account (a company, not a person).

Examples

Example 1

accounts

account_idcompany_nameplanbilling_cyclesigned_up_on
1Zentrix Labsenterpriseannual2024-03-14
2Quillpadgrowthannual2024-01-09
3Ledgerlyenterprisemonthly2024-05-02
4Orbitpayenterpriseannual2023-12-31
5Vaani AIenterpriseannual2024-01-01
6Mintmarkenterpriseannual2024-03-14
7Tiffinlystartermonthly2024-07-20

Output

account_idcompany_name
5Vaani AI
1Zentrix Labs
6Mintmark

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_id

Another 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_id

Another 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 →