Query Supabase auth.users: Signups, Active Users and Cohorts

Quick answer

Signups per week, and how many of those users are still active (seen in the last 30 days in your events table).

-- PostgreSQL (Supabase SQL editor)
select
  date_trunc('week', u.created_at)::date as signup_week,
  count(*) as signups,
  count(*) filter (where exists (
    select 1 from public.events e
    where e.user_id = u.id and e.created_at >= now() - interval '30 days'
  )) as still_active
from auth.users u
where not coalesce(u.is_anonymous, false)
group by 1
order by 1 desc
limit 12;

Change public.events to any table your app writes when a user does something. Without one, swap the exists for u.last_sign_in_at >= now() - interval '30 days' and read the caveat under Common mistakes.

Schema assumptions

Source	Tables used	Notes
Supabase Auth	auth.users (id, email, created_at, last_sign_in_at, email_confirmed_at, is_anonymous, raw_app_meta_data), auth.sessions (user_id, created_at, updated_at)	Managed by Supabase; read-only for analytics. Rows in auth.sessions disappear on sign-out and expiry.
Your app	public.events (user_id, name, created_at)	Any activity table works: page views, created records, API calls.
demo_shop (AI2SQL)	customers (id, signup_date), orders (customer_id, order_date, status)	Cohort query only.

All queries are PostgreSQL for the Supabase SQL editor. Example outputs use made-up data.

Copy-ready SQL

1. Signups per day with confirmation rate

Purpose: daily signups and how many finish email confirmation. Change: the 30 days.

-- PostgreSQL (Supabase SQL editor)
select
  date_trunc('day', created_at)::date as day,
  count(*) as signups,
  round(100.0 * count(*) filter (where email_confirmed_at is not null) / count(*), 1) as confirmed_pct
from auth.users
where created_at >= now() - interval '30 days'
  and not coalesce(is_anonymous, false)
group by 1
order by 1 desc;

day	signups	confirmed_pct
2026-10-11	31	83.9

2. DAU, WAU and MAU from your events table

Purpose: active users on three horizons. Change: public.events.

-- PostgreSQL (Supabase SQL editor)
select
  count(distinct user_id) filter (where created_at >= now() - interval '1 day') as dau,
  count(distinct user_id) filter (where created_at >= now() - interval '7 days') as wau,
  count(distinct user_id) filter (where created_at >= now() - interval '30 days') as mau
from public.events
where created_at >= now() - interval '30 days';

dau	wau	mau
412	1105	1840

3. Users with a live session right now

Purpose: a rough “online now” number without an events table. Change: the 1 hour.

-- PostgreSQL (Supabase SQL editor)
select count(distinct user_id) as users_active_last_hour
from auth.sessions
where updated_at >= now() - interval '1 hour';

users_active_last_hour
57

4. Activation: signups who did the key action within 7 days

Purpose: the share of new users who reach the “aha” step. Change: 'project_created' to your activation event.

-- PostgreSQL (Supabase SQL editor)
select
  date_trunc('week', u.created_at)::date as signup_week,
  count(*) as signups,
  count(*) filter (where exists (
    select 1 from public.events e
    where e.user_id = u.id
      and e.name = 'project_created'
      and e.created_at < u.created_at + interval '7 days'
  )) as activated,
  round(100.0 * count(*) filter (where exists (
    select 1 from public.events e
    where e.user_id = u.id
      and e.name = 'project_created'
      and e.created_at < u.created_at + interval '7 days'
  )) / count(*), 1) as activation_pct
from auth.users u
where u.created_at < now() - interval '7 days'
  and not coalesce(u.is_anonymous, false)
group by 1
order by 1 desc
limit 12;

signup_week	signups	activated	activation_pct
2026-09-28	176	71	40.3

5. Weekly retention cohorts

Purpose: the classic cohort grid. Each row is a signup week; columns are the share of that cohort active in weeks 1 to 4. Change: public.events.

-- PostgreSQL (Supabase SQL editor)
with cohorts as (
  select id, date_trunc('week', created_at) as cohort
  from auth.users
  where not coalesce(is_anonymous, false)
),
activity as (
  select distinct user_id, date_trunc('week', created_at) as week
  from public.events
),
joined as (
  select c.cohort, c.id, (extract(epoch from (a.week - c.cohort)) / 604800)::int as week_n
  from cohorts c
  left join activity a on a.user_id = c.id and a.week >= c.cohort
)
select
  cohort::date,
  count(distinct id) as signups,
  round(100.0 * count(distinct id) filter (where week_n = 1) / count(distinct id), 1) as w1_pct,
  round(100.0 * count(distinct id) filter (where week_n = 2) / count(distinct id), 1) as w2_pct,
  round(100.0 * count(distinct id) filter (where week_n = 3) / count(distinct id), 1) as w3_pct,
  round(100.0 * count(distinct id) filter (where week_n = 4) / count(distinct id), 1) as w4_pct
from joined
where cohort >= now() - interval '12 weeks'
group by cohort
order by cohort desc;

cohort	signups	w1_pct	w2_pct	w3_pct	w4_pct
2026-09-07	182	31.9	22.5	19.2	17.0
2026-08-31	169	30.2	24.3	18.9	16.6

6. Signups by provider per month

Purpose: is Google sign-in taking over from email? Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select
  date_trunc('month', created_at)::date as month,
  count(*) filter (where raw_app_meta_data ->> 'provider' = 'email') as email,
  count(*) filter (where raw_app_meta_data ->> 'provider' = 'google') as google,
  count(*) filter (where raw_app_meta_data ->> 'provider' = 'github') as github,
  count(*) as total
from auth.users
group by 1
order by 1 desc;

month	email	google	github	total
2026-09-01	402	377	61	840

7. Unconfirmed signups older than 7 days

Purpose: a list for a reminder email, or for cleanup. Change: the 7 days.

-- PostgreSQL (Supabase SQL editor)
select id, email, created_at
from auth.users
where email_confirmed_at is null
  and email is not null
  and created_at < now() - interval '7 days'
order by created_at desc;

id	email	created_at
1f9a…	kim@example.com	2026-09-30 22:41

with cohorts as (
  select id, date_trunc('month', signup_date)::date as cohort
  from customers
),
activity as (
  select distinct customer_id, date_trunc('month', order_date)::date as month
  from orders
  where status <> 'cancelled'
),
joined as (
  select
    c.cohort,
    c.id,
    (extract(year from age(a.month, c.cohort)) * 12 + extract(month from age(a.month, c.cohort)))::int as month_n
  from cohorts c
  left join activity a on a.customer_id = c.id and a.month >= c.cohort
)
select
  cohort,
  count(distinct id) as signups,
  round(100.0 * count(distinct id) filter (where month_n = 1) / count(distinct id), 1) as m1_pct,
  round(100.0 * count(distinct id) filter (where month_n = 2) / count(distinct id), 1) as m2_pct,
  round(100.0 * count(distinct id) filter (where month_n = 3) / count(distinct id), 1) as m3_pct
from joined
group by cohort
order by cohort desc