SQL CREATE INDEX

The command that actually builds one.

What & Why

CREATE INDEX builds the index structure from the previous lesson. Once created, it's maintained automatically — you never manually update it yourself.

See How It Works

BUSINESS QUESTION

Growth wants a concrete event-name index proposal backed by the representative event distribution.

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

-- Proposed DDL: CREATE INDEX idx_growth_events_event_name ON growth.events (event_name);
WITH distribution AS (
  SELECT event_name, COUNT(*) AS matching_rows
  FROM growth.events
  GROUP BY event_name
)
SELECT
  event_name,
  matching_rows,
  ROUND(matching_rows::numeric / NULLIF(SUM(matching_rows) OVER (), 0), 4) AS table_fraction
FROM distribution
ORDER BY matching_rows DESC, event_name;

RESULT — event-name distribution

event_name	matching_rows	table_fraction
signup	2	0.5000
activated	1	0.2500
report_viewed	1	0.2500

This read-only result helps evaluate whether an event-name index would be selective.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice