Analytics design

Set up event and user tables and write skeleton queries.

In this project you will build an analytics report database. First step: create the events table.

CREATE TABLE events (
  id INT PRIMARY KEY,
  user_id INT,
  event_date DATE,
  event_type VARCHAR(50)
);

Skeleton of the events table.

The table events is created.

Analytics design — details

Analytics tables often use user_id and event_date. A DATE column is essential for time series.

Each row in the events table is one user action; an index on user_id and event_date speeds up daily and per-user reports noticeably.

Create events with id, user_id, event_date and event_type.

Show example solution

CREATE TABLE events (
  id INT PRIMARY KEY,
  user_id INT,
  event_date DATE,
  event_type VARCHAR(50)
)

Add PRIMARY KEY

CREATE TABLE events (
  id INT ____,
  user_id INT
);

Show answer

CREATE TABLE events (
  id INT PRIMARY KEY,
  user_id INT
);

Next lesson: CTE summary