SQL Month-to-Date

MTD's monthly counterpart to YTD — from the 1st of this month through today, same DATE_TRUNC pattern at the narrower grain.

What & Why

Month-to-date (MTD) is the same idea as YTD, scoped to the current month instead of the current year — mechanically identical to the earlier "Current Month" lesson, with the business-reporting framing that MTD implies a comparison against a prior month's equivalent point.

See How It Works

BUSINESS QUESTION

Using 2024-03-31 as a fixed report date, Marketing wants the month-to-date lead count and needs to see that the count resets when a new month begins.

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

Watch the month-to-date count reset at a new monthStep 1 of 2

2024-03-01 <= created_at < 2024-04-01

as_of_date	mtd_leads
2024-03-31	1

As of March 31, Lead 304 is the one marketing.leads row inside March month-to-date.

EXAMPLE QUERY

SELECT
  DATE '2024-03-31' AS as_of_date,
  COUNT(*) AS mtd_leads
FROM marketing.leads
WHERE created_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2024-04-01 00:00:00+00';

Now You Try

Practice this concept

Using 2024-03-31 as the fixed report date, Marketing wants the month-to-date lead count.

Available schema

marketing

Prefix tables with marketing.table_name.

as_of_datemtd_leads

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