SQL Days Since Signup

A specific, extremely common application of subtracting dates — measuring how long a user or campaign has been active.

What & Why

Subtracting a signup date from a reporting date returns elapsed calendar days. This worked result uses April 30, 2024 as a fixed reporting boundary for the four growth.users rows.

See How It Works

BUSINESS QUESTION

Growth wants every user account's age in days as of 2024-04-30.

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

EXAMPLE QUERY

SELECT
  id AS user_id,
  created_at::date AS signup_date,
  DATE '2024-04-30' - created_at::date AS days_since_signup
FROM growth.users
ORDER BY days_since_signup DESC, user_id;

RESULT — exact output from the displayed Queryflo rows

user_id	signup_date	days_since_signup
101	2024-01-15	106
102	2024-02-10	80
103	2024-03-12	49
104	2024-04-01	29

All account ages use the same fixed 2024-04-30 report date.

Now You Try

Practice this concept

Calculate days since signup for each growth.users row as of the fixed date 2024-04-30, ordered from longest to shortest.

Available schema

growth

Prefix tables with growth.table_name.

user_idsignup_datedays_since_signup

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