# This Year's Class

> The cohort is in. Time to count who made it through the door.

Canonical URL: <https://datadriven.io/problems/this_year_s_class>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The growth team is pulling together the 2025 signup class for its year-end report. Count how many users joined that year.

## Worked solution and explanation

### What is really being asked

Strip off the cohort-reporting costume and this is a single-number count of rows that fall inside one calendar year. It looks trivial, and the COUNT part is. The thing that actually separates candidates is how they carve the year window out of signup_date. That column is a text date like '2025-11-11', and the sandbox is SQLite, so the YEAR() function you reach for out of habit does not exist here. Pick the wrong tool for the window and you either crash the query or quietly count the wrong years.

> **The whole problem is the year filter**
>
> COUNT(*) is the easy half. The real move is turning a full date into just its year and comparing that to '2025'. In SQLite the clean way is strftime('%Y', signup_date), which returns the four-character year as text, so you compare it to the string '2025', not the number 2025.

**The count for this year's class**

```sql
SELECT COUNT(*) AS signup_count
FROM users
WHERE strftime('%Y', signup_date) = '2025';
```

*Extract the year from each signup_date, keep only the target year, then count the survivors.*

#### Step 1: Reduce each date to its year

strftime('%Y', signup_date) takes '2025-11-11' and hands back '2025'. Doing this in the WHERE clause means every row is judged by its year alone, which is exactly the grain the growth team cares about.

#### Step 2: Compare against a string, not an integer

strftime always returns text, so the comparison is = '2025' with quotes. Writing = 2025 makes SQLite compare text to a number and no row matches, so you silently get zero. This is the most common way this easy question goes wrong.

#### Step 3: Count what is left

COUNT(*) over the filtered rows gives the single number the report wants. Aliasing it AS signup_count gives the output a clean column name instead of the raw expression.

**Habit from Postgres/MySQL**

WHERE EXTRACT(YEAR FROM signup_date) = 2025 or WHERE YEAR(signup_date) = 2025. Both throw 'no such function' in this sandbox.

**Works in SQLite**

WHERE strftime('%Y', signup_date) = '2025'. Portable to a LIKE '2025%' prefix match only because the dates are stored as ISO 'YYYY-MM-DD' text.

> **The BETWEEN off-by-one**
>
> Some candidates reach for signup_date BETWEEN '2025-01-01' AND '2025-12-31'. It usually works here, but the moment signup_date carries a time component ('2025-12-31 14:00:00') the upper bound excludes the last day. Anchoring on the extracted year sidesteps that trap entirely.

> **What the interviewer is watching for**
>
> On an easy count, the tell is whether you notice the column is a text date and reach for the engine's date function rather than assuming YEAR() exists everywhere. Naming the string-vs-integer comparison out loud signals you have been burned by it before.

> **Wrapping the column blocks the index**
>
> strftime(...) on signup_date means any index on that column cannot be used, so this is a full scan. On ten rows it is free; on a hundred million it is not. In production you would filter with a sargable range like signup_date >= '2025-01-01' AND signup_date < '2026-01-01', which lets the index seek the year window.

## Common follow-up questions

- Now break the same count down by year instead of one fixed year. _(Tests moving the extracted year from the WHERE clause into GROUP BY and the SELECT list.)_
- Count only signups whose account_status is 'active' in that year. _(Adds a second predicate and checks they can combine filters cleanly.)_
- Rewrite it so an index on signup_date can be used. _(Probes the sargable range-filter rewrite from the performance note.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/this_year_s_class)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.