SQL Filtering the Last 7 Days

A WHERE clause comparing a date column to CURRENT_DATE minus 7 — the standard shape of a rolling weekly filter.

What & Why

A rolling seven-day filter uses a lower timestamp and an exclusive upper timestamp. The worked Queryflo result fixes the window to March 8 through March 15, 2024 and returns the one event inside it.

See How It Works

BUSINESS QUESTION

Using 2024-03-15 as a fixed report timestamp, Growth wants events from the preceding seven days.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

SELECT
  event_name,
  COUNT(*) AS events
FROM growth.events
WHERE created_at >= TIMESTAMPTZ '2024-03-08 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2024-03-15 00:00:00+00'
GROUP BY event_name
ORDER BY events DESC, event_name;

RESULT — exact output from the displayed Queryflo rows

event_name	events
report_viewed	1

The fixed March 8–15 window contains only event 1004.

Now You Try

Practice this concept

Count growth.events in the fixed seven-day window from March 8 through March 15, 2024, ordered by count and event name.

Available schema

growth

Prefix tables with growth.table_name.

event_nameevents

growth.events

Column	Type
id	bigint
user_id	integer
event_name	text
created_at	timestamp with time zone
properties	jsonb
deprecated_metric	text

query.sql

Intermediate business practice