Project 4 — Search and Analytics

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Project 4 — Search and Analytics
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A production feature needs explicit latency, freshness, indexing cost, and refresh ownership—not just one query. Build ranked search with stable pagination and analytics from events into a rollup view. Document exact product metric definitions beside the SQL.

Connect it to a real scenario

Store daily active learners, completions, and average completion percentage in a materialized view with scheduled refresh. For search, review plans, evaluate a fixed set of top queries, and monitor no-result rate.

Evaluate search ranking with a fixed query set
Define analytics metrics precisely
Assign refresh ownership and an SLA

Try the working example

sql

CREATE MATERIALIZED VIEW app.daily_learning_metrics AS
SELECT date_trunc('day', completed_at) AS day,
       count(DISTINCT enrollment_id) AS active_learners,
       count(*) AS lessons_completed
FROM app.lesson_progress
WHERE completed = true
GROUP BY 1;

CREATE UNIQUE INDEX daily_learning_metrics_day_uidx
  ON app.daily_learning_metrics (day);

REFRESH MATERIALIZED VIEW CONCURRENTLY app.daily_learning_metrics;

You should see

You have a concurrently refreshable daily metrics source for the dashboard.

5-minute try-it

Write weekly completions per course and document the timezone boundary used by the metric.

One important caution

Dashboards disagree when the same metric name hides different timezone, bot/internal-user, or late-data rules.

PostgreSQL — Materialized Views — PostgreSQL Global Development Group

Easy traps

Dashboards disagree when the same metric name hides different timezone, bot/internal-user, or late-data rules.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Write weekly completions per course and document the timezone boundary used by the metric.

You'll know it worked when: You have a concurrently refreshable daily metrics source for the dashboard.