Data Operations SQL Practice Dataset: Funnel and Retention

This is a no-registration PostgreSQL practice dataset for data operations work. Three small tables model user registration, product events, and order states so you can practice definitions, joins, funnels, cohorts, repeat purchase, and anomaly diagnosis.

Every record is synthetic data authored by OfferKu Editorial. It does not describe a real company, person, or business result. The dataset is deliberately small enough to reconcile by hand before you extend a query.

Download

• Download the PostgreSQL practice data (SQL)
• Download the data dictionary, setup guide, and ten exercises (Markdown)

File version: 2026-08-13. The script drops and recreates tables with the same practice names. Run it only in a personal practice database, never in production.

What Is in the Dataset?

The tables join on user_id and include primary keys, foreign keys, required fields, and enum checks. Inserts declare columns explicitly so schema changes are visible. Compare the script with PostgreSQL's official CREATE TABLE reference, inserting-data guide, and foreign-key tutorial.

Recommended Practice Order

Validate before interpreting

After setup, reconcile table counts, primary-key uniqueness, foreign-key coverage, and timestamp order. A payment cannot precede order creation. One user may have many events and orders, so a direct join can multiply rows.

Start with one-table definitions

Calculate daily registrations, channel mix, and order-status distribution. For every metric, write down:

• The row grain.
• Numerator, denominator, and exclusions.
• Whether the clock is created_at or paid_at.
• UTC or a reporting time zone.
• Treatment of cancelled and refunded orders.

Move to cross-table analysis

Work through these questions in order:

1. Activation within 24 hours of registration using onboarding_complete.
2. A user funnel from product_view to add_to_cart, checkout_start, and purchase.
3. First-purchase conversion by acquisition channel.
4. Subsequent activity by registration-week cohort.
5. Users with at least two paid orders.
6. Error-event share by platform and application version.

Build intermediate CTEs and reconcile row counts. This makes duplication and denominator errors easier to see than one long query.

A Starter Query

This example checks registrations by date; it intentionally does not solve the exercise set:

SELECT
  registered_at::date AS registration_date,
  COUNT(*) AS registered_users
FROM users
GROUP BY registered_at::date
ORDER BY registration_date;

Add channel, then verify that channel subtotals still reconcile to the daily count. Before using a window, decide whether “day” means a calendar date or a rolling 24 hours after registration.

Turn the Exercise into a Work Sample

Do not submit SQL screenshots alone. A reviewable work sample should contain:

1. The question and decision owner.
2. Table grain, keys, and data limitations.
3. Metric definitions and queries.
4. At least two quality checks.
5. A result table or chart.
6. Facts, hypotheses, and conclusions the data cannot support.
7. The next data request or experiment.

Keep the synthetic-data disclosure in the work sample. You can demonstrate method and validation, but you cannot present these results as a launch or growth outcome from a real employer.

Common Mistakes

• Counting event rows as users without deduplicating user_id.
• Grouping all orders by created_at and labeling the output paid GMV.
• Joining event and order detail before aggregation, which repeats amounts.
• Measuring retention for cohorts without a complete observation window.
• Calling a version difference causal before checking channel and platform mix.
• Ignoring cancellation, refund, null, and time-zone rules.

Use the data operations metric dictionary to define measures and the anomaly diagnosis guide to structure investigation. For role preparation, continue with the data operations role guide, SQL interview question framework, and data operations topic.