Supabase SQL Examples: 15 Copy-Ready Queries

Quick answer

Signups per day for the last 30 days, straight from Supabase Auth. Run it in the SQL editor.

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

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, raw_user_meta_data)	Readable from the SQL editor (postgres role). Don’t expose it through the API.
Your app tables	public.profiles (id, full_name, plan, created_at), public.events (user_id, name, created_at)	Examples; rename to your own tables.
Postgres catalog	pg_stat_user_tables, pg_stat_activity, pg_constraint, pg_index	Built into every Postgres.
demo_shop (AI2SQL)	customers (id, signup_date), orders (customer_id, order_date, status)	For the cohort query only.

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

Copy-ready SQL

Users

1. Total, confirmed and anonymous users

Purpose: the size of your user base. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select
  count(*) as users,
  count(*) filter (where email_confirmed_at is not null) as confirmed,
  count(*) filter (where is_anonymous) as anonymous
from auth.users;

users	confirmed	anonymous
4210	3388	112

2. Signups per week

Purpose: the growth curve. Change: week to month.

-- PostgreSQL (Supabase SQL editor)
select date_trunc('week', created_at)::date as week, count(*) as signups
from auth.users
where not coalesce(is_anonymous, false)
group by 1
order by 1 desc
limit 12;

week	signups
2026-10-05	188

3. Users by sign-in provider

Purpose: email vs Google vs GitHub. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select raw_app_meta_data ->> 'provider' as provider, count(*) as users
from auth.users
group by 1
order by users desc;

provider	users
email	2210
google	1830

4. Users who never came back

Purpose: signed up more than 7 days ago and never signed in again. Change: the 7 days.

-- PostgreSQL (Supabase SQL editor)
select id, email, created_at
from auth.users
where created_at < now() - interval '7 days'
  and (last_sign_in_at is null or last_sign_in_at < created_at + interval '1 hour')
order by created_at desc
limit 100;

id	email	created_at
7c1e…	sam@example.com	2026-09-28 10:14

5. Look up a user with their profile

Purpose: support lookups. Change: the email.

-- PostgreSQL (Supabase SQL editor)
select u.id, u.email, u.created_at, u.last_sign_in_at, p.full_name, p.plan
from auth.users u
left join public.profiles p on p.id = u.id
where u.email = 'sam@example.com';

id	email	created_at	last_sign_in_at	full_name	plan
7c1e…	sam@example.com	2026-09-28 10:14	2026-10-10 08:02	Sam Lee	pro

6. Profiles table that fills itself on signup

Purpose: a public table your app can read, created for every new user. Change: the profile columns.

-- PostgreSQL (Supabase SQL editor)
create table if not exists public.profiles (
  id uuid not null references auth.users on delete cascade,
  full_name text,
  plan text default 'free',
  created_at timestamptz default now(),
  primary key (id)
);

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, full_name)
  values (new.id, new.raw_user_meta_data ->> 'full_name');
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

7. Row level security: users read and edit only their own profile

Purpose: make profiles safe to query from the browser. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
alter table public.profiles enable row level security;

drop policy if exists "Users can view own profile" on public.profiles;
create policy "Users can view own profile" on public.profiles
  for select to authenticated
  using ((select auth.uid()) = id);

drop policy if exists "Users can update own profile" on public.profiles;
create policy "Users can update own profile" on public.profiles
  for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

8. Users by plan

Purpose: free vs paid split from your profiles table. Change: plan to your column.

-- PostgreSQL (Supabase SQL editor)
select coalesce(p.plan, '(no profile)') as plan, count(*) as users
from auth.users u
left join public.profiles p on p.id = u.id
group by 1
order by users desc;

plan	users
free	3920
pro	290

Activity

9. Daily active users from your events table

Purpose: real usage, not sign-ins. Change: public.events to your activity table.

-- PostgreSQL (Supabase SQL editor)
select date_trunc('day', created_at)::date as day, count(distinct user_id) as dau
from public.events
where created_at >= now() - interval '30 days'
group by 1
order by 1 desc;

day	dau
2026-10-11	412

10. DAU / MAU stickiness

Purpose: how often monthly users come back. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
with daily as (
  select date_trunc('day', created_at) as day, count(distinct user_id) as dau
  from public.events
  where created_at >= now() - interval '30 days'
  group by 1
)
select
  round(avg(dau)) as avg_dau,
  (select count(distinct user_id) from public.events where created_at >= now() - interval '30 days') as mau,
  round(100.0 * avg(dau) / nullif((select count(distinct user_id) from public.events where created_at >= now() - interval '30 days'), 0), 1) as stickiness_pct
from daily;

avg_dau	mau	stickiness_pct
398	1840	21.6

11. Month-1 retention by signup cohort

Purpose: do new users come back? Full cohort grids on the auth.users page. Change: the events table.

-- PostgreSQL (Supabase SQL editor)
with cohorts as (
  select id, date_trunc('month', created_at) as cohort
  from auth.users
  where not coalesce(is_anonymous, false)
),
activity as (
  select distinct user_id, date_trunc('month', created_at) as month
  from public.events
)
select
  c.cohort::date,
  count(distinct c.id) as signups,
  round(100.0 * count(distinct a.user_id) / nullif(count(distinct c.id), 0), 1) as month_1_retention_pct
from cohorts c
left join activity a on a.user_id = c.id and a.month = c.cohort + interval '1 month'
group by c.cohort
order by c.cohort desc;

cohort	signups	month_1_retention_pct
2026-08-01	760	24.1

Database health

12. Biggest tables

Purpose: what is using your storage. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select
  schemaname || '.' || relname as table_name,
  pg_size_pretty(pg_total_relation_size(relid)) as total_size,
  n_live_tup as approx_rows
from pg_stat_user_tables
order by pg_total_relation_size(relid) desc
limit 20;

table_name	total_size	approx_rows
public.events	412 MB	3120044

13. Foreign keys without an index

Purpose: the most common cause of slow joins and deletes. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select
  c.conrelid::regclass as table_name,
  a.attname as column_name
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
where c.contype = 'f'
  and array_length(c.conkey, 1) = 1
  and c.connamespace = 'public'::regnamespace
  and not exists (
    select 1 from pg_index i
    where i.indrelid = c.conrelid and i.indkey[0] = c.conkey[1]
  )
order by 1, 2;

table_name	column_name
events	user_id

14. Open connections by state

Purpose: are you close to the connection limit? Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select usename, state, count(*) as connections
from pg_stat_activity
where datname = current_database()
group by 1, 2
order by connections desc;

usename	state	connections
authenticator	idle	18
postgres	active	1

15. Tables in public without row level security

Purpose: catch tables the API can read without policies. Change: nothing.

-- PostgreSQL (Supabase SQL editor)
select c.relname as table_name
from pg_class c
where c.relnamespace = 'public'::regnamespace
  and c.relkind = 'r'
  and not c.relrowsecurity
order by 1;

table_name
events

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'
)
select
  c.cohort,
  count(distinct c.id) as signups,
  count(distinct a.customer_id) filter (where a.month = c.cohort + interval '1 month') as active_month_1,
  round(100.0 * count(distinct a.customer_id) filter (where a.month = c.cohort + interval '1 month')
        / count(distinct c.id), 1) as month_1_retention_pct
from cohorts c
left join activity a on a.customer_id = c.id
group by c.cohort
order by c.cohort desc