SQL Year-to-Date

The business-reporting name for 'current year' — often abbreviated YTD, comparing this year's progress to the same point in a prior year.

What & Why

Year to date means January 1 through the reporting cutoff. The worked Queryflo table fixes the cutoff at April 1, 2024, so it contains the January, February, and March leads.

See How It Works

BUSINESS QUESTION

Using 2024-03-31 as the report date, Marketing wants leads created since the start of that year.

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

EXAMPLE QUERY

SELECT
  id AS lead_id,
  source,
  created_at
FROM marketing.leads
WHERE created_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2024-04-01 00:00:00+00'
ORDER BY created_at, lead_id;

RESULT — exact output from the displayed Queryflo rows

lead_id	source	created_at
301	google_ads	2024-01-21 09:10:00+00
302	google_ads	2024-01-24 12:40:00+00
303	email	2024-02-16 08:30:00+00
304	linkedin	2024-03-20 17:15:00+00

The fixed January 1–April 1 boundaries contain all four displayed leads.

Now You Try

Practice this concept

Return leads in the fixed 2024 year-to-date window from January 1 through April 1, ordered by creation time and lead id.

Available schema

marketing

Prefix tables with marketing.table_name.

lead_idsourcecreated_at

marketing.leads

Column	Type
id	integer
campaign_id	integer
email	text
created_at	timestamp with time zone
qualified_at	timestamp with time zone
converted_at	timestamp with time zone
lead_score	integer
source	text
country	text
archive_status	text

query.sql

Intermediate business practice