SQL Reporting and Analytics Projects: Build a Daily Activity Report

For this project, an active user is a user with any recorded event on a day. Purchase count is the number of purchase event rows, not the number of distinct purchasers. Known purchase value is the sum of present amounts on purchase events. Missing purchase amounts are counted separately. These definitions make repeated activity and incomplete money fields visible.

Define event_day as a calendar day already assigned by the data pipeline for this fixture. A production project would specify the reporting timezone and how raw timestamps become that day. The event id is a unique source identity. If an import can replay events, add a reliable ingestion or uniqueness policy so one occurrence cannot be loaded twice and counted as new activity.

The raw table has one row per event. A user may have several events on a date, so COUNT(*) does not measure daily active users. COUNT(DISTINCT user_id) grouped by event_day counts the unique participating users. The daily output has one row per observed calendar day, a different grain from the source event detail.

The fixture does not create rows for dates with no events. A report promising every calendar day needs a date dimension or generated calendar joined to the daily measures. Choosing that complete-calendar shape is separate from aggregating the event table. Explicitly state whether absent output days mean zero activity or simply an unrepresented calendar date.

Conditional aggregation lets one daily grouping calculate several related metrics. COUNT(CASE WHEN event_type='purchase' THEN 1 END) counts purchase rows, while SUM(CASE WHEN event_type='purchase' THEN amount END) adds only their known amounts. An explicit CASE for purchase rows with amount IS NULL measures incompleteness. Each expression has its own population rule.

Do not filter the whole source to purchases if the active-user measure includes signup activity. That WHERE would redefine activity for every metric. A shared reporting-period condition can belong in WHERE because all columns refer to the same days. Keep measure-specific conditions inside their CASE expressions so the intended populations stay independent.

On January 1, users 1 and 2 are active, and one purchase contributes twenty. On January 2, users 1, 2 and 3 are active, and two purchases contribute thirty plus ten. January 3 has user 3 and one purchase with an unknown amount. Its known-value sum is NULL because no known purchase amount exists.

The purchase-event total across all days is four. The known purchase amounts total sixty. Adding daily distinct-user counts produces six user-day observations, not three unique users overall. Those are different measures. A project should include this distinction in its metric dictionary so another analyst does not reaggregate the daily table under an incorrect interpretation.

A running revenue calculation can apply a window over ordered daily summaries. Choose a frame and missing-value policy explicitly; treating an unknown-only day as zero is a reporting assumption. A moving average needs a complete calendar if its window is meant to represent a fixed number of days rather than a fixed number of observed report rows.

A signup cohort analysis needs a user’s first qualifying signup date and subsequent behaviour measured relative to it. Joining every signup event to every purchase can multiply data if repeat signups exist. Prepare one cohort identity per user before joining activity. Retention, conversion and lifetime value each need their own denominator and observation window rather than being copied from the active-user count.

Keep the schema, fixture insert script, metric definitions, report query and expected output together. Include a small validation query for unique event ids, recognised event types and missing purchase amounts. Document the target engine and minimum version if you add windows, date generation or dialect-specific formatting. A reviewer should be able to reproduce the report without guessing hidden source assumptions.

For a dashboard, expose an as-of time and completeness indicator when data arrives late. Use a consistent snapshot or refresh process if several widgets must reconcile. Measure performance on realistic volumes and inspect the plan, but retain the tiny fixture as a regression check. A change that makes the chart faster while altering active-user meaning or concealing unknown purchase value must be reviewed as a metric change.

The seven events span three observed days. Several users have different event types on a day, and the final purchase has an unknown amount. The complete fixture uses ordinary shared SQL declarations and can be run on the four major engines discussed in this course.

CREATE TABLE analytics_events (
 id INTEGER PRIMARY KEY,user_id INTEGER NOT NULL,event_day DATE NOT NULL,
 event_type VARCHAR(20) NOT NULL,amount DECIMAL(10,2)
);
INSERT INTO analytics_events (id,user_id,event_day,event_type,amount) VALUES
 (1,1,'2026-01-01','signup',NULL),(2,1,'2026-01-01','purchase',20),
 (3,2,'2026-01-01','signup',NULL),(4,2,'2026-01-02','purchase',30),
 (5,1,'2026-01-02','purchase',10),(6,3,'2026-01-02','signup',NULL),
 (7,3,'2026-01-03','purchase',NULL);

Calculate overall distinct active users, purchase events and known purchase value across the full fixture. Keep all event types available to the user-count measure.

SELECT COUNT(DISTINCT user_id) AS distinct_users,
 COUNT(CASE WHEN event_type='purchase' THEN 1 END) AS purchase_events,
 SUM(CASE WHEN event_type='purchase' THEN amount END) AS known_value
FROM analytics_events;

distinct_users | purchase_events | known_value
3 | 4 | 60

The unique identities are 1, 2 and 3, giving three users across the period. Four purchase event rows exist, including the incomplete final purchase. Known monetary inputs are twenty, thirty and ten, which sum to sixty. The task deliberately calculates period-wide distinct users from raw identity rather than adding the daily distinct counts, while the purchase count and known-value sum reconcile additively.

Use the PostgreSQL aggregate-expression reference, window tutorial and SQLite aggregate reference for the underlying SQL calculations.