Analytics design
- SQL course
- Advanced
- Lesson 19/81
- 5 min
Set up event and user tables and write skeleton queries.
Analytics design
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.
Practice Analytics design
Create events with id, user_id, event_date and event_type.
Starter code
-- events table
CREATE TABLE eventsSample solution
CREATE TABLE events (
id INT PRIMARY KEY,
user_id INT,
event_date DATE,
event_type VARCHAR(50)
)Expected output
Tablo oluşturuldu
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Which column is essential in analytics?
- Image
- Color
- A date Correct answer
- Language
Explanation: A date column is essential for analytics.
2 Add PRIMARY KEY
Question
CREATE TABLE events (
id INT ____,
user_id INT
);
Answer
CREATE TABLE events (
id INT PRIMARY KEY,
user_id INT
);Explanation: PRIMARY KEY is the right answer.
3 Analytics design: match the terms
| Term | Meaning |
|---|---|
user_id |
user |
event_date |
date |
event_type |
type |
analytics |
report |