SQL Filtering the Last 30 Days

The exact same pattern as the last 7 days, with the number swapped out — a wider rolling window, checked against every campaign this time.

What & Why

The identical shape as the previous lesson — WHERE date_column >= CURRENT_DATE - n — just with 30 in place of 7. Widening the window naturally lets more rows through.

See How It Works

BUSINESS QUESTION

Using 2024-04-15 as a fixed report date, Growth wants users created since the inclusive 2024-03-16 cutoff.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

Watch the rolling window keep recent rowsExample 1 of 4

as of 2024-04-15 · created_at >= 2024-03-16

id	created_at	rolling_window_result
101	2024-01-15 09:10:00+00	outside window
102	2024-02-10 11:05:00+00	?
103	2024-03-12 16:35:00+00	?
104	2024-04-01 13:20:00+00	?

User 101 falls before the fixed March 16 cutoff.

EXAMPLE QUERY

SELECT
  id AS user_id,
  channel,
  created_at
FROM growth.users
WHERE created_at >= TIMESTAMPTZ '2024-03-16 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2024-04-16 00:00:00+00'
ORDER BY created_at DESC, user_id;

Now You Try

Practice this concept

Using 2024-04-15 as the fixed report date, Growth wants users in the reproducible trailing 30-day window.

Available schema

growth

Prefix tables with growth.table_name.

user_idchannelcreated_at

growth.users

Column	Type
id	integer
created_at	timestamp with time zone
country	text
channel	text
plan	text
activated_at	timestamp with time zone
churned_at	timestamp with time zone
legacy_user_code	text

query.sql

Intermediate business practice